CODEWITHSUNDEEP
sqlcounting
Zak Fischer
Zak Fischer

Reputation: 151

SQL - Summarizing Monthly Sales Data

I have two tables that I need to combine, but I can't get to seem the joins to work. The picture has three tables

_date_array2: Has a field DateMonthYr that contains all possible date/yr combinations

_sales_summ_tbl__ => Has 5 fields. Only months with sales show up. For example, you see only three records showing up for the second table. There is no 5/2016, for example, because there were no sales for that month.

My goal is to "pad" the second table to have TotalDemand of 0's for months with no sales. I am very close (see the third table), except I cannot get the PartNumber to show up for dates with no sales.

My guess is that it's due to the RIGHT JOIN. But I'm not sure how to handle this. The output I am hoping for is table 3 but with the part number populated for all entries.

enter image description here

And here is my code (the results from running this code are the third/last table in the picture):

SELECT TmpSalesTbl.PartNumber as PartNumber,
tmp_date_array.CreateDateMonth as CreateDateMonth,
tmp_date_array.CreateDateYear as CreateDateYear,
CASE WHEN TmpSalesTbl.TotalDemand is NULL THEN 0 ELSE TmpSalesTbl.TotalDemand END as TotalDemand
FROM #_sales_summ_tbl__ TmpSalesTbl
RIGHT JOIN #_date_array2 tmp_date_array on tmp_date_array.CreateDateMonthYr = TmpSalesTbl.CreateDateMonthYr
ORDER BY tmp_date_array.CreateDateYear, tmp_date_array.CreateDateMonth

Upvotes: 0

Views: 188

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

It is more conventional to place the list of all dates first, then left join to the data and whilst using a case expression is fine an alternative is coalesce(). This should ensure all the wanted months/years display:

SELECT
      tmpsalestbl.PartNumber               AS partnumber
    , tmp_date_array.CreateDateMonth       AS createdatemonth
    , tmp_date_array.CreateDateYear        AS createdateyear
    , COALESCE(tmpsalestbl.TotalDemand, 0) AS totaldemand
FROM #_date_array2 tmp_date_array
LEFT JOIN #_sales_summ_tbl__ tmpsalestbl ON tmp_date_array.CreateDateMonthYr = tmpsalestbl.CreateDateMonthYr
ORDER BY
      tmp_date_array.CreateDateYear
    , tmp_date_array.CreateDateMonth

To populate for evey partnumber, on every month, you will need a new subquery:

select distinct PartNumber #_sales_summ_tbl__

And then cross join that to the years/months so you have a complete set of years/months/parts.

SELECT
      cj.PartNumber                        AS partnumber
    , tmp_date_array.CreateDateMonth       AS createdatemonth
    , tmp_date_array.CreateDateYear        AS createdateyear
    , COALESCE(tmpsalestbl.TotalDemand, 0) AS totaldemand
FROM #_date_array2 tmp_date_array
CROSS JOIN (
      SELECT DISTINCT
            PartNumber FROM #_sales_summ_tbl__
      ) cj
LEFT JOIN #_sales_summ_tbl__ tmpsalestbl ON tmp_date_array.CreateDateMonthYr = tmpsalestbl.CreateDateMonthYr
                                        AND cj.PartNumber = tmpsalestbl.PartNumber
ORDER BY
      tmp_date_array.CreateDateYear
    , tmp_date_array.CreateDateMonth
;

Upvotes: 1

Related Questions