>>>>>>http://social.technet.microsoft.com/wiki/contents/articles/25654.t-sql-find-the-peak-hour.aspx
>>>>>
>>>>>Snip : "a simple solution"
>>>>>
>>>>>If you call that simple what do you call 'complicated' :-}
>>>>>
>>>>>I raised this with Kevin : given the option of a simple CLR method (a small piece of code with more flexible options over the number of periods in the table and the span of the 'peak' period why on earth would you use this T-SQL ?
>>>>
>>>>
>>>>Viv - I think I mentioned this before - there's nothing wrong with a CLR solution. There were several solutions here - Walter recommended correlated subqueries, which are historically very good at implementing a "perform varying" pattern - I mentioned UNPIVOT and recursive queries (though in retrospect that one was a bit more complex for this particular problem), and Naomi referenced CROSS APPLY. And Naomi also mentioned that the new analytic functions in 2012 allow for cumulative aggregations in fewer lines of code. And you mentioned a CLR method. All are valid.
>>>>
>>>>If a person is a contractor (or simply does a large amount of job hopping), they'll invariably run into environments where certain possible solutions aren't on the table. I know DBAs who turned off CLR integration (though I don't agree with doing so, especially since new functionality in the SQL/BI tools in 2012 require CLR integration). Walter bring up a good point, that some people need to stay closer to the common denominator of SQL 2000. Yes, there are only so many hours in a day to learn every possible approach to something, but I like these discussions simply to get different perspectives on solving problems.
>>>>
>>>>Ironically, I'm getting one foot (or maybe more like one toe) back into the .NET world (not abandoning SQL/BI just "expanding back"), so posts like yours help me to remember the .NET world. :)
>>>>
>>>>I think Naomi's solution (and Walter's) are a bit more complicated than mine. The point is that all proposed methods work.
>>>
>>>I understand the possible need for supporting earlier versions of MSSQL and that some DBAs might not allow CLR integration (though I'm not sure why).
>>>But, given a case where neither of the above apply which would you prefer - a TSQL or a CLR solution ?
>>>
>>>Enjoy the toe dipping :-}
>>
>>The reason that DBA's do not like it is because of a potential security breach,
>
>I'm not sure what those potential breaches are. MSSQL itself restricts what access the CLR assembly has to data. CAS controls what access the CLR has to the external environment (and if PERMISSION_SET = SAFE that means *none*)
>
>Good article : http://www.codemag.com/Article/0603031
>
>>Therefore I've not bothered to create custom aggregrations into SQL server, even though I agree with you that it makes life a lot easier (not have to create complex SQL) and better performance.

I guess, anything they do not know is regarded a security breach. I can tell you however several clients where we would be unable to install CLR aggregated functions because the DBA's will prevent it.