Identify Change Points in a Column

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)

Identify Change Points in a Column

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 00:49:28

DECLARE @EffLevels TABLE (ChangePoint int, Value Int)

INSERT @EffLevels
SELECT '1000', '767' UNION ALL --Changed
SELECT '1000', '675' UNION ALL
SELECT '1001', '600' UNION ALL --Changed
SELECT '1001', '545' UNION ALL
SELECT '1001', '765' UNION ALL
SELECT '1000', '673' UNION ALL --Changed
SELECT '1002', '343' UNION ALL --Changed
SELECT '1002', '413' UNION ALL
SELECT '1002', '334' UNION ALL
SELECT '1001', '823' --Changed

-- My Result should be
-- ChangePoint PrevChangePoint Value
-- 1000 Null 767
-- 1001 1000 675
-- 1000 1001 765
-- 1002 1000 343
-- 1001 1002 823

Any suggestion ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 01:03:52

You need some way to order this table. Do you have a column that does that?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 01:06:44

You sample output does not seem follow any pattern. For example, why you have
1002 1000 343 rather than
1002 1000 673? Can you explain?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 01:07:35

Yes,There's are primary keys Called RntID(int) and ProductionDay(varchar(10)),The RntID is and Identity on ProductionDay ie..
ProductionDay has Many RntIDs starting from 1 till end of Day

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 01:09:08

Please provide a complete example that illustrates your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 01:09:42

The Pattern needed is CurrentChangePoint , PrevPoint and CurrentValue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 01:19:52

Nope i was asking can you explain your requirement.If you take this
SELECT '1000', '675' UNION ALL
SELECT '1001', '600'

you are getting 1001 1000 675

and if you consider this

SELECT '1000', '673' UNION ALL --Changed
SELECT '1002', '343'

you're gettig 1002 1000 343 rather than 1002 1000 673. can you explain this difference?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 01:34:50

Oops..Sorry there was a typing error:
SELECT '1000', '675' UNION ALL
SELECT '1001', '600'

Should Get 1001 1000 600

and

SELECT '1000', '673' UNION ALL --Changed
SELECT '1002', '343'

Should Get 1002 1000 343

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 02:51:38

[code]DECLARE @EffLevels TABLE (ID int identity(1,1),ChangePoint int, Value Int)

INSERT @EffLevels (ChangePoint,Value)
SELECT '1000', '767' UNION ALL --Changed
SELECT '1000', '675' UNION ALL
SELECT '1001', '600' UNION ALL --Changed
SELECT '1001', '545' UNION ALL
SELECT '1001', '765' UNION ALL
SELECT '1000', '673' UNION ALL --Changed
SELECT '1002', '343' UNION ALL --Changed
SELECT '1002', '413' UNION ALL
SELECT '1002', '334' UNION ALL
SELECT '1001', '823' --Changed

SELECT e1.ChangePoint,e2.ChangePoint,e1.Value
FROM @EffLevels e1
LEFT JOIN @EffLevels e2
ON e2.ID=e1.ID-1
AND e1.ChangePoint <> e2.ChangePoint
WHERE e2.ID IS NOT NULL
OR e1.ID=1
[/code]

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 03:31:21

Thanks visak,
you made it look so simple..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources