I am having trouble trying to get the results I need from a left outer join. I have the following tables:
Table1 FiscalYear (smallint) FiscalWeek (tinyint)
Table2 FiscalYear (smallint) FiscalWeek (tinyint) Store (integer) Sales (money)
Table1 has a row for every week in the year, so 52 rows for FY 2005 with the week ranging from 1 to 52. Table 2 has sales entries for each store per week. However, not every store has an entry for each week as they may not have been open.
What I am trying to do is get a result set that has 52 rows for each store containing NULLs where the store did not have a row for that week. I am using a left outer join to retrieve the results. If the result set is limited to 1 store then the results work, but when I want all stores, it isn't what I want since the left outer join gets satisfied by one store that might not satisfy it for another (i.e. if Store 1 has sales for week 1 but store 2 doesn't, store 1 satisfies the join and store 2 does not produce nulls for that week). Here is my sample SQL
| Select | |
| a.FiscalYear | |
| ,a.FiscalWeek | |
| ,b.Store | |
| ,b.Sales | |
| From | |
| Table1 a | |
| Left Outer Join Table2 b On b.FiscalYear = a.FiscalYear and b.FiscalWeek = a.FiscalWeek | |
| Order By | |
| a.FiscalYear, b.Store, a.FiscalWeek |
Any clues
Weekly Sales Join
dioptre
jd_scribe
What you want is the cross product of Fiscal Years/Weeks and Stores. If you have a separate Store table, you can use that to get a list of stores. Otherwise, use a derived table from a Distinct select on Table2.
Select
a.FiscalYear,
a.FiscalWeek,
b.Store,
c.Sales
From
Table1 a
Cross Join ( Select Distinct Store from Table2 ) b
Left Join Table2 c
On a.FiscalYear = c.FiscalYear
And a.FiscalWeek = c.FiscalWeek
And b.Store = c.Store
Order by a.FiscalYear, a.FiscalWeek, B.Store