Time and day of week (BETWEEN) question

Question

Time and day of week (BETWEEN) question

Jeffery Williams

SSCertifiable

Points: 6441
More actions
September 23, 2007 at 8:59 am

#178647

Ok. I need help with the END portion of this range. Here is my code section for this:
SELECT
@RateGroupID = RateDeckGroupID
FROM
TelecomRateDeckGroup
WHERE
(@CallStartTime >= GroupStartTime AND @CallDOW >= GroupStartDOW)
--AND (@CallDOW <= GroupEndDOW) AND @CallStartTime <= GroupEndTime)
The uncommented portion works I have a call that starts on a given time and date. I get the Day of week value earlier in the SPROC. and then set the time as an int. I really don't care about the date portion only what day of the week it is ( 1 = Sunday).
I need to find the record in the DB that has a start time >= the time of my call and on or after the day of the week set in the db for that record. I also need (and this is where the problem is) to get the record where the end Time is within the scope of the call start time and the call start day of week is on or before the end day of week; hope this makes sense.
Now my sample data has a call time of 1950 (7:50pm) and a Day of week set to 6 (Friday)
My database has a record with the CallStartTime = 1900 (7:00pm) and Call Start Day of Week as 6
This same record has a CallEndTime of 0600 (6:00 am) and the Call End Day of Week is 7
This means that a call taking place from 7pm on the 6th day of the week through 6am on the 7th day of the week should match this record.
I can not get the END portion of this logic to work as 0600 is less than 1950.
??
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Robert O'Byrne SSCrazy Points: 2017 More actions · Answer 1

Yeah, dealing with time as an integer is going to cause problems. And even if you program a workaround you will probably end up having an issue when you wrap over more than 1 year. (or month)

I would recommend using the full date/time value in this portion of the WHERE clause. If you don't have one you may have to construct it from individual date and time pieces. It just works better when you deal with a date as a date, and you wouldn't have to worry about looking at this line when troubleshooting an issue.

unclebiguns-494774 Valued Member Points: 54 More actions · Answer 2

First of all, I agree with Robert, it is a lot easier to deal with regular datetime data types than it is to deal with them in parts, but here is an idea that may help you out:

Where

(@CallStartTime >= GroupStartTime and @CallDow >=GroupStartDow) And
(Case
When GroupEndDow > GroupStartDow Then @CallStartTime - 1200
Else @CallStartTime
End <= GroupEndTime And @CallDow <-GroupEndDow)

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

I will try this, thank you.

I have a datetime field as well for this that I was playing with. The problem here is that the DATE portion is not used and therefore is entered as a static value 01/01/1800

Now if the times are entered 01/01/1800 19:00:00 (Start Time)

and the end time is entered 01/01/1800 06:00:00 (End time)

there is a problem. The end time is actually the next day however the DATE is the same for both.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Where are you getting the CDR's from? Most CDR's include the CallStartTime as both Date and Time. And, the call duration is almost always listed in seconds. To get the CallEndTime as a date/time, all you need to do is...

DATEADD(ss,Duration,CallStartTime) and all of your other woes get real simple.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert O'Byrne SSCrazy Points: 2017 More actions · Answer 5

Jerrfrey Williams Wrote

"Now if the times are entered 01/01/1800 19:00:00 (Start Time)

and the end time is entered 01/01/1800 06:00:00 (End time)

there is a problem. The end time is actually the next day however the DATE is the same for both."

I think you're going to need a date / time field for the calls. Without one you just can't be sure what you're dealing with. If you don't have one in the file, you have to construct one. You may be able to figure out where the midnight point comes in and then add in current date before it, currentdate + 1 after it. Or better yet, the record may contain a record id with servernumber/datetime data which you may be able to extract.

Then there's always time zone issues. If you're reporting CDR records to local users across the country(ies) you have to adjust them for local times.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

You can use these for your filtering parameters. Not that this makes a HUGE assumption that no call is ever >24 hours (which you seem to be making, since you only look at one @callDOW).

declare @datec datetime

declare @enddate datetime

declare @startdate datetime

set @datec=cast('1/1/1900' as datetime)

select @startdate=dateadd(dd,@calldow,

dateadd(mm,cast(right(@callStartTime,2) as int),

dateadd(hh,cast(left(@callStartTime,2) as int),@datec))),

@enddate=dateadd(dd,@calldow,

dateadd(mm,cast(right(@callEndTime,2) as int),

dateadd(hh,cast(left(@callEndTime,2) as int),@datec)))

select @enddate=case when @startdate>@enddate then dateadd(dd,1,@enddate) end

Now - you could compare them to these (i.e. these would be based on your fields)

Your "start date"

dateadd(dd,groupstartdow,

dateadd(mm,cast(right(groupStartTime,2) as int),

dateadd(hh,cast(left(groupStartTime,2) as int),@datec)))

you "end date"

dateadd(dd,(groupenddow+case when groupstartdow>groupenddow then 7 else 0 end),

dateadd(mm,cast(right(groupendTime,2) as int),

dateadd(hh,cast(left(groupendtime,2) as int),@datec)))

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

I need to find the record in the DB that has a start time >= the time of my call and on or after the day of the week set in the db for that record.

Hmm ... if the call is on the last (7th) day of the week, might it not end on the 1st day of the (next) week?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

John te Lintelo Say Hey Kid Points: 702 More actions · Answer 8

I believe the following code will do what you want. It should also handle the case where the GroupStartDOW = 7 and GroupEndDOW = 1.

SELECT @RateGroupID = RateDeckGroupID

FROM TelecomRateDeckGroup

WHERE

-- Start period

(

(@CallDOW + CASE WHEN @CallDOW = 1 AND GroupStartDOW > GroupEndDOW THEN 7 ELSE 0 END) > GroupStartDOW

OR

(

(@CallDOW + CASE WHEN @CallDOW = 1 AND GroupStartDOW > GroupEndDOW THEN 7 ELSE 0 END) = GroupStartDOW

AND

@CallStartTime >= GroupStartTime

)

AND

-- End period

(

@CallDOW GroupEndDOW THEN 7 ELSE 0 END)

OR

(

@CallDOW = (GroupEndDOW + CASE WHEN @CallDOW = 7 AND GroupStartDOW > GroupEndDOW THEN 7 ELSE 0 END)

AND

@CallStartTime <= GroupEndTime

)

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 9

I will take a look at your example. Just commenting on your first line.

As far as the assumption; I only care about when the call started not the duration or ending time.

I will have a look at your example, thank you.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 10

Thank you as well, Looking over everything now.

To explain the intent here. I only care about when a call starts. Think of this as a cell phone and you have free nights.

Your free time starts at 7 pm and ends at 6 am the next morning (as an example).

You don't care about when the call ENDS, only that it begins during this time frame. The call can start at 7pm and go until 7 pm three days later for all I care; in this example.

Thanks everyone for your help so far. I will let you know how it turns out.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 11

Jeff,

Thank you for your input. In this case I am not looking for the end time or making any sort of rating determination based upon the end time or call duration. Yes I have this data available however I am only concerned with matching a rate entry based upon the call start time and the time 'frame' that the rate record covers.

Of course there are other criteria however it is this time frame that I am having problems with.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh