Write a query that returns individual row results from the fact table, but also returns the average for all the rows and for each shift using the OVER command. In your query, use those results to calculate the difference between a fact table rows's Calls with the average number for all the calls. Here is a sample of the result set:
|
FactCallCenterID
|
Shift
|
Calls
|
FullDateAlternateKey
|
AvgforAllCalls
|
AvgforShiftCalls
|
Difference
|
|
91
|
PM2
|
699
|
2010-11-23
|
361
|
512
|
338
|
|
95
|
PM2
|
669
|
2010-11-24
|
361
|
512
|
308
|
|
79
|
PM2
|
663
|
2010-11-20
|
361
|
512
|
302
|
|
51
|
PM2
|
648
|
2010-11-13
|
361
|
512
|
287
|
|
115
|
PM2
|
632
|
2010-11-29
|
361
|
512
|
271
|