Showing one row for each calendar week in SQL

I have a SQL query which pulls unit sales by item, by week:

SELECT sls_vendor, 
   sls_item, 
   sls_units, 
   DATEPART(week, sls_week) AS sls_date 
FROM   mytable 

Assume I'm looking at a 8 week period, but not every item/vendor combination has a full 8 weeks of sales. However I need my query to show a null value in that instance. So the query would return 8 rows for each item/vendor combination regardless of existence.

I tried creating a temp table which has the numbers 28 to 35 and performing a left join on the q开发者_JAVA百科uery above, but that doesn't show null values. The results are no different than running the original query alone.

I can think of how this would be done using a crosstab/pivot query, but isn't this something the join should be doing?

Edit: Updated to show my join query. Datetable just has 8 rows with 1 incremental number for each calendar week.

SELECT * FROM @datetable
    LEFT JOIN
    (SELECT 
        sls_vendor,
        sls_item,
        sls_units,
        datepart(week,sls_week) AS sls_date
    FROM mytable) AS QRY
    ON temp_week = qry.sls_date

---

Your method should work just fine:

;with mytable as (
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/7/2011' as sls_week union
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/14/2011' as sls_week union
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/21/2011' as sls_week
)
,datetable as (
    select 28 as temp_week union
    select 29 union
    select 30 union
    select 31 union
    select 32 union
    select 33 union
    select 34 union
    select 35
)
SELECT * FROM datetable

    LEFT JOIN

    (SELECT 
        sls_vendor,
        sls_item,
        sls_units,
        datepart(week,sls_week) AS sls_date
    FROM mytable) AS QRY

    ON temp_week=qry.sls_date

Output:

temp_week   sls_vendor  sls_item    sls_units   sls_date
28          NULL        NULL        NULL        NULL
29          NULL        NULL        NULL        NULL
30          NULL        NULL        NULL        NULL
31          NULL        NULL        NULL        NULL
32          NULL        NULL        NULL        NULL
33          1           Test        30          33
34          1           Test        30          34
35          1           Test        30          35

Edit: If you want to include all week values for every sales vendor, include a cross join with the distinct selection of vendors:

;with mytable as (
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/7/2011' as sls_week union
    select 2 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/14/2011' as sls_week union
    select 3 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/21/2011' as sls_week
)
,datetable as (
    select 28 as temp_week union
    select 29 union
    select 30 union
    select 31 union
    select 32 union
    select 33 union
    select 34 union
    select 35
)
SELECT * FROM datetable
cross join (select distinct sls_vendor from mytable) v

    LEFT JOIN

    (SELECT 
        sls_vendor,
        sls_item,
        sls_units,
        datepart(week,sls_week) AS sls_date
    FROM mytable) AS QRY

    ON temp_week=qry.sls_date and v.sls_vendor=qry.sls_vendor

Output:

temp_week   sls_vendor  sls_vendor  sls_item    sls_units   sls_date
28          1           NULL        NULL        NULL        NULL
29          1           NULL        NULL        NULL        NULL
30          1           NULL        NULL        NULL        NULL
31          1           NULL        NULL        NULL        NULL
32          1           NULL        NULL        NULL        NULL
33          1           1           Test        30          33
34          1           NULL        NULL        NULL        NULL
35          1           NULL        NULL        NULL        NULL
28          2           NULL        NULL        NULL        NULL
29          2           NULL        NULL        NULL        NULL
30          2           NULL        NULL        NULL        NULL
31          2           NULL        NULL        NULL        NULL
32          2           NULL        NULL        NULL        NULL
33          2           NULL        NULL        NULL        NULL
34          2           2           Test        30          34
35          2           NULL        NULL        NULL        NULL
28          3           NULL        NULL        NULL        NULL
29          3           NULL        NULL        NULL        NULL
30          3           NULL        NULL        NULL        NULL
31          3           NULL        NULL        NULL        NULL
32          3           NULL        NULL        NULL        NULL
33          3           NULL        NULL        NULL        NULL
34          3           NULL        NULL        NULL        NULL
35          3           3           Test        30          35

---

Does it work for you?

SELECT sls_vendor, 
   sls_item, 
   sls_units, 
   DATEPART(WEEK, sls_week) AS sls_date 
FROM (
    SELECT VALUE = 28 UNION ALL 
    SELECT VALUE = 29 UNION ALL 
    SELECT VALUE = 30 UNION ALL  
    SELECT VALUE = 31 UNION ALL  
    SELECT VALUE = 32 UNION ALL  
    SELECT VALUE = 33 UNION ALL  
    SELECT VALUE = 34 UNION ALL 
    SELECT VALUE = 35
) dates
LEFT JOIN mytable m
ON dates.value = DATEPART(WEEK, m.sls_week)

---

The following query works in Data.StackExchange. See here. It gets the top Post per week by score.

WITH weeksyears 
     AS (SELECT w.NUMBER AS week, 
                y.NUMBER AS year
         FROM   (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 1 AND 52) w, 
                (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 2008 AND 2012) y), 
     topPostPerWeek
     AS (SELECT score, 
                Datepart(week, creationdate)       week, 
                Datepart(YEAR, creationdate)       YEAR, 
                Row_number() OVER (PARTITION BY Datepart(wk, creationdate), 
                Datepart( 
                   YEAR, 
                creationdate) ORDER BY score DESC) row 
         FROM   posts) 
SELECT * 
FROM   weeksyears wy 
       LEFT JOIN topPostPerWeekt 
         ON wy.week = t.week 
            AND wy.YEAR = t.YEAR 
WHERE  row = 1 
OR row IS NULL 
ORDER BY wy.YEAR, wy.WEEK
​

Every row prior to the 38 week in 2008 is empty except for week and year. As well as the rows after the 35 week in 2011.

However if you edit the query and remove OR row IS NULL the query will act just as if it were an INNER JOIN

My guess is that there's somthing in your WHERE that's referring to the "RIGHT" table. Just add OR [rightTable.field] IS NULL and you'll be fine.