Home » SQL and stuff » SQL Group by Date Range

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

2 thoughts on “SQL Group by Date Range

  1. Hello, Joe

    I guess you gave up on the Mercury (now HP) products, then??

    Me, I’m still a LoadRunner-er – when I can

    Write back soon, Sir. Only Italian I have is a 1978 Moto Morini 350 Sport…never got as far as a Duck :-)

    1. Hello Martin,

      I have not done much WinRunner or QTP in quite a few years. Currently doing Nunit Test automation in C#. I am re-building an old 1990 750 sport currently. Waiting on some engine parts to be machined.

      It appears you are still in the UK?

      Thanks

      Joe Pitz

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>