All Forums Database
mitsukiefi 10 posts Joined 08/12
28 Sep 2012
Show Whole Business Week

Dear All
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.
 
Best regards,
Christoph

Qaisar Kiani 337 posts Joined 11/05
28 Sep 2012

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
WAQ 158 posts Joined 02/10
28 Sep 2012

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?

You must sign in to leave a comment.