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
I have been having to access a lot of stored procedures recently and decided it was time to optimize my datalayer class. I wanted to be able to call just about any stored procedure, that returns a data set, regardless of how many parameters the stored procedure requires.
I wrote the following method using the params keyword. This worked so well that I think I will use this format for update, insert (ExecuteNonQuery methods) and ExecuteScalar methods.
public bool GetLookUpData(string storedProc, string spParms, ref SqlDataReader rdr, params string strParms)
string Parms = spParms.Split(‘,’);
StringBuilder sqlErr = new StringBuilder();
if (Parms.Length != strParms.Length)
throw new System.ArgumentOutOfRangeException(“Arguments do not match”);
if (sqlConn.State == ConnectionState.Closed)
SqlCommand sqlQuery = new SqlCommand(storedProc, sqlConn);
sqlQuery.CommandType = CommandType.StoredProcedure;
sqlQuery.CommandTimeout = ConstSettings.SQLTimeOut;
for (int i = 0; i < strParms.Length; i++)
sqlErr.Append(sqlQuery.CommandText + ” ‘”);
for (int i = 0; i < strParms.Length – 1; i++)
sqlErr.Append(strParms[i] + “‘,'”);
sqlErr.Append(strParms[strParms.Length-1] + “‘”);
rdr = sqlQuery.ExecuteReader();
if (rdr == null)
logEntry(“Null – “ + this.sqlErr);
catch (Exception ex) // catches without assigning to a variable
logEntry(ex.Message + ” – “ + this.sqlErr);
I am currently consulting for Denso Wireless writing manufacturing traceability systems in C# and ASP.net. Denso Wireless manufactures navigation systems for some of the major automotive manufactures.
The manufacturing systems that I develop interface directly to assembly line hardware that in real time collects manufacturing metrics and data and stores this data in a SQL Server database instance.
The Traceability system also provides engineering and management real time access to the gathered metrics and data from an internationally accessible web site that is developed in ASP.net.