COUNT problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

COUNT problem

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2011-09-07 : 15:13:30

I have the following SQL statement which works fine:

@AgingDate date,
@Company varchar(10),
@PastDueAmount decimal(18,2)

AS
DECLARE
@startPrevYear date,
@endPrevYear date,

@startThisYear date,
@endThisYear date

set @startPrevYear = DATEADD(Year, DATEDIFF(Year, 0, @AgingDate) -1, 0)
set @endPrevYear = DATEADD(Day, -1, DATEADD(Year, 1, @startPrevYear))

set @startThisYear = DATEADD(yy, DATEDIFF(yy, 0, @AgingDate), 0)
set @endThisYear = DATEADD(dd ,-1,DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, getdate()) +1, 0)))
SELECT
CTR.ACCOUNTNUM
,SUM(CTR.AMOUNTCUR * -1) as CreditNoteTotal
,SUM(case when CTR.TransDate between @startPrevYear and @endPrevYear then CTR.AMOUNTCUR * -1 else 0 end) as CreditNoteTotalPrevYear
,SUM(case when CTR.TransDate between @startThisYear and @endThisYear then CTR.AMOUNTCUR * -1 else 0 end) as CreditNoteTotalThisYear
FROM CUSTTrans CTR INNER JOIN
CUSTTABLE CT ON CTR.ACCOUNTNUM = CT.ACCOUNTNUM
AND CTR.DATAAREAID = CT.DATAAREAID
AND CTR.AmountCur < @PastDueAmount * -1

WHERE CTR.DATAAREAID = @Company
AND CTR.TransDate <= @AgingDate
AND TRANSTYPE = 2
AND CTR.AMOUNTCUR < 0
GROUP BY CTR.ACCOUNTNUM
ORDER BY CTR.ACCOUNTNUM

I want to count up the number of transactions as well and call it CreditNoteCountPrevYear and CreditNoteCountThis Year. But I'm not sure how to do this. Can someone help me out?

Bob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-07 : 15:25:48

,SUM(case when CTR.TransDate between @startPrevYear and @endPrevYear then 1 else 0 end) as CreditNoteCountPrevYear
,SUM(case when CTR.TransDate between @startThisYear and @endThisYear then 1 else 0 end) as CreditNoteCountThisYear

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources