I have stored information about company sales in a table tab_sales as follows:
Company Week Date Sales
Co. 1 201237 10-09-12 $200
Co. 1 201237 12-09-12 $150
Co. 1 201237 14-09-12 $420
The example shows that for this particular week, sales occured only for three specific business days.
I need to retrieve the following result set:
Company Date Sales
Co. 1 10-09-12 $200
Co. 1 11-09-12 $0
Co. 1 12-09-12 $150
Co. 1 13-09-12 $0
Co. 1 14-09-12 $420
In other words, I need to show the whole business week including the days the company didn't sell anything.
Please note that the fact that nothing was sold on a particular day is not recorded in the DWH.
We have a table tab_calendar_date that contains all information about days, weeks, months, and years.
Do you have any ideas how to achieve this? Any help is highly appreciated.
Do a Right outer join with sys_calendar.calendar. Something like this...
SEL A.Company, B.Date, A.Sales
FROM tabl_calendar_date A
RIGHT OUTER JOIN sys_calendar.Calendar B
ON A.Date = B.Calendaar_date
Can you explain a bit more about your tab_calendar_date table? Hows does it look like?
Do you have weekly intervals present in that table?