SQL: How to get the actual shift values

Question

I´ve created a report in SSRS in which I display all Machines volume from last hour:

where  IntervalStartDateTime >= dateadd(HOUR, -2, GETDATE())
    And IntervalStartDateTime <= dateadd(HOUR, -1, GETDATE())

from last 4 hours:

where  IntervalStartDateTime >= dateadd(HOUR, -4, GETDATE())
    And IntervalStartDateTime <= dateadd(HOUR, -3, GETDATE())

and from last 8 hours:

where  IntervalStartDateTime >= dateadd(HOUR, -8, GETDATE())
    And IntervalStartDateTime <= dateadd(HOUR, -7, GETDATE())

And additionaly I would like to display a row for the actual shift --> here I have some problems. There a three shifts. Night Shift: from: 05:45 - 13:45 early shift: from: 13:45 - 21:45 late shift: from : 21:45 - 05:45

Table:

IntervalStartDateTime
2017-03-28 15:30:00.000
2017-03-28 15:30:00.000
2017-03-28 15:30:00.000
2017-03-28 15:30:00.000
2017-03-28 15:30:00.000

How can I get the actual shift value? I am using Microsoft SQL Server Management Studio

To confirm, you are asking for a row that shows all Machines volume from the current shift? — toonice, Mar 28 '17 at 13:52

score 0 · Answer 1 · answered Mar 28 '17 at 13:52

For the current shift...

with WorkByShift as
(
select OtherFields,
       case
         when cast(IntervalStartDateTime as time) < '05:45:00' then 1
         when cast(IntervalStartDateTime as time) < '13:45:00' then 2
         when cast(IntervalStartDateTime as time) < '21:45:00' then 3
         else 1
       end as ShiftNo
from MyTable
)
select WBS.*
from WorkByShift  WBS
where ShiftNo = case
                 when cast(GETDATE() as time) < '05:45:00' then 1
                 when cast(GETDATE() as time) < '13:45:00' then 2
                 when cast(GETDATE() as time) < '21:45:00' then 3
                 else 1
                end
and IntervalStartDateTime > dateadd(HOUR, -16, getdate())

score 0 · Accepted Answer · edited May 23 '17 at 12:17

Please try -

WHERE ( IntervalStartTime >= CASE
                                 WHEN CONVERT( TIME, GETDATE() ) < '05:45:00' THEN CONVERT( DATE, DATEADD( DAY, -1, GETDATE() ) ) + CAST( '21:45:00' AS DATETIME )
                                 WHEN CONVERT( TIME, GETDATE() ) < '13:45:00' THEN CONVERT( DATE, GETDATE() ) + CAST( '05:45:00' AS DATETIME )
                                 WHEN CONVERT( TIME, GETDATE() ) < '21:45:00' THEN CONVERT( DATE, GETDATE() ) + CAST( '13:45:00' AS DATETIME )
                                 ELSE                                              CONVERT( DATE, GETDATE() ) + CAST( '21:45:00' AS DATETIME )
                             END )

Note : This is my first time trying SQL-Server, but I'm going to have start sometime I guess. :)

If you have any questions or comments, then please feel free to post a Comment accordingly.

SQL: How to get the actual shift values

2 Answers2