Archive

Archive for February, 2010

SQL Group by Date Range

February 20, 2010 2 comments

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

Categories: SQL and stuff

C# params keywork in datalayer

February 19, 2010 Leave a comment

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();
try
{
if (Parms.Length != strParms.Length)
{
throw new System.ArgumentOutOfRangeException(“Arguments do not match”);
}

if (sqlConn.State == ConnectionState.Closed)
{
return false;
}

SqlCommand sqlQuery = new SqlCommand(storedProc, sqlConn);
sqlQuery.CommandType = CommandType.StoredProcedure;
sqlQuery.CommandTimeout = ConstSettings.SQLTimeOut;

for (int i = 0; i < strParms.Length; i++)
{
sqlQuery.Parameters.AddWithValue(Parms[i], strParms[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);
return false;
}

sqlErr.Remove(0,strParms.Length);

return true;
}
catch (Exception ex) // catches without assigning to a variable
{

logEntry(ex.Message + ” – “ + this.sqlErr);
return false;
}
}

Categories: C# Code and stuff

Current Consulting Project

February 19, 2010 Leave a comment

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.

Categories: Consulting
Follow

Get every new post delivered to your Inbox.

Join 56 other followers