views:

37

answers:

4

I have following problem where I have to group different transactions into timeslots. Suppose you have a table with records which contain an entry datetimestamp. These records are created by users (operators) who work in different shifts

Shift 1: 5 - 13h // Shift 2: 13 - 21h // Shift 3: 21 - 5h

Now I want to have a flexible query which rounds the timestamps down to the start time of the shift. Example:

2010-09-08 06:12:00.000 --> 2010-09-08 05:00:00.000
2010-09-08 02:12:00.000 --> 2010-09-07 21:00:00.000

I already tried a few queries using dateadd and datediff but I don't get it to work... Can anybody help? Thanks

A: 

How about this...?

select 
    case
        when datepart(Hh, dt) >= 5 AND datepart(Hh, dt) < 13 then 1
        when datepart(Hh, dt) >= 13 AND datepart(Hh, dt) < 21 then 2
        when datepart(Hh, dt) < 5 OR datepart(Hh, dt) >= 21 then 3
    end
from myTable
gkrogers
Note how the `day` part can change to the previous day in the example. You must distinguish between 'shift 3, started today' and 'shift 3, started yesterday'.
GSerg
Ah, sorry missed the bit about rounding the datetime field down to the start of the shift, rather than just identifying the shift.
gkrogers
I was thinking too far, this is indeed the solution, thanks!!
Koen VC
+1  A: 
select
case 
  when datepart(hh, start_date) between 5 and 12
       then dateadd(hh, 5, dateadd(d, datediff(d, 0, start_date), 0))
  when datepart(hh, start_date) between 13 and 20
       then dateadd(hh, 13, dateadd(d, datediff(d, 0, start_date), 0))
  when datepart(hh, start_date) between 21 and 23
       then dateadd(hh, 21, dateadd(d, datediff(d, 0, start_date), 0))
  else dateadd(hh, 21, dateadd(d, datediff(d, 0, start_date)-1, 0))
end
from ...
GSerg
A: 
select 
    case
        --shift I   
        when datepart(HH, [TimeStamp]) >= 5 and datepart(HH, [TimeStamp]) < 13 then
            dateadd(HH, 5, dateadd(dd,0, datediff(dd,0,[TimeStamp])))
        --shift II
        when datepart(HH, [TimeStamp]) >= 13 and datepart(HH, [TimeStamp]) < 21 then
            dateadd(HH, 13, dateadd(dd,0, datediff(dd,0,[TimeStamp])))
        --shift III
        when datepart(HH, [TimeStamp]) >= 21 then               
            dateadd(HH, 21, dateadd(dd,0, datediff(dd,0,[TimeStamp])))
        when datepart(HH, [TimeStamp]) < 5 then         
            dateadd(HH, 21, dateadd(dd,0, datediff(dd,0,[TimeStamp])-1))                            
    end as StartTime
from 
    Table1
Branimir
A: 

If your database supports an INTERVAL type, subtract five hours from the event's timestamp to line it up relative to midnight instead of 0500 and divide the time part by eight hours to get a shift number indexed from zero.

To get the shift start time, multiply the shift number by eight hours, add that to the date part of the subtraction you did above and then add five additional hours to line it back up with your shift schedule.

If you're going to be querying these a lot, you might be better off to create a second table that individually identifies each shift by an ID and its start time. You can then populate the shifts table using an ON INSERT trigger on the table containing your events that does the calculation once, inserts a new row into the table of shifts if needed and ties your event row to it using a foreign key. That would also give you a much easier way to query all of the events that happened during a specific shift.

Blrfl