T-SQL Challenge - Hierarchical Aggregates and Sorting

Given the Orders table in the Northwind database, return the following:
- Individual orders and their freight values
- Daily total freight (in separate rows)
- Monthly total freight (in separate rows)
- Yearly total freight (in separate rows)
- Grand total freight (in a separate row)

The result set should include the following columns:
OrderYear, OrderMonth, OrderDay, OrderID , Freight.

Return the rows sorted such that:
- Orders will be sorted by OrderDate, OrderID
- The row with the daily total should be returned right after the corresponding day’s individual orders
- The row with the monthly total should be returned right after the corresponding month’s last daily total
- The row with the yearly total should be returned right after the corresponding year’s last monthly total
- The row with the grand total should appear last

Sorting needs to be deterministic.
You cannot rely on luck, optimization or anything else.

The output should look like this (in abbreviated form):

OrderYear   OrderMonth  OrderDay    OrderID     Freight----------- ----------- ----------- ----------- ---------------------1996        7           4           10248       32.381996        7           4           NULL        32.381996        7           5           10249       11.611996        7           5           NULL        11.611996        7           8           10250       65.831996        7           8           10251       41.341996        7           8           NULL        107.17...1996        7           31          10269       4.561996        7           31          NULL        4.561996        7           NULL        NULL        1288.18...1996        12          31          10399       27.361996        12          31          NULL        27.361996        12          NULL        NULL        2798.591996        NULL        NULL        NULL        10279.87...1998        5           6           11074       18.441998        5           6           11075       6.191998        5           6           11076       38.281998        5           6           11077       8.531998        5           6           NULL        71.441998        5           NULL        NULL        685.081998        NULL        NULL        NULL        22194.05NULL        NULL        NULL        NULL        64942.69(1337 row(s) affected)

Please post your solution as a comment in the blog
(make sure you don’t peek at others’ solutions before finishing yours).
Please also send me the solution directly as a .sql file to [email protected].
The puzzle will be open for a week.
I’ll post a summary blog entry with the solutions next week.

Good Luck!

--
BG