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

Dan Angelo Alcanar

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

Yogesh Sharma

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related