SQL Server find nearest shift start (time) to a date

Question

I need to find records that have been changed since the last shift start. Shifts start at 07:00h (morning), 15:00h (afternoon) and 23:00h (night).

For example. It is now 17:15h, so I'm in the afternoon shift. Now I want to find work related records that have been changed since the start of my previous shift. So I need a starttime of 15:00 the previous day. That is since 24 hours ago + the time in the current shift.

I need a solution that I can implement as part of a where clause.

Basically I try to find the last start of a shift (7, 15 or 23h) and then subtract another 24 hours from that date. And have the query run until current time.

select * from workorder
where changedate >= getDate() - 24h - time_in_shift

or

select * from workorder
where changedate >= last_shift_start - 24h

To be honest I haven't found a good strategy yet. At first I tried to find the correct shift with a case statement and use that to do the correct calculation. But I don't think this is best way to do it.

select getDate() as now,
case
   when datepart(hh, getDate())-23 >= 0 then 'night'
   when datepart(hh, getDate())-15 >= 0 then 'afternoon'
   when datepart(hh, getDate())-7 >= 0 then 'morning'
   else 'night'
end as shift; 

---

Here is some sample data

id changedate
1 '2017-04-20 01:00:00'
2 '2017-04-20 02:00:00'
3 '2017-04-20 08:00:00'
4 '2017-04-20 09:00:00'
5 '2017-04-20 14:00:00'
6 '2017-04-20 16:00:00'
7 '2017-04-20 17:00:00'
8 '2017-04-20 22:00:00'
9 '2017-04-20 23:00:00'
10 '2017-04-20 23:30:00'
11 '2017-04-21 01:10:00'
12 '2017-04-21 02:10:00'
13 '2017-04-21 08:10:00'
14 '2017-04-21 10:10:00'
15 '2017-04-21 16:10:00'
16 '2017-04-21 16:20:00'

Lets say it is now 2017-04-21 17:00:00. So I'm in de afternoon shift. That means I want all records that have been changed since me previous start. Thus since 2017-04-20 15:00:00. That are all records except the first 5.

Answer 1

If you're using SQL Server 2012, you can build your WHERE clause with a case statement:

select  *
from    workorder
where   changedate >= case
            when datepart(hh, getDate()) >= 23
              then smalldatetimefromparts(year(dateadd(day, -1, cast(getdate() as date))),
                                          month(dateadd(day, -1, cast(getdate() as date))),
                                          day(dateadd(day, -1, cast(getdate() as date))),
                                          23, 00)
            when datepart(hh, getDate()) < 7
              then smalldatetimefromparts(year(dateadd(day, -2, cast(getdate() as date))),
                                          month(dateadd(day, -2, cast(getdate() as date))),
                                          day(dateadd(day, -2, cast(getdate() as date))),
                                          23, 00)
            when datepart(hh, getDate()) between 15 and 22
              then smalldatetimefromparts(year(dateadd(day, -1, cast(getdate() as date))),
                                          month(dateadd(day, -1, cast(getdate() as date))),
                                          day(dateadd(day, -1, cast(getdate() as date))),
                                          15, 00)
            else smalldatetimefromparts(year(dateadd(day, -1, cast(getdate() as date))),
                                        month(dateadd(day, -1, cast(getdate() as date))),
                                        day(dateadd(day, -1, cast(getdate() as date))),
                                        7, 00)
        end;

If you're using older version of SQL Server, the same can be accomplished replacing smalldatetimefromparts with a couple explicit conversion; you can see it explained in this answer

Answer 2

First thing is a table for the shifts (with one column only). Of course you could generate this on-the-fly, but why not have it in your database?

shiftstart
07:00
15:00
23:00

To get the current shift, you can do something like this:

select
  coaleasce
  (
    max(case when shiftstart < cast(getdate() as time) then shiftstart end),
    max(shiftstart)
  )
from shift;

Add yesterday to get the shiftstart for yesterday:

dateadd(day, -1, cast(getdate() as date)) + <above time>

---

UPDATE: As Damien_The_Unbeliever so rightly pointed out:

if it's currently 02:00, we want everything after 23:00 of two days ago

So we must subtract either one day or two depending on the time.

---

The complete adjusted query:

select *
from workorder
where changedate >=
(
  select
    coaleasce
    (
      dateadd(day, -1, cast(getdate() as date)) + 
      max(case when shiftstart < cast(getdate() as time) then shiftstart end),
      dateadd(day, -2, cast(getdate() as date)) + 
      max(shiftstart)
    )
  from shift
);