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.38 1996 7 4 NULL 32.38 1996 7 5 10249 11.61 1996 7 5 NULL 11.61 1996 7 8 10250 65.83 1996 7 8 10251 41.34 1996 7 8 NULL 107.17 ... 1996 7 31 10269 4.56 1996 7 31 NULL 4.56 1996 7 NULL NULL 1288.18 ... 1996 12 31 10399 27.36 1996 12 31 NULL 27.36 1996 12 NULL NULL 2798.59 1996 NULL NULL NULL 10279.87 ... 1998 5 6 11074 18.44 1998 5 6 11075 6.19 1998 5 6 11076 38.28 1998 5 6 11077 8.53 1998 5 6 NULL 71.44 1998 5 NULL NULL 685.08 1998 NULL NULL NULL 22194.05 NULL 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
