Need help with dates - 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)

Need help with dates

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2011-08-31 : 16:27:07

I have the following simple SQL statement:

@AgingDate date,
@Company varchar(10)
AS
SELECT
CTR.ACCOUNTNUM
,CT.Name
,sum(CTR.AMOUNTCUR * -1) as CreditNoteTotal

FROM CUSTTrans CTR INNER JOIN
CUSTTABLE CT ON CTR.ACCOUNTNUM = CT.ACCOUNTNUM
AND CTR.DATAAREAID = CT.DATAAREAID

WHERE CTR.DATAAREAID = @Company
AND CTR.DueDate <= @AgingDate
AND TRANSTYPE = 2
AND CTR.AMOUNTCUR < 0
GROUP BY CTR.ACCOUNTNUM, CT.NAME
ORDER BY CTR.ACCOUNTNUM, CT.NAME

This all works. But I want to add a new coulmn. I want this to be the SUM for CTR.AccountCur for the prior year. I want to use my parameter @AgingDate and calculate the total for the prior year. So if 8-31-2011 is entered, I want a new column with the total of CTR.AccountCur for all of 2010. I can't seem to work this out.

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-08-31 : 17:16:53

[CODE]declare
@startPrevYear date,
@endPrevYear date

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

SELECT
CTR.ACCOUNTNUM
,CT.Name
,sum(CTR.AMOUNTCUR * -1) as CreditNoteTotal
,SUM(case when CTR.DueDate between @startPrevYear and @endPrevYear then CTR.AMOUNTCUR * -1 else 0 end) as CreditNoteTotalPrevYear
FROM CUSTTrans CTR INNER JOIN
CUSTTABLE CT ON CTR.ACCOUNTNUM = CT.ACCOUNTNUM
AND CTR.DATAAREAID = CT.DATAAREAID

WHERE CTR.DATAAREAID = @Company
AND CTR.DueDate <= @AgingDate
AND TRANSTYPE = 2
AND CTR.AMOUNTCUR < 0
GROUP BY CTR.ACCOUNTNUM, CT.NAME
ORDER BY CTR.ACCOUNTNUM, CT.NAME[/CODE]

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 23:59:36

i'm not seeing any date criteria in your original query. How are you ensuring it will get data for current year only?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources