Pulling reports for Late night shift

Document created by cdnadmin on Jan 25, 2014
Version 1Show Document
  • View in full screen mode
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.
@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)
@midnight midnite
,@Shift1StartTime Shft1
,@Shift2StartTime Shft2
,@Shift3StartTime Shft3