I have two tables.
An orders table with customer, and date. A date dimension table from a data warehouse.
The orders table does not contain activity for every date in a given month, but I need to return a result set that fills in the gaps with date and customer.
For Example, I need this:
Customer Date =============================== Cust1 1/15/2012 Cust1 1/18/2012 Cust2 1/5/2012 Cust2 1/8/2012
To look like this:
Customer Date ============================ Cust1 1/15/2012 Cust1 1/16/2012 Cust1 1/17/2012 Cust1 1/18/2012 Cust2 1/5/2012 Cust2 1/6/2012 Cust2 1/7/2012 Cust2 1/8/2012
This seems like a left outer join, but it is not returning the expected results. Here is what I am using, but this is not returning every date from the date table as expected.
SELECT o.customer,
d.fulldate
FROM datetable d
LEFT OUTER JOIN orders o
ON d.fulldate = o.orderdate
WHERE d.calendaryear IN ( 2012 );
See Question&Answers more detail:os