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)
 need to get a date when the value changes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpash
Posting Yak Master

103 Posts

Posted - 02/26/2013 :  02:44:30  Show Profile  Reply with Quote
id asofdate desc
1 2/25/2012 a
1 2/24/2012 a
1 2/23/2012 a
1 2/22/2012 a
1 2/21/2012 b
2 2/25/2012 a
2 2/24/2012 b
2 2/25/2012 b


output--
1 2/22/2012(most recent when value changes from b to a)
2 2/25/2012(most recent when value change from b to a)

note:desc should not be null

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  02:59:03  Show Profile  Reply with Quote

;WITH CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY asofdate,desc) AS Seq,*
FROM table
)
SELECT id,asofdate
FROM
(
SELECT c1.id,c1.asofdate,ROW_NUMBER() OVER (PARTITION BY c1.id ORDER BY c1.asofdate DESC) AS Rn
FROM CTE c1
INNER JOIN CTE c2
On c2.id = c1.id
AND c2.Seq = c1.Seq+1
AND c2.desc <> c1.desc             
)t
WHERE Rn=1




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

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.05 seconds. Powered By: Snitz Forums 2000