SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sum Value with Comparison Month/Year from 2 Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
226 Posts

Posted - 11/14/2012 :  04:45:50  Show Profile  Reply with Quote
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

Edited by - micnie_2020 on 11/14/2012 04:47:49

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  06:51:40  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000