Get flag changes - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Get flag changes

Author

Topic

JeanneZhang
Starting Member

5 Posts

Posted - 2008-01-09 : 14:16:59

I need to have a SQL to catch the flag changes based on the ID. The table data like:

ID Update_Date Flag
100 12/03/2007 Y
100 12/05/2007 Y
100 12/09/2007 N
100 12/10/2007 N
100 12/19/2007 Y
101 12/03/2007 N
101 12/09/2007 Y
101 12/21/2007 Y
102 12/07/2007 Y
102 12/18/2007 Y
102 12/24/2007 N
102 12/28/2007 N

The result should be:

ID Update_Date Flag
100 12/09/2007 N
100 12/19/2007 Y
101 12/09/2007 Y
102 12/24/2007 N

Thanks a lot for any help.

Jeanne

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-09 : 15:20:26

declare @t table ( ID int, Update_Date datetime, Flag char(1) )

insert @t ( ID, Update_Date, Flag )
select 100, '12/03/2007', 'Y' union all
select 100, '12/05/2007', 'Y' union all
select 100, '12/09/2007', 'N' union all
select 100, '12/10/2007', 'N' union all
select 100, '12/19/2007', 'Y' union all
select 101, '12/03/2007', 'N' union all
select 101, '12/09/2007', 'Y' union all
select 101, '12/21/2007', 'Y' union all
select 102, '12/07/2007', 'Y' union all
select 102, '12/18/2007', 'Y' union all
select 102, '12/24/2007', 'N' union all
select 102, '12/28/2007', 'N'

select t2.ID, t2.Update_Date, t2.Flag
from (
select row_number() over(partition by ID order by Update_Date) rownum, *
from @t ) t1
join (
select row_number() over(partition by ID order by Update_Date) rownum, *
from @t ) t2 on t1.ID = t2.ID and t1.rownum + 1 = t2.rownum
where t1.Flag <> t2.Flag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources