Sum Value with Comparison Month/Year from 2 Date

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)

Sum Value with Comparison Month/Year from 2 Date

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-11-14 : 04:45:50

Hi ALl,

I am trying to group the amount if less than posted date as below:-

Before
------
ID Description Datt DatePur PostedDate Amt
1365 A 2009/01 2009-01-31 2009-03-31 460606.166666667
1365 A 2009/02 2009-01-31 2009-03-31 460606.166666667
1365 A 2009/03 2009-01-31 2009-03-31 460606.166666667
1365 A 2009/04 2009-01-31 2009-03-31 460606.166666667
1365 A 2009/05 2009-01-31 2009-03-31 460606.166666667

After (Based on Posted Date start on 2009/03, lung sum Jan & Feb Amt to Mar together, the rest remain as no change)
-------------------------------------------------------
ID Description Datt DatePur PostedDate Amt
1365 A 2009/01 2009-01-31 2009-03-31 0
1365 A 2009/02 2009-01-31 2009-03-31 0
1365 A 2009/03 2009-01-31 2009-03-31 460606.166666667 * 3
1365 A 2009/04 2009-01-31 2009-03-31 460606.166666667
1365 A 2009/05 2009-01-31 2009-03-31 460606.166666667

Can anyone tell me how to write the sql statement?

Please advise.

Thank you.

Regards,
Micheale

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 06:51:40

Could be something like this:

SELECT
	*,
	CASE
		WHEN Datt < '2009/03' THEN 0
		WHEN Datt = '2009/03' THEN 
			SUM(CASE WHEN Datt <= '2009/03' THEN Amt ELSE 0 END)
			OVER (PARTITION BY id)
		ELSE Amt
	END 
FROM
	YourTable

If Datt is a date, you should use a date/datetime type. IN the query above, I am assuming it is a string because it seemed so.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources