kingchris
Reputation: 1757
T-SQL Group By on Irregular Time Frames
I have a SQL statement that works perfectly if want my SUMmations for each and every hour (SQL Server 2008). The DATEPART(HOUR, DATE_TIME) is doing all the wonderful work for me.
SELECT SUM(case STATION_ID when 'S-WELDCHK' then 1 else 0 end) as WELDCHK
,SUM(case STATION_ID when 'S-GLUING-OUT-OK' then 1 else 0 end) as GLUING
,SUM(case STATION_ID when 'S-GLUING-OUT-NOK' then 1 else 0 end) as 'GLUING-NOK'
,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-OK' then 1 else 0 end) as ULTRAWELD
,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-NOK' then 1 else 0 end) as 'ULTRAWELD-NOK'
,SUM(case STATION_ID when 'S-BOLTFAST-OUT-OK' then 1 else 0 end) as BOLTFAST
,SUM(case STATION_ID when 'S-BOLTFAST-OUT-NOK' then 1 else 0 end) as 'BOLTFAST-NOK'
,SUM(case STATION_ID when 'S-MAPVISION-OUT-OK' then 1 else 0 end) as MAPVISION
,SUM(case STATION_ID when 'S-MAPVISION-OUT-NOK' then 1 else 0 end) as 'MAPVISION-NOK'
,SUM(case STATION_ID when 'S-CHECKFIX-OUT-OK' then 1 else 0 end) as CHECKFIX
,SUM(case STATION_ID when 'S-CHECKFIX-OUT-NOK' then 1 else 0 end) as 'CHECKFIX-NOK'
,SUM(case STATION_ID when 'S-EJOT-OUT-OK' then 1 else 0 end) as EJOT
,SUM(case STATION_ID when 'S-EJOT-OUT-NOK' then 1 else 0 end) as 'EJOT-NOK'
FROM [dbFactory].[dbo].[Events]
where (DATEPART(yy,DATE_TIME) = 2014
AND DATEPART(mm,DATE_TIME) = 2
AND DATEPART(dd,DATE_TIME)= 5)
GROUP BY
DATEPART(HOUR, DATE_TIME)
with rollup
What I actually want is SUMS for irregular time periods that I have in a temp table (truncated for brevity)
Start Finish
06:00:00.000 06:30:00.000
06:30:00.000 07:30:00.000
07:30:00.000 08:30:00.000
08:30:00.000 09:30:00.000
09:30:00.000 10:00:00.000
10:00:00.000 10:30:00.000
10:30:00.000 11:30:00.000
11:30:00.000 12:30:00.000
12:30:00.000 13:30:00.000
13:30:00.000 14:00:00.000
Any suggestions on websites or what I should read up on to solve this. There is supposed to be a facility to rollup on a user defined function that perhaps I could pass the START and FINISH period times and the DATE_TIME of the transaction.
Upvotes: 0
Views: 231
Answers (2)
kingchris
Reputation: 1757
This is how I obtained a working solution. Its not pretty or efficient. This is part of a report that runs every 24 hours currently.
Stored procedure that gets the data
USE [dbFactory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 20140225
-- Description: Used by a report to extract SUM values for various Factory stations
-- =============================================
ALTER PROCEDURE [dbo].[spRptFetchStationActivitySummationsWithGroupBy]
@Day DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @START DATETIME;
DECLARE @FINISH DATETIME;
if @Day IS NULL
BEGIN
SET @DAY = GETDATE();
END
-- Subtract a day from the day passed in and set its time to 6am
DECLARE @ActualDay DATETIME = DATEADD(day,-1,DATEADD(HOUR,6,DATEADD(dd, 0, DATEDIFF(dd, 0, @DAY))));
DECLARE @dtTwentyFoursHoursLater DATETIME = DATEADD(HOUR,24,@ActualDay);
-- If a transaction time is exactly on 6:00 am we want to avoid counting it twice.
-- There are granularity issues on milliseconds
SET @dtTwentyFoursHoursLater = DATEADD(millisecond,-2,@dtTwentyFoursHoursLater);
-- SELECT @ActualDay,@dtTwentyFoursHoursLater
CREATE TABLE #LocalEvents(
[SERIAL_NUMBER] [nchar](20) NOT NULL,
[DATE_TIME] [datetime] NOT NULL,
[STATION_ID] [nchar](20) NOT NULL,
[SUCCESS] [char](1) NOT NULL,
[OVERRIDDEN] [char](1) NOT NULL,
[USER_NAME] [nchar](30) NOT NULL,
[EVENT_ID] [smallint] NOT NULL,
[EXTRA_INFO] [nchar](30) NOT NULL);
-- Stuff in the dummy entries which have one entry for each time frame from for the GROUP BY
INSERT INTO #LocalEvents([SERIAL_NUMBER],
[DATE_TIME],
[STATION_ID],
[SUCCESS],
[OVERRIDDEN],
[USER_NAME],
[EVENT_ID],
[EXTRA_INFO]
)
SELECT [SERIAL_NUMBER],
[DATE_TIME],
[STATION_ID],
[SUCCESS],
[OVERRIDDEN],
[USER_NAME],
[EVENT_ID],
[EXTRA_INFO]
FROM dbCCB.dbo.Events;
-- Stuff in the real events of the past 24 hours
INSERT INTO #LocalEvents([SERIAL_NUMBER],
[DATE_TIME],
[STATION_ID],
[SUCCESS],
[OVERRIDDEN],
[USER_NAME],
[EVENT_ID],
[EXTRA_INFO]
)
SELECT [SERIAL_NUMBER],
[DATE_TIME],
[STATION_ID],
[SUCCESS],
[OVERRIDDEN],
[USER_NAME],
[EVENT_ID],
[EXTRA_INFO]
FROM dbFactory.dbo.Events
WHERE DATE_TIME BETWEEN @ActualDay AND @dtTwentyFoursHoursLater;
-- The UDF returns the first two chars to show the order as is 01 to 30 for the time periods.
-- We chop them off (SUBSTRING) to provide the client with the start and end times of the time period.
SELECT SUBSTRING(dbo.fnReturnGroupWhenBetweenTimes( CONVERT(TIME,DATE_TIME)),4,200) as [Time]
,SUM(case STATION_ID when 'S-WELDCHK' then 1 else 0 end) as WELDCHK
,SUM(case STATION_ID when 'S-GLUING-OUT-OK' then 1 else 0 end) as GLUING
,SUM(case STATION_ID when 'S-GLUING-OUT-NOK' then 1 else 0 end) as 'GLUING-NOK'
,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-OK' then 1 else 0 end) as ULTRAWELD
,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-NOK' then 1 else 0 end) as 'ULTRAWELD-NOK'
,SUM(case STATION_ID when 'S-BOLTFAST-OUT-OK' then 1 else 0 end) as BOLTFAST
,SUM(case STATION_ID when 'S-BOLTFAST-OUT-NOK' then 1 else 0 end) as 'BOLTFAST-NOK'
,SUM(case STATION_ID when 'S-MAPVISION-OUT-OK' then 1 else 0 end) as MAPVISION
,SUM(case STATION_ID when 'S-MAPVISION-OUT-NOK' then 1 else 0 end) as 'MAPVISION-NOK'
,SUM(case STATION_ID when 'S-CHECKFIX-OUT-OK' then 1 else 0 end) as CHECKFIX
,SUM(case STATION_ID when 'S-CHECKFIX-OUT-NOK' then 1 else 0 end) as 'CHECKFIX-NOK'
,SUM(case STATION_ID when 'S-EJOT-OUT-OK' then 1 else 0 end) as EJOT
,SUM(case STATION_ID when 'S-EJOT-OUT-NOK' then 1 else 0 end) as 'EJOT-NOK'
/*,SUM(case STATION_ID when 'S-GENERIC' then 1 else 0 end) as 'GENERIC'*/
FROM #LocalEvents
GROUP BY dbo.fnReturnGroupWhenBetweenTimes(CONVERT(TIME,DATE_TIME))
WITH ROLLUP
DROP TABLE #LocalEvents;
END
The UDF that filters the Events into irregular shifts.
USE [dbFactory]
GO
/****** Object: UserDefinedFunction [dbo].[fnReturnGroupWhenBetweenTimes] Script Date: 03/14/2014 12:44:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 20140226
-- Description: Returns a 1 if Date's TIME is between two dates
-- =============================================
ALTER FUNCTION [dbo].[fnReturnGroupWhenBetweenTimes]
(
-- Add the parameters for the function here
@TestTime TIME
)
RETURNS VARCHAR(20)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result VARCHAR(20)
-- Add the T-SQL statements to compute the return value here
SELECT @Result =
case
when @TestTime between CONVERT(TIME,'06:00',14) and CONVERT(TIME,'06:30',14) then '01 06:00 06:30'
when @TestTime between CONVERT(TIME,'06:30',14) and CONVERT(TIME,'07:30',14) then '02 06:30 07:30'
when @TestTime between CONVERT(TIME,'07:30',14) and CONVERT(TIME,'08:30',14) then '03 07:30 08:30'
when @TestTime between CONVERT(TIME,'08:30',14) and CONVERT(TIME,'09:30',14) then '04 08:30 09:30'
when @TestTime between CONVERT(TIME,'09:30',14) and CONVERT(TIME,'10:00',14) then '05 09:30 10:00'
when @TestTime between CONVERT(TIME,'10:00',14) and CONVERT(TIME,'10:30',14) then '06 10:00 10:30'
when @TestTime between CONVERT(TIME,'10:30',14) and CONVERT(TIME,'11:30',14) then '07 10:30 11:30'
when @TestTime between CONVERT(TIME,'11:30',14) and CONVERT(TIME,'12:30',14) then '08 11:30 12:30'
when @TestTime between CONVERT(TIME,'12:30',14) and CONVERT(TIME,'13:30',14) then '09 12:30 13:30'
when @TestTime between CONVERT(TIME,'13:30',14) and CONVERT(TIME,'14:00',14) then '10 13:30 14:00'
when @TestTime between CONVERT(TIME,'14:00',14) and CONVERT(TIME,'14:30',14) then '11 14:00 14:30'
when @TestTime between CONVERT(TIME,'14:30',14) and CONVERT(TIME,'15:30',14) then '12 14:30 15:30'
when @TestTime between CONVERT(TIME,'15:30',14) and CONVERT(TIME,'16:30',14) then '13 15:30 16:30'
when @TestTime between CONVERT(TIME,'16:30',14) and CONVERT(TIME,'17:30',14) then '14 16:30 17:30'
when @TestTime between CONVERT(TIME,'17:30',14) and CONVERT(TIME,'18:00',14) then '15 17:30 18:00'
when @TestTime between CONVERT(TIME,'18:00',14) and CONVERT(TIME,'18:30',14) then '16 18:00 18:30'
when @TestTime between CONVERT(TIME,'18:30',14) and CONVERT(TIME,'19:30',14) then '17 18:30 19:30'
when @TestTime between CONVERT(TIME,'19:30',14) and CONVERT(TIME,'20:30',14) then '18 19:30 20:30'
when @TestTime between CONVERT(TIME,'20:30',14) and CONVERT(TIME,'21:30',14) then '19 20:30 21:30'
when @TestTime between CONVERT(TIME,'21:30',14) and CONVERT(TIME,'22:00',14) then '20 21:30 22:00'
when @TestTime between CONVERT(TIME,'22:00',14) and CONVERT(TIME,'22:30',14) then '21 22:00 22:30'
when @TestTime between CONVERT(TIME,'22:30',14) and CONVERT(TIME,'23:30',14) then '22 22:30 23:30'
when @TestTime between CONVERT(TIME,'23:30',14) and CONVERT(TIME,'23:59:59.998',14) then '23 23:30 00:30'
when @TestTime between CONVERT(TIME,'00:00',14) and CONVERT(TIME,'00:30',14) then '23 23:30 00:30'
when @TestTime between CONVERT(TIME,'00:30',14) and CONVERT(TIME,'01:30',14) then '24 00:30 01:30'
when @TestTime between CONVERT(TIME,'01:30',14) and CONVERT(TIME,'02:00',14) then '25 01:30 02:00'
when @TestTime between CONVERT(TIME,'02:00',14) and CONVERT(TIME,'02:30',14) then '26 02:00 02:30'
when @TestTime between CONVERT(TIME,'02:30',14) and CONVERT(TIME,'03:30',14) then '27 02:30 03:30'
when @TestTime between CONVERT(TIME,'03:30',14) and CONVERT(TIME,'04:30',14) then '28 03:30 04:30'
when @TestTime between CONVERT(TIME,'04:30',14) and CONVERT(TIME,'05:30',14) then '29 04:30 05:30'
when @TestTime between CONVERT(TIME,'05:30',14) and CONVERT(TIME,'06:00',14) then '30 05:30 06:00'
else 'UnKnown'
end
-- Return the result of the function
RETURN @Result
END
The contents of the dummy Events File minus a few columns of duplicate data
SERIAL_NUMBER DATE_TIME
DUMMY-SERIAL 2014-03-14 00:00:00.000
DUMMY-SERIAL 2014-03-14 01:00:00.000
DUMMY-SERIAL 2014-03-14 01:45:00.000
DUMMY-SERIAL 2014-03-14 02:15:00.000
DUMMY-SERIAL 2014-03-14 03:00:00.000
DUMMY-SERIAL 2014-03-14 04:00:00.000
DUMMY-SERIAL 2014-03-14 05:00:00.000
DUMMY-SERIAL 2014-03-14 05:45:00.000
DUMMY-SERIAL 2014-03-14 06:15:00.000
DUMMY-SERIAL 2014-03-14 07:00:00.000
DUMMY-SERIAL 2014-03-14 08:00:00.000
DUMMY-SERIAL 2014-03-14 09:00:00.000
DUMMY-SERIAL 2014-03-14 09:45:00.000
DUMMY-SERIAL 2014-03-14 10:15:00.000
DUMMY-SERIAL 2014-03-14 11:00:00.000
DUMMY-SERIAL 2014-03-14 12:00:00.000
DUMMY-SERIAL 2014-03-14 13:00:00.000
DUMMY-SERIAL 2014-03-14 13:45:00.000
DUMMY-SERIAL 2014-03-14 14:15:00.000
DUMMY-SERIAL 2014-03-14 15:00:00.000
DUMMY-SERIAL 2014-03-14 16:00:00.000
DUMMY-SERIAL 2014-03-14 17:00:00.000
DUMMY-SERIAL 2014-03-14 17:45:00.000
DUMMY-SERIAL 2014-03-14 18:15:00.000
DUMMY-SERIAL 2014-03-14 19:00:00.000
DUMMY-SERIAL 2014-03-14 20:00:00.000
DUMMY-SERIAL 2014-03-14 21:00:00.000
DUMMY-SERIAL 2014-03-14 21:45:00.000
DUMMY-SERIAL 2014-03-14 22:15:00.000
DUMMY-SERIAL 2014-03-14 23:00:00.000
My thanks to the many people at StackOverflow for all the help. I read many Questions and answers. I now have to learn about FIRST_VALUE and LAST_VALUE in my Groups but get it working in 2008 not 2012. It never ends.
Upvotes: 0
podiluska
Reputation: 51504
Join your query to the Periods table, using between
inner join Periods on events.DATE_TIME between Periods.Start and Periods.End
and group by a field in the Periods table
group by Periods.Start
NB. You need to decide what happens if the time period is exactly on the border between two periods. You may need to adjust the periods, or join using > and <=
You may also want to look at PIVOT rather than using the SUM(CASE... construct.
Upvotes: 1
Related Questions
- Group over dynamic date ranges
- T-SQL Grouping Dynamic Date Ranges
- SQL Server Group By with Irregular Date Range Criteria
- GROUP BY and Aggregation over a date range
- Grouping by timeframes with a modifier that changes over time
- SQL Server: Group results on time interval
- time interval period in sql
- Group by Time Interval
- Date-based multiple group by T-SQL query
- GROUPING DATEs in sql