CODEWITHSUNDEEP
sqldatecount
RustyHamster
RustyHamster

Reputation: 359

Show 0 if no data exists for month in SQL Server 2012

I am currently working on a query to show all completions we have had over the past 2 years.

Here is my query

SELECT 
    SigD.actualCompletionDate,
    MONTH(SigD.actualCompletionDate) AS [Month Completed],
    YEAR(SigD.actualCompletionDate) AS [Year Completed]
FROM 
    [BPS].[dbo].tbl_buildLoanSignificantDate AS SigD
INNER JOIN 
    tbl_buildLoan AS bl ON SigD.BuildloanId = bl.BuildloanId
INNER JOIN 
    tbl_buildLoanMortgage AS blm ON SigD.BuildloanId = blm.BuildloanId
INNER JOIN 
    tbl_Broker AS brk ON bl.BrokerId = brk.BrokerId
LEFT JOIN 
    tbl_firmNetwork AS firm ON brk.firmNetworkID  = firm.firmNetworkID
WHERE 
    SigD.actualCompletionDate BETWEEN '01/01/2016' AND GETDATE() 
    AND blm.mortgageStatusID = 7 
    AND bl.caseTypeID = 2
    AND firm.name = 'First Complete'

The problem I'm having is to show the months were we had no completions.

This is the result of my Query

actualCompletionDate    Month Completed Year Completed
------------------------------------------------------
2016-05-12                     5               2016
2016-01-21                     1               2016
2016-05-26                     5               2016
2016-04-12                     4               2016
2016-08-22                     8               2016
2017-07-26                     7               2017
2016-05-19                     5               2016

What I want is to show each month of the years (2016 & 2017); if there was no completions in that month, then I need to show a 0 for that month.

I hope I have explained myself here.

Cheers Rusty

Upvotes: 1

Views: 174

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

could you try this.

;WITH FULLDATE AS (
SELECT * FROM 
    (VALUES(2016),(2017)) YEARS(Y) CROSS JOIN
    (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) MONTHS(M)
)
SELECT 
    X.actualCompletionDate,
    F.M AS [Month Completed],
    F.Y AS [Year Completed]
FROM 
    FULLDATE F
    LEFT JOIN (
        SELECT 
        SigD.actualCompletionDate,
        Month(SigD.actualCompletionDate) AS [Month Completed],
        YEAR(SigD.actualCompletionDate) AS [Year Completed]
        FROM 
        [BPS].[dbo].tbl_buildLoanSignificantDate AS SigD 
        INNER JOIN tbl_buildLoan AS bl  ON SigD.BuildloanId = bl.BuildloanId
        INNER JOIN tbl_buildLoanMortgage AS blm ON SigD.BuildloanId = blm.BuildloanId
        INNER JOIN tbl_Broker AS brk ON bl.BrokerId = brk.BrokerId
        INNER JOIN tbl_firmNetwork AS firm ON brk.firmNetworkID  = firm.firmNetworkID
        WHERE SigD.actualCompletionDate BETWEEN '01/01/2016' AND GETDATE() 
            and blm.mortgageStatusID = 7 
            and bl.caseTypeID = 2
            and firm.name = 'First Complete'
    ) AS X ON F.M = X.[Month Completed] AND F.Y = X.[Year Completed]
    ORDER BY F.Y , F.M

Upvotes: 1

sagi
sagi

Reputation: 40481

To show 0 where? You can just join to a derived table that contains all the expected dates:

SELECT COALESCE(p.actualCompletionDate,<DefaultDate>) as actualCompletionDate,
 --replace <defaultDate> with what you want to show if there's no data in this month
       [s.Month],
       [s.Year]
FROM (SELECT '1' as [month],'2016' as [year] 
      UNION ALL
      SELECT '2' ,'2016' 
      .... --As many dates as you need
      ) s
LEFT JOIN (Your Query Here) p
 ON(p.month = s.[month completed] AND p.year = s.[year completed]

Upvotes: 1

Related Questions