>>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 ;
>>
>
>I like that one as well. Just a note (and I have to go back and check to see if this applied to Naomi's), you'd need a SELECT TOP 1 WITH TIES, as you could have multiple blocks with the same hour count.
Yes, you're right WITH TIES is needed when you need multiple instances. I could not however get that from Don's requirement since he described: "The challenge is to find the one hour block that contains the highest total for the day.", so I left it out.
>When I read Don's email, I thought of recursion. It's interesting how three different people approach things three different ways. I've noticed Naomi uses CROSS APPLY quite a bit for different challenges (and I've come to realize the performance on it isn't as bad as I thought), you like to use correlated subqueries in some of your answers, and in this one I thought of a recursive query. (Not a value judgment, just good to see different perspectives to solve a problem)
I think because our thinking pattern differs. I've been writing SQL queries of this nature for more than 20 years. That is in a time where the SQL language was not as rich of features as it is now. I've learned to approach a problem by solving it in SQL by its elementary and basic syntax. I pick up some new features now and then and learn to use them, but I stay at the roots as much as possible.
You, OTOH, seem to come from the other end. As a SQL MPV, you're always into the latest and greatest new features and thinking about where they can be applied. That is great, certainly if you earn your living for doing that.
Naomi falls somewhere in the middle, I guess. She is ambitious, smart and has developed her own style in writing complex SQL. and certainly is an asset to any team doing massive and complex SQL.
There is no question that both of you are far more comfortable and knowledgeable about newer features of SQL and where they can be applied, but if you understand the basics and keep close to them as much as possible you can do wonderful things as well.
>The recursive query turned out to be trickier than I thought. Took me an hour to write it. Actually as I look at all three, I think I like the correlated subquery the most, although somewhat surprised that the cost was slightly higher than either CROSS APPLY or recursion. (The ORDER BY is the culprit, but obviously can't get around it).
Since the query is about ranges, groups of records, a recursive query is not the first solution of choice to me. Recursive queries are best applied when you've got to weed through recursive data structures.
>Anyway, here's mine, could probably be simplified (and I included the beginning time in the result)
>
>Don, glad you posted the question, this is all good stuff.
>
>DECLARE @TestTable TABLE
>( TrafficDate Date,
> P01 INT, P02 INT, P03 INT, P04 INT, P05 INT, P06 INT, P07 INT, P08 INT,
> P09 INT, P10 INT, P11 INT, P12 INT, P13 INT, P14 INT, P15 INT, P16 INT)
>
>INSERT INTO @TestTable values
> ('1/1/2014', 1, 5, 4, 1, 8, 2, 1, 3, 1, 9, 7, 4, 5, 1, 2, 9) ,
> ('1/2/2014', 9, 1 , 4, 6, 8, 5, 4, 1, 2, 2, 1, 8, 6, 4, 3, 5) ,
> ('1/3/2014', 5, 4, 3, 6, 1, 8, 2, 9, 9, 1, 5, 6, 7, 7, 7, 1)
>
>
>DECLARE @RunFordate date = '1-2-2014'
>DECLARE @NumBuckets int = 16
>
> ;with UnPivotedCTE as
> ( select TrafficDate, TimeSegment, TrafficCount
> from (select * from @TestTable WHERE TrafficDate = @RunFordate) Temp
> unpivot ( TrafficCount for TimeSegment in ( P01, P02, P03, P04, P05, P06, P07, P08,
> P09, P10, P11, P12, P13, P14, P15, P16) ) T ) ,
>
> AnchorCTE as (select 1 as Bucket, TrafficCount, row_number() over (order by TimeSegment) as RowNum
> FROM UnPivotedCTE where TimeSegment between 'P01' AND 'P04'
> UNION ALL
> SELECT Bucket + 1 as Bucket, UnpivotedCTE.TrafficCount, row_number() over (order by TimeSegment) as RowNum
> FROM UnPivotedCTE, AnchorCTE
> WHERE RowNum = 1 and Bucket <= @NumBuckets - 4
> and TimeSegment between 'P' + right('0' + cast(Bucket + 1 as varchar(2)) ,2)
> and 'P' + right('0' + cast(Bucket + 4 as varchar(2)),2) ) ,
>
> RankCTE as ( select Bucket, sum(TrafficCount) as SumTrafficCount,
> RANK() OVER (ORDER BY SUM(TrafficCount) desc) AS GroupRank FROM AnchorCTE GROUP BY Bucket )
>
>
>SELECT DATEADD( MINUTE, 15 * Bucket, cast(@RunFordate as datetime)) AS TimeStart, Bucket, SumTrafficCount
> from RankCTE where GroupRank = 1
>
>
