SQL Group by Date Range

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]
GO
/****** Object:  StoredProcedure [dbo].[PassedScannedOut]    Script Date: 02/16/2010 16:01:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

– =============================================
– 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]
@ScanStation varchar(50)

AS
BEGIN

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] (
[ScanStation] [varchar](50),
[Date] [char](10),
[Passed] [int]
)

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())
begin

– 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
(
select case
when ScanTime >= @StartDateTime and scantime <= @EndDateTime then CONVERT(char(10),@StartDate,111)
end
as Date,ScanStation,Result
from scanlogging
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)

end

select * from
(select top 7 * from #ScanOutResults
where ScanStation is not null
order by date desc
) sl
order by date


END

About these ads