计算上个月第一天和最后一天之间的数据

Question

我想计算具有固定范围的某个列，但我无法找出正确的方法。

DECLARE @FirstDayOfLastMonth DATETIME = dateadd(month, - 1, format(getutcdate(), 'yyyy-MM-01'))
DECLARE @LastDayOfLastMonth DATETIME = EOMONTH(@FirstDayOfLastMonth)
--SELECT SUM(Debit)FROM vGLTransaction_Detail WHERE DatePosted > @FirstDayOfLastMonth AND DatePosted < @LastDayOfLastMonth GROUP BY AcctCode
----This works alone and this is my desired output
SELECT AcctCode
,(
SELECT SUM(Debit)
FROM vGLTransaction_Detail
WHERE DatePosted > @FirstDayOfLastMonth
AND DatePosted < @LastDayOfLastMonth
GROUP BY AcctCode
) [Beginning] --i want something like this
,SUM(Debit) [Debit]
,SUM(Credit) [Credit]
,SUM(Credit) [Ending]
FROM dbo.vGLTransaction_Detail
GROUP BY AcctCode

Answer 1

像这样：

DECLARE @LastDayOfLastMonth DATETIME = EOMONTH(@FirstDayOfLastMonth)
--SELECT SUM(Debit)FROM vGLTransaction_Detail WHERE DatePosted > @FirstDayOfLastMonth AND DatePosted < @LastDayOfLastMonth GROUP BY AcctCode
----This works alone and this is my desired output
SELECT AcctCode
,SUM(CASE WHEN  DatePosted > @FirstDayOfLastMonth AND DatePosted < @LastDayOfLastMonth THEN Debit ELSE 0 END)
,SUM(Debit)[Debit]
,SUM(Credit)[Credit]
,SUM(Credit)[Ending]
FROM dbo.vGLTransaction_Detail
GROUP BY AcctCode

对于 SQL Server 2012+，您可以使用IIF：

SUM(IIF(atePosted > @FirstDayOfLastMonth AND DatePosted < @LastDayOfLastMonth, Debit, 0))

我发现这种情况更清楚。