Pulling reports for Late night shift

Version 1
    This document was generated from CDN thread

    Created by: Anthony Kaune on 23-07-2012 11:47:45 AM
    Does anyone have a good stategy for setting up reports for a late night shift.
     
    EX: 1st shift 7am-3pm
    2nd shift 3pm-11pm
    3rd shift 11pm-7am
     
    Standard reports will not let you pull the 11pm-7am interval since 11pm is after 7am.  Right now we just run the report twice once for 11pm-midnight and again for midnight to 7am. 
     
    I am trying to come up with a report that will give us the ability to gather the data on one report run and not have to spend all that time combining data later.  Anyone know of a way to accomplish this?
     

    Subject: RE: Pulling reports for Late night shift
    Replied by: Edwin Andrews on 10-08-2012 01:34:59 PM
    /*
    These will provide the start DateTime for each of the shifts. You can use similar for end.
    If I understand correctly, a CASE statement or WHERE filter using these values will provide what you're after.
    */
     
    declare
    @midnight DateTime, @Shift1StartTime DateTime, @Shift2StartTime DateTime, @Shift3StartTime DateTime
    ;
    SET @midnight = dateadd(day,datediff(day,0,getdate()),0)  
    set @Shift1StartTime = dateadd(hh,-1,@midnight)
    set @Shift2StartTime =  dateadd(hh,7,@midnight)
    set @Shift3StartTime =  dateadd(hh,15,@midnight)
    ;
    select
    @midnight midnite
    ,@Shift1StartTime Shft1
    ,@Shift2StartTime Shft2
    ,@Shift3StartTime Shft3