>Hi All -
>
>Here is a interesting problem for all you SQL experts. I am not sure it is possible but I thought I would ask anyway. I have a table containing 24 hour traffic counts. The counts are taken every 15 minutes and the data is stored in columns P1 thru P96. Thus the first hour of the day from midnight to 1:00 AM is stored in P1-P4 and the total for the hour would be (P1+P2+P3+P4). The challenge is to find the one hour block that contains the highest total for the day. The one hour block may begin at any of the 15 minute time periods (any P column up to P93). The desired result would provide the beginning of the time period (P column) and the total for the hour.
>
>The problem is easy enough to solve with VB after the data is loaded into a grid or table but the current challenge is to solve it with an SQL query so it could be stored into a parameterized view and used with a GIS interface. Any thoughts as to whether this is even possible?
>
>- D

Here is code for 12 first column that can be easily expanded to 96 columns.

DECLARE @Counts TABLE (
	P1 INT
	,P2 INT
	,P3 INT
	,P4 INT
	,P5 INT
	,P6 INT
	,P7 INT
	,P8 INT
	,P9 INT
	,P10 INT
	,P11 INT
	,P12 INT
	);

INSERT INTO @Counts (
	P1
	,P2
	,P3
	,P4
	,P5
	,P6
	,P7
	,P8
	,P9
	,P10
	,P11
	,P12
	)
VALUES (
	10
	,12
	,7
	,6
	,32
	,4
	,9
	,30
	,90
	,21
	,23
	,12
	)

DECLARE @Today DATETIME = cast(CURRENT_TIMESTAMP AS DATE);;

WITH cte
AS (
	SELECT *
	FROM @Counts
	UNPIVOT(TrafficCount FOR ColumnName IN (
				P1
				,P2
				,P3
				,P4
				,P5
				,P6
				,P7
				,P8
				,P9
				,P10
				,P11
				,P12
				)) unpvt
	)
	,cte2
AS (
	SELECT *
		,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) / 4 + 1 AS [Hour]
		,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) % 4 * 15 AS [Minutes]
	FROM cte
	)
	,cte3
AS (
	SELECT TrafficCount
		,dateadd(minute, [Minutes], dateadd(hour, [hour] - 1, @Today)) AS StartTime
	FROM cte2
	)
SELECT TOP (1)
WITH TIES TrafficCount AS IntervalStartCount
	,TotalCount
	,StartTime
	,EndTime
FROM cte3 c
CROSS APPLY (
	SELECT SUM(TrafficCount) AS TotalCount
		,DATEADD(minute, 15, MAX(c2.StartTime)) AS EndTime
	FROM cte3 c2
	WHERE c2.StartTime >= c.StartTime
		AND c2.StartTime < dateadd(hour, 1, c.StartTime)
	) X
ORDER BY TotalCount DESC;

I used 2008 style query with CROSS APPLY although it could have been solved with new running total abilities in SQL 2012 and up. I just didn't have time to figure out exact PARTITION BY clause to make it work in SQL 2012+.