>>>>http://social.technet.microsoft.com/wiki/contents/articles/25654.t-sql-find-the-peak-hour.aspx
>>>
>>>Goodness! I'm famous.
>>>
>>>:)
>>>
>>>Thank you for the heads up. What does he mean when he speaks about the "correctly designed table"? Is he suggesting there might be a better table structure for my data? He doesn't really give much help there.
>>
>>By correctly designed table it means one row for each date/time instead of 96 columns.
>>
>>BTW, who is he? I wrote that article.
>
>OOPS! Sorry. I looked at it too quickly and missed your name on it. My apologies.
>
>Actually columns P1-P96 are only half of the record. The table also contains P101-P196 which represents the opposite direction. I did it this way to keep the count together and avoid bloat in the number of records in the table. It also enables me to save the location, date, contractor, lat/long, etc only once instead of 200 times for each count. This seemed more efficient to me. You can see the final product at http://gismaps.pagnet.org/trafficcounts.

The product looks very nice!

As for DB design, the normalization requires one row per each time instead of 96 (or 192) columns. You saw we had to do an extra step in order to get the desired result.