如果在SQL Server 2012中没有数据存在月份，则显示0

Question

我目前正在进行一项查询，以显示过去两年中所有完成项目。

这里是我的查询

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' 

我遇到的问题是，以显示几个月是我们没有完工。

这是我的查询结果

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 

我想是显示年（2016 & 2017年）每月;如果当月没有完成，那么我需要显示该月的0。

我希望我在这里解释一下自己。

干杯 生锈

Answer 1

您可以试试这个。

;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 

Answer 2

要显示0在​​哪里？您可以加入到包含所有预期日期的派生表中：

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]