处理ssrs图表的sql查询表达式中不存在的值

Question

我在ssrs折线图中使用以下查询。它根据每个订单日期计算每月记录的订单数量。

我的问题是，当一个月没有订单时，而不是说零或为空，它将一起删除该月的行。我希望它将其计为零，但也可以使用null。

基本上，无论是否包含信息，我都希望总是有十二行。

我怎样才能解决这个问题？有没有可以使用的表达方式？还是我遗漏了一些显而易见的东西？

SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Worker_ID1 IS NULL OR @Worker_ID1 = Worker.ID THEN 1 END) AS 'Worker1',
COUNT(CASE WHEN @Worker_ID2 IS NULL OR @Worker_ID2 = Worker.ID THEN 1 END) AS 'Worker2',
COUNT(CASE WHEN @Worker_ID3 IS NULL OR @Worker_ID3 = Worker.ID THEN 1 END) AS 'Worker3',
COUNT(CASE WHEN @Worker_ID4 IS NULL OR @Worker_ID4 = Worker.ID THEN 1 END) AS 'Worker4',
COUNT(CASE WHEN @Worker_ID5 IS NULL OR @Worker_ID5 = Worker.ID THEN 1 END) AS 'Worker5'

FROM Ord
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
JOIN Grouping ON Ord.Grouping_ID = Grouping.ID
JOIN Worker ON Grouping.Worker_ID = Worker.ID

WHERE @Year = YEAR(Ord.OrdDate)
AND (@ProdType_ID IS NULL OR @ProdType_ID = ProdType.ID)

GROUP BY MONTH(Ord.OrdDate)

Answer 1

通过删除where子句并在count聚合中进行过滤，使其正常工作。

SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID1 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker1',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID2 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker2',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID3 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker3',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID4 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker4',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID5 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker5'

FROM Ord
JOIN Grouping ON Ord.Grouping_ID = Grouping.ID
JOIN Worker ON Grouping.Worker_ID = Worker.ID
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID

GROUP BY MONTH(Ord.OrdDate)