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

About these ads
Categories: SQL and stuff
  1. Martin Eatough
    July 4, 2010 at 5:23 pm

    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 :-)

    • July 4, 2010 at 6:25 pm

      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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 49 other followers

%d bloggers like this: