Compute data between First and Last Day Of Last Month

Question

I want to compute the certain column with fix range but I can't figure out the right way.

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

score 2 · Accepted Answer · answered Jan 09 '20 at 06:20

Something like this:

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

For SQL Server 2012+ you can use IIF:

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

I find it more clear for such cases.

Compute data between First and Last Day Of Last Month

1 Answers1