>>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
>
>SQL 2012+ solution (in my quick tests surprisingly seems to be slower than SQL 2005-2008 solution - may change on a real table with many rows):


I do not think a simple example like this is very representative performance wise. The time to run the solution is small anyways. I think before you make any jugdement on performance, you need to run this on a representative number of records. This is because the optimizer is smart and will take the number of records into account and change the execution plan if neccesary.


>

>;
>
>WITH cte
>AS (
>	SELECT *
>	FROM (
>		SELECT *
>		FROM @TestTable
>		WHERE TrafficDate = @RunFordate
>		) src
>	UNPIVOT(TrafficCount FOR ColumnName IN (
>				P01
>				,P02
>				,P03
>				,P04
>				,P05
>				,P06
>				,P07
>				,P08
>				,P09
>				,P10
>				,P11
>				,P12
>				,P13
>				,P14
>				,P15
>				,P16
>				)) 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
>    ,StartTime
>	,DATEADD(hour, 1, StartTime) as EndTime
>	,SUM(TrafficCount) OVER (
>		ORDER BY StartTime ROWS BETWEEN CURRENT ROW
>				AND 3 FOLLOWING
>		) AS TotalCount
>FROM cte3
>ORDER BY TotalCount DESC;