>>>
>>>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 ?
>
>To this day, I don't think CLR methods have taken off the way some expected. Some DBAs are either skeptical or downright superstitiously frightened by CLR integration with the database (usually because they don't know .NET languages and don't want to learn them).

So 'fear and superstition ? :-}

>I can certainly understand some developers wanting to bring back the raw results and process them in the application development tool

Well, by your own admission, the query you came up with took you an hour ( and that without adding P17 - P96 :-}). If you had the plumbing in place to use CLR then it could be something as simple as :

static int MaxForInterval(List<int> counts, int interval)
        {
            if (interval > counts.Count) return 0;
            int max = 0;
            for (int i = 0; i < counts.Count - interval + 1; i++)
            {
                max = Math.Max(max,counts.GetRange(i, interval).Sum());
            }
            return max;
        }

which also allows for varying the number of periods and the ability to change the length of time (1hr, 2hrs, 3hrs, etc.) which you may be interested in. That took me five minutes (OK - I skipped the 'tied results' aspect but that's easy to add)