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 to get a date when the value changes

Author  Topic 

shilpash
Posting Yak Master

103 Posts

Posted - 2013-02-26 : 02:44:30
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

52326 Posts

Posted - 2013-02-26 : 02:59:03
[code]
;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

[/code]


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

Go to Top of Page
   

- Advertisement -