Show Whole Business Week

Database
Enthusiast

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

2 REPLIES
Enthusiast

Re: Show Whole Business Week

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
Enthusiast

Re: Show Whole Business Week

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?