CODEWITHSUNDEEP
sqlsql-server
Dan Angelo Alcanar
Dan Angelo Alcanar

Reputation: 379

MS SQL - JOIN show months that doesn't have data

I am having a new problem with SQL JOINS.

Here is my scenario.

I have a report, where I need to show sales performance for a specific customer. The report shows the sales performance per month.

Please look at my result set:

AFP035

This is the sales report for customer AFP035. As you can see there are months that are not showing. I am required to include those months that doesn't have data(NULL).

Here what I have tried. I created a month table where I maintained the months.

table months

And then I did a LEFT JOIN. Seems strange because it doesn't show the desired result. From what I understand LEFT JOIN should show the NULL values. And I am wrong.

Please see my query:

select  CustNm as company,
        Cust as customer,
        Cust as custCode,
        Mon,
        case
        when Mon = 1 THEN 'January'
        when Mon = 2 THEN 'February'
        when Mon = 3 THEN 'March'
        when Mon = 4 THEN 'April'
        when Mon = 5 THEN 'May'
        when Mon = 6 THEN 'June'
        when Mon = 7 THEN 'July'
        when Mon = 8 THEN 'August'
        when Mon = 9 THEN 'September'
        when Mon = 10 THEN 'October'
        when Mon = 11 THEN 'November'
        when Mon = 12 THEN 'December'
        end as 'month',
        Yr as 'year',
        Mon as monthCheck,

        case
        when NetSales IS NULL THEN 0
        else NetSales
        end as netSales,

        case
        when PrvYrSales IS NULL THEN 0
        else PrvYrSales
        end as prevYearSales,

        case
        when SalesGrwth IS NULL THEN 0
        else SalesGrwth * 100
        end as salesGrowth,

        case
        when YrBfrLst IS NULL THEN 0
        else YrBfrLst
        end as yearBeforeLast,

        case
        when BfrLstGrwth IS NULL THEN 0
        else BfrLstGrwth * 100
        end as yearBeforeLastGrowth

from    BigEMasterData.dbo.monthtmp as a

        left outer join BigESales.dbo.tbl_ReportCOMPANYperCust as b
        on b.Mon = a.monthNo
        or b.Mon is null

The result is the screenshot above.

Would you help me with this or at least enlighten me why I am not getting the desired result?

Thank you so much. Any suggestion would be highly appreciated.

EDIT: SOLVED: with Yogesh's help Thankyou.

;WITH CTE AS (
    SELECT * FROM tbl_ReportCOMPANYperCust r 
    WHERE Cust = 'AFP035'
	and Yr = 2016
)

SELECT 
       r.CustNm as company, r.Cust as customer, r.Cust as custCode, 
       a.monthNo, a.monthName, r.Yr as Year, 
       COALESCE(NetSales, 0) as netSales, 
       COALESCE(PrvYrSales, 0) as prevYearSales, 
       COALESCE(SalesGrwth*100, 0) as salesGrowth, 
       COALESCE(YrBfrLst, 0) as yearBeforeLast, 
       COALESCE(BfrLstGrwth*100, 0) as yearBeforeLastGrowth  
FROM BigEMasterData.dbo.monthtmp a
LEFT OUTER JOIN CTE r on r.Mon = a.monthNo

Here is the result. : enter image description here

Upvotes: 1

Views: 213

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Let me correct your query, in order to get the all months then your calendar table should first table 

SELECT 
       r.CustNm as company, r.Cust as customer, r.Cust as custCode, 
       a.monthNo, a.MontName, r.Yr as Year, 
       COALESCE(NetSales, 0) as netSales, 
       COALESCE(PrvYrSales, 0) as prevYearSales, 
       COALESCE(SalesGrwth*100, 0) as salesGrowth, 
       COALESCE(YrBfrLst, 0) as yearBeforeLast, 
       COALESCE(BfrLstGrwth*100, 0) as yearBeforeLastGrowth  
FROM monthtmp a
LEFT OUTER JOIN tbl_ReportCOMPANYperCust r on r.Mon = a.monthNo

Use ANSI SQL Standard COALESCE() function to check NULL values

In order to get all months name use CTE or Subquery which could hold the filtered records

;WITH CTE AS (
    SELECT * FROM tbl_ReportCOMPANYperCust r 
    WHERE Cust = 'AFP035'
    and Yr = 2016
)

SELECT 
       COLAESCE(r.CustNm, LAG(r.CustNm) OVER (ORDER BY a.monthNo)) as company, 
       COLAESCE(r.Cust, LAG(r.Cust) OVER (ORDER BY a.monthNo)) as customer, 
       COLAESCE(r.Cust, LAG(r.Cust) OVER (ORDER BY a.monthNo)) as custCode, 
       a.monthNo, a.monthName, r.Yr as Year, 
       COALESCE(NetSales, 0) as netSales, 
       COALESCE(PrvYrSales, 0) as prevYearSales, 
       COALESCE(SalesGrwth*100, 0) as salesGrowth, 
       COALESCE(YrBfrLst, 0) as yearBeforeLast, 
       COALESCE(BfrLstGrwth*100, 0) as yearBeforeLastGrowth  
FROM BigEMasterData.dbo.monthtmp a
LEFT OUTER JOIN CTE r on r.Mon = a.monthNo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You would appear to want:

SELECT c.company, c.customer, c.custCode, 
       m.monthNo, m.monthName, c.Year, 
       COALESCE(r.NetSales, 0) as netSales,
       COALESCE(r.PrvYrSales, 0) as prevYearSales, 
       COALESCE(r.SalesGrwth*100, 0) as salesGrowth,
       COALESCE(r.YrBfrLst, 0) as yearBeforeLast, 
       COALESCE(r.BfrLstGrwth*100, 0) as yearBeforeLastGrowth  
FROM (SELECT DISTINCT r.CustNm as company, r.Cust as customer, r.Cust as custCode, r.Yr as Year
      FROM tbl_ReportCOMPANYperCust
      WHERE c.Cust = 'AFP035'
     ) c CROSS JOIN
     BigEMasterData.dbo.monthtmp m LEFT OUTER JOIN 
     tbl_ReportCOMPANYperCust r 
     ON r.Mon = m.monthNo AND r.Cust = c.customer
ORDER BY m.monthNo;

Notice the CROSS JOIN. This brings in the initial columns, so you have them even for months that do not match the data.

Upvotes: 1

Related Questions