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

This, and the other solutions, looks like pure gobble-degook to me (based on the fact that anything beyond a simple join is beyond me :-} )

Wouldn't it be far simpler and more concise to use a CLR method ?