>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?
>
>- Dot

Its a bit of a pitty a whole day is in one row as the solution below would not be able to calculate the peak if the peak is beyond 23.00. It would have been better if each measurement is in its own row. Then a UNPIVOT would not be neccesary, and peak crossing midnight would be very easy to calculate.


Just a twist on Naomi's solution, but with only one CTE

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


WITH Cte AS (
	SELECT *, RANK() OVER (ORDER BY CAST(SUBSTRING(Columnname,2,2) as int)) as xrank
	FROM @Counts
	UNPIVOT(TrafficCount FOR ColumnName IN (P1	,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)) unpvt)
SELECT TOP 1 TrafficCount, ColumnName, (SELECT SUM(Trafficcount) FROM Cte WHERE xrank BETWEEN a.xRank AND a.XRank+3) as TotalCount
FROM Cte a
ORDER BY 3 DESC ;