How do I add missing Months?

Question

I have a query that pulls the Sales by Customer by Fiscal Month. However, I have some Customers that did not make purchases in a given month. For those situations, I would like the MonthNum field to have a 0 for the SalesDlr value, but the MonthNum value still needs to be the actual Month Number. Currently, I do not get a MonthNum value in the query below:

SELECT
    a.Customer,
    a.CustomerName,
    a.MonthNum,
    a.FiscalYear,
    a.SalesDlr
FROM
(SELECT
    sd.SBCUST AS Customer,
    sd.RMNAME AS CustomerName,
    fc.FiscalMonthNum AS MonthNum,
    fc.FiscalYear,
    SUM(sd.SBEPRC) AS SalesDlr
FROM
    dbo.SalesData sd
    LEFT OUTER JOIN dbo.FiscalCalendar fc ON fc.FiscalDate = sd.SBINDT
WHERE
    sd.SBTYPE = 'O'
AND
    sd.SBINDT > '2012-12-31'
AND
    sd.SBCLS NOT IN ('1500')
GROUP BY
    sd.SBCUST,
    sd.RMNAME,
    fc.FiscalMonthNum,
    fc.FiscalYear
)a
GROUP BY
    a.Customer,
    a.CustomerName,
    a.MonthNum,
    a.FiscalYear,
    a.SalesDlr
ORDER BY
    a.Customer,
    a.MonthNum,
    a.FiscalYear

How can I fix that?

Blog series you may find useful: http://www.sqlperformance.com/generate-a-set-1 http://www.sqlperformance.com/generate-a-set-2 http://www.sqlperformance.com/generate-a-set-3 — Aaron Bertrand, Jun 19 '13 at 16:50
" For those situations, I would like the MonthNum field to have a 0 for the value " .... are you sure ?? or do you want 0 for the SalesDlr — Talasila, Jun 19 '13 at 18:38
I want 0 for the SalesDlr, but I also need the MonthNum field to have a value for the Month where there are no sales. For example, is Customer ABC started buying in April 2013, then I need January 2013 to have a SalesDlr value of 0 as well as February and March. — tsqln00b, Jun 19 '13 at 19:22
Shouldn't your query then do FiscalCalendar left join SalesData? Seems that you want the months for which there are no sales so switching the order would make sense. — Peter Schott, Jun 20 '13 at 19:36

John Tseng · Accepted Answer · 2013-06-24T20:32:05.263

1

I am guessing that your SalesData does not have an entry every customer in every month. If a customer Curtis does not make a purchase in month May, then he probably does not have any entries in SalesData for the month of May. Hence, when you left join with SalesData on the left, Curtis will not have a row for May.

Switching the order of the left join would not be sufficient, either, since that would generate only one date for the missing customer. Hence, you really want a cross join between the distinct customers and distinct months, and then get the sum for those:

with FilteredSalesData as (
    SELECT  
        sd.SBCUST as Customer,
        sd.RMName as CustomerName,
        sd.SBEPRC,
        fc.FiscalMonthNum as MonthNum,
        fc.FiscalYear
    FROM dbo.SalesData sd
    join FiscalCalendar fc ON fc.FiscalDate = sd.SBINDT
        WHERE
            sd.SBTYPE = 'O'
        AND
            sd.SBCLS NOT IN ('1500')
), DistinctCustomers as (
    select distinct 
        SBCUST as Customer,
        RMName as CustomerName
    from FilteredSalesData
), DistinctMonths as (
    select distinct
        FiscalMonthNum as MonthNum,
        FiscalYear
    From FiscalCalendar
    WHERE FiscalDate > '2012-12-31'
)
select
    Customer,
    CustomerName,
    MonthNum,
    FiscalYear,
    SUM(sd.SBEPRC) as SalesDlr
From DistinctCustomers dc
CROSS JOIN DistinctMonths dm
left Join FilteredSalesData sd 
    on sd.customer = dc.Customer 
    and sd.CustomerName = dc.CustomerName
    and sd.MonthNum = dm.MonthNum
    and sd.FiscalYear = dm.FiscalYear
group by Customer, CustomerName, MonthNum, FiscalYear
order by Customer, MonthNum, FiscalYear

edited Jun 24 '13 at 20:32

answered Jun 21 '13 at 00:28

John Tseng

6,262
2
27
35

You are correct in the statement that it does not have an entry for every customer in every month. However, your answer still does not produce the desired results. – tsqln00b Jun 24 '13 at 16:52
@tsqln00b Good point. I missed the where filters. You can first filter SalesData with a sub query before the join. I will edit my solution when I get back to a computer. – John Tseng Jun 24 '13 at 18:22
@tsqln00b I've updated the solution with the filters inside a subquery. With the filters outside, it would filter out all the NULL rows that left join kept. – John Tseng Jun 24 '13 at 18:50
I still don't get a 0 value in a month where there were no sales. – tsqln00b Jun 24 '13 at 18:52
What I am looking for would be this: 123 Acme Co. 1 2013 $10000 123 Acme Co. 2 2013 $5000 123 Acme Co. 3 2013 $0 123 Acme Co. 4 2013 $5000 123 Acme Co. 5 2013 $7500 123 Acme Co. 6 2013 $0. When I run the query, I don't get a result for months 3 and 6. – tsqln00b Jun 24 '13 at 18:54
@tsqln00b I see what I had been missing now. I think the latest update will have a row for every customer X month. – John Tseng Jun 24 '13 at 20:32

How do I add missing Months?

1 Answers1