>>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+.
Smokin' !
In real life my table has lots of records so I need to filter the select with some parameters in a WHERE clause. I tried this and it works great.
FROM cte WHERE (LocID='N-18' AND Classification = 2)
but this does not
FROM cte WHERE (LocID=@LocID AND Classification = @Class)
I note that you used a parameter for @Today. So why does mine not work and yours does?
