好的,首先,向所有的开发人员问好,
这个问题我一直在绞尽脑汁,我有一个存储过程,每月从我的客户那里获得债务,并将其显示在我的仪表板上,所以如果我有1个月未支付的0个文档,那么我的图表上就不会显示那个月。
基本上我想要本年度的图表,就像现在我们在7月份,我希望它显示从1月到7月的月份,如果有月份的债务为0,那么我希望结果部署为0。
我粘贴了我存储的实际过程,所以我有一些来自更有经验的开发人员的指示,我已经搜索过了,但我不能很好地解决它,非常感谢提前。
SELECT
DATENAME(MONTH, [Documents].[Date]) AS 'Name',
SUM([Documents].[Balance]) AS 'Value'
FROM
[Documents]
WHERE
[Documents].[Balance] <> 0 AND
[Documents].[MovementType] = COALESCE(@movementType, [Documents].[MovementType])
GROUP BY
DATENAME(MONTH, [Documents].[Date])
发布于 2018-07-13 01:24:18
我可以通过使用COALESCE
和Table Value Constructor
来实现: Microsoft SQL Server2008或更高版本支持'Table Value Constructor‘:
SELECT
DATENAME(MONTH, [Documents].[Date]) AS 'Name',
COALESCE(SUM([Documents].[Balance]), 0) AS 'Value'
FROM
[Documents]
RIGHT JOIN
(Select v.valueId From (values ('January'), ('February'), ('March'), ('April'), ('May'), ('June'), ('July'), ('August'), ('September'), ('October'), ('November'), ('December')) v(valueId)) as vm
on vm.valueId = DATENAME(MONTH, [Documents].[Date])
WHERE
[Documents].[Balance] <> 0 AND
[Documents].[MovementType] = COALESCE(@movementType, [Documents].[MovementType])
GROUP BY
DATENAME(MONTH, [Documents].[Date])
注意:我还没有测试它,但它应该可以工作
发布于 2018-07-13 02:35:46
这也可能行得通。
DECLARE @MONTH TABLE(Month INT)
DECLARE @COUNT INT
SET @COUNT = 0
WHILE @COUNT <> 12
BEGIN
SET @COUNT = @COUNT + 1
INSERT INTO @MONTH VALUES (@COUNT)
END
SELECT m.Month AS 'Name',SUM(ISNULL([Documents].[Balance],0)) AS 'Value'
FROM @MONTH m
LEFT OUTER JOIN [Documents] ON m.Month = DATEPART(Month,[Documents].[Date])
WHERE [Documents].[Balance] <> 0 AND
[Documents].[MovementType] = COALESCE('', [Documents].[MovementType])
GROUP BY m.Month
https://stackoverflow.com/questions/51311142
复制相似问题