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)
 Shift value to begin fiscal year 2012/04
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
225 Posts

Posted - 12/20/2012 :  20:45:27  Show Profile  Reply with Quote
Hi All,

How to shift the price value if the ID having Datt 2012/04 else remain same.

Before:
ID Datt Amt Price
1 2012/01 3 50
1 2012/02 3 0
1 2012/03 3 0
1 2012/04 3 0
2 2011/01 5 90
2 2011/02 5 0

After:
ID Datt Amt Price
1 2012/01 3 0
1 2012/02 3 0
1 2012/03 3 0
1 2012/04 3 50
2 2011/01 5 90
2 2011/02 5 0

Please advise.

Thank you.

Regards,
Micheale

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  20:51:04  Show Profile  Reply with Quote
Your question is not clear.
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
225 Posts

Posted - 12/20/2012 :  20:56:32  Show Profile  Reply with Quote
Hi,

To explain further,

eg:

i need all the group ID which having Datt 2012/04, the previous Price value will shift to Datt 2012/04
ID Datt Price
1 2012/04 50

and the previous record price will zerolize
ID Datt Price
1 2012/01 0

For ID 2, there is no Datt 2012/04. Mean the value remain as Current without changing the price value.

Hope i make my question clear.

Thank you.

Regards,
Micheale
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  21:09:00  Show Profile  Reply with Quote
Select T.ID,T.Date,T.Amt,Case When T.Date = '2012/04' then P.Price Else T.Price End as Price
from Table T
left join
(
Select ID,MIN(Date) MinDate,MIN(Price) Price
from Table
Group by ID
)P on P.ID = T.ID and T.Date = P.MinDate
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.03 seconds. Powered By: Snitz Forums 2000