I recently solved an interesting problem. SQL Server datetime column treats a calendar day as being from 12:00 am to 12:00 pm. Where I work we run production from 03:00 am one day to 03:00 am the next day.
We recently needed to add data to our web site that reports daily production totals for the last seven days.
After doing some testing I realized that grouping by date was not going to work for two reasons. Finished products are scanned out anytime during either of our two shifts, Using a group by date would create individual rows every time the time portion of the date changes during a given date. And the above problem of when our day starts and ends.
After doing some digging I came up with the following stored procedure:
I set the starting date used in a while loop that is 9 days earlier than the current date.
For that day I construct a starting datetime variable and an ending datetime variable based on our starting shift time and ending shift time for that day.
Using a select case statement I build a date range expression that I can then use as part of my group by clause.
I insert these summed passed products into a temp table. I them increment the date and loop again.
The while loop continues until the current date.
In the last 7 days there will always be a week-end where production sums will be null.
I can them filter the week-end data out and pull the last 7 days of data and return those values in the stored procedure.
USE [Production Monitoring]
/****** Object: StoredProcedure [dbo].[PassedScannedOut] Script Date: 02/16/2010 16:01:07 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
– Author: <Joe Pitz>
– Create date: <02/12/2010>
– Description: Sums scanlogging by date for passed results for last 7 days
– Last Updated:
ALTER PROCEDURE [dbo].[PassedScannedOut]
Declare @StartDate as datetime
Declare @EndDate as datetime
Declare @LastWeek as datetime
Declare @Today as date
Declare @Days int
Declare @StartDateTime as datetime
Declare @EndDateTime as datetime
Declare @EndStartDateTime as datetime
Declare @StartTime varchar(12)
Declare @EndTime varchar(12)
Declare @Hours int
Create TABLE [dbo].[#ScanOutResults] (
set @Today = GETDATE()
set @Days = -9
select @StartTime = StartTime from shifts
where shiftname = ‘Shift1′
select @EndTime = StartTime,
@Hours = Hours from shifts
where shiftname = ‘Shift2′
set @LastWeek = DateAdd(dd,@Days,@Today)
set @StartDate = @LastWeek
while (@StartDate <= GETDATE())
– set the begin date and end date
set @StartDateTime = Convert(datetime,@StartDate + @StartTime,20)
set @EndStartDateTime = Convert(datetime,@StartDate + @EndTime,20)
set @EndDateTime = DateAdd(hh,@Hours,@EndStartDateTime)
– Debug code to verify sums by date range
–select * from ScanLogging
–where result = ’1′ and ScanTime >= @StartDateTime and ScanTime <= @EndDateTime
–and ScanStation = @ScanStation
insert #ScanOutResults (ScanStation,Date,Passed)
select sl.Scanstation,sl.Date,sum(sl.result) Passed from
when ScanTime >= @StartDateTime and scantime <= @EndDateTime then CONVERT(char(10),@StartDate,111)
where result = ’1′ and ScanTime between @StartDateTime and @EndDateTime
and ScanStation = @ScanStation) as sl
group by sl.ScanStation,sl.Date
set @StartDate = DATEADD(dd,1,@StartDate)
select * from
(select top 7 * from #ScanOutResults
where ScanStation is not null
order by date desc
order by date