Out of Sequence Data Elements - 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)

Out of Sequence Data Elements

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-09 : 08:43:12

Cosider the table below.

ID X Y
-- -- --
1 a 11
2 a 12
3 a 14

4 b 10
5 b 16

6 c 7
7 c 8

The Y values of record 3 and 5 are out of sequence . They should have been 13 and 11 respectively.The idea is to Flag cases that are out of sequence within the same group of Y

My desired result is as follows

ID X Y Y2 Flag
-- -- -- -- ----
1 a 11 11 1
2 a 12 12 1
3 a 14 13 0

4 b 10 10 1
5 b 16 11 0

6 c 7 7 1
7 c 8 8 1

Any assistance will be most welcomed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 11:32:20

[code]SELECT t.ID,
t.X,
t.Y,
CASE WHEN t.Y<>t1.Y+1 THEN t1.Y+1
ELSE t.Y
END AS Y2,
CASE WHEN t.Y<>t1.Y+1 THEN 0
ELSE 1
END AS Flag
FROM Table t
OUTER APPLY(SELECT TOP 1 Y
FROM Table
WHERE X=t.X
AND ID < t.ID
ORDER BY ID DESC) t1[/code]

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-09 : 17:39:21

Thanks visakh16 , that worked like Charm !!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 23:23:18

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources