我在ssrs折线图中使用以下查询.它根据每个订单日期计算每月记录的订单数量.
我的问题是,当一个月没有订单时,而不是说零或null它会一起删除该月的行.我更喜欢把它算作零但是null也可以.
基本上,我想总是有12行,无论它们是否包含信息.
我怎样才能解决这个问题？有一个我可以用的表达式吗？或者我错过了一些完全明显的东西？
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)
解决方法
如上所述,你需要一个外连接和某种日历表.这是未经测试的,但我认为对您有用：
with dateCTE as
(
select cast('2012-01-01' as datetime) dateValue -- start date
union all
select DateAdd(mm,1,dateValue)
from dateCTE
where dateValue < '2012-12-30' -- end date
)
SELECT
MONTH(dateCTE.dateValue) AS 'MONTH',COUNT(CASE WHEN @Worker_ID5 IS NULL OR @Worker_ID5 = Worker.ID THEN 1 END) AS 'Worker5'
FROM dateCTE
LEFT JOIN Ord on MONTH(dateCTE.datevalue) = MONTH(Ord.OrdDate)
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
JOIN Groupord ON Ord.Groupord_ID = Groupord.ID
JOIN Worker ON Groupord.Worker_ID = Worker.ID
WHERE (@Year = YEAR(Ord.OrdDate) or ORD.prod_id is null)
AND (@DrugType_ID IS NULL OR @ProdType_ID = ProdType.ID)
GROUP BY MONTH(dateCTE.dateValue)
OPTION (MAXRECURSION 0)
