Get previous value

Question

Post reply

Get previous value

cphspain

Old Hand

Points: 342
More actions
October 4, 2013 at 4:43 am

#280582

Hi,
I need get from a history table an specific previous value, any help will be welcome and it's appreciate beforehand, thanks
It treats about get those records with zonestate_id 'A' and his previous zonestate_id 'ARM'
Sample
system_no /event_date/ seqno/ event_id/ eventrpt_id/ zone_id/alarminc_no/ zonestate_id
1000353702013-10-03 16:44:35.640 2737698087441O E441 NULL ARM
1000353702013-10-03 16:44:35.663 273769809C O O/C NULL ARM
1000353702013-10-03 16:44:53.840 2737698687130R 11143069012A
1000353702013-10-03 16:44:58.1932737698807130R 11143069012A
1000353702013-10-03 16:44:58.570273769883C O O/C 143069012ARM
1000353702013-10-03 16:45:05.1372737699077441O E441 143069012ARM
1000353702013-10-03 16:45:05.183 273769908C O O/C 143069012ARM
1000353702013-10-03 16:45:10.880273769931C O O/C 143069012ARM
1000353702013-10-03 16:45:12.9202737699357130R 11143069012A
1000353702013-10-03 16:45:12.9702737699367130R 11143069012A
1000353702013-10-03 16:45:19.4302737699447130R 11143069012A
1000353702013-10-03 16:45:30.9902737699737130R 11143069012A
1000353702013-10-03 16:45:31.3202737699787130R 11143069012A
1000353702013-10-03 16:45:33.843 2737699897130R 11143069012A
1000353702013-10-03 16:45:50.100 2737700587130R 11143069012A
1000353702013-10-03 16:46:09.147 2737701117130R 11143069012A
1000353702013-10-03 16:46:09.643 2737701187130R 11143069012A
3000105962013-10-03 16:16:16.240273763678CLMAILO O/C NULL ARM
3000105962013-10-03 16:16:18.3632737636817131 R 11 143067032A
300010596 2013-10-03 16:16:20.7002737637007131 R 11 143067032A
3000105962013-10-03 16:16:20.760 2737637017131 R 11 143067032A
3000105962013-10-03 16:16:25.5602737637217131 R 11 143067032A
So for the system_no 100035370 we should get
1000353702013-10-03 16:44:35.663 273769809C O O/C NULL ARM
1000353702013-10-03 16:44:53.8402737698687130R 11143069012A
1000353702013-10-03 16:45:10.880273769931C O O/C 143069012ARM
1000353702013-10-03 16:45:12.9202737699357130R 11143069012A
For system_no 300010596 we should get
3000105962013-10-03 16:16:16.240273763678CLMAILO O/C NULL ARM
3000105962013-10-03 16:16:18.3632737636817131 R 11 143067032A
Sample table and values
CREATE TABLE [event_history](
[system_no] [int] NOT NULL,
[event_date] [datetime] NOT NULL,
[seqno] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[event_id] [char](6) NULL,
[eventrpt_id] [char](2) NULL,
[zone_id] [char](6) NULL,
[alarminc_no] [decimal](18, 0) NULL,
[zonestate_id] [char](4) NULL,)
INSERT INTO [event_history]([system_no], [event_date], [seqno], [event_id], [eventrpt_id], [zone_id], [alarminc_no], [zonestate_id])
Values('100035370', '2013-10-03 16:44:35.640', '273769808', '7441', 'O', 'E441',NULL, 'ARM')
Values('100035370', '2013-10-03 16:44:35.663', '273769809', 'C', 'O', 'O/C',NULL, 'ARM')
Values('100035370',’2013-10-03 16:44:53.840', '273769868', '7130', 'R', '11','143069012', 'A')
Values('100035370','2013-10-03 16:44:58.193', '273769880','7130','R', '11', '143069012', 'A')
Values('100035370','2013-10-03 16:44:58.570', '273769883', 'C', 'O','O/C', '143069012', 'ARM')
Values ('100035370', '2013-10-03 16:45:05.137', '273769907', '7441', 'O', 'E441', '143069012', 'ARM')
Values('100035370','2013-10-03 16:45:05.183', '273769908','C', 'O', 'O/C', '143069012', 'ARM')
Values('100035370','2013-10-03 16:45:10.880', '273769931','C', 'O', 'O/C', '143069012', 'ARM')
Values('100035370','2013-10-03 16:45:12.920', '273769935','7130', 'R', '11', '143069012', 'A')
Values('100035370','2013-10-03 16:45:12.970', '273769936','7130','R', '11', '143069012', 'A')
Values('100035370','2013-10-03 16:45:19.430', '273769944','7130', 'R', '11', '143069012', 'A')
Values('100035370','2013-10-03 16:45:30.990', '273769973', '7130', 'R', '11', '143069012', 'A')
Values('100035370', '2013-10-03 16:45:31.320', '273769978', '7130', 'R', '11', '143069012', 'A')
Values('100035370', '2013-10-03 16:45:33.843', '273769989', '7130', 'R', '11', '143069012', 'A')
Values('100035370', '2013-10-03 16:45:50.100', '273770058', '7130', 'R', '11', '143069012', 'A')
Values('100035370', '2013-10-03 16:46:09.147', '273770111', '7130', 'R', '11', '143069012', 'A')
Values('100035370', '2013-10-03 16:46:09.643', '273770118', '7130', 'R', '11', '143069012', 'A')
Values('300010596', '2013-10-03 16:16:16.240', '273763678', 'CLMAIL', 'O', 'O/C', NULL, 'ARM')
Values('300010596', '2013-10-03 16:16:18.363', '273763681', '7131', 'R', '11', '143067032', 'A')
Values('300010596', '2013-10-03 16:16:20.700', '273763700', '7131', 'R', '11', '143067032', 'A')
Values('300010596', '2013-10-03 16:16:20.760', '273763701', '7131', 'R', '11', '143067032', 'A')
Values('300010596',2013-10-03 16:16:25.560', '273763721', '7131', 'R', '11', '143067032', 'A')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY [system_no] ORDER BY [event_date]) AS rn

FROM [event_history])

SELECT a.*

FROM CTE a

WHERE EXISTS(SELECT * FROM CTE b WHERE b.[system_no] = a.[system_no]

AND ((b.rn = a.rn + 1 AND b.[zonestate_id] = 'A' AND a.[zonestate_id] = 'ARM')

OR (b.rn = a.rn - 1 AND b.[zonestate_id] = 'ARM' AND a.[zonestate_id] = 'A')

))

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

cphspain Old Hand Points: 342 More actions · Answer 2

It worked fine !! thanks a lot Mark 🙂

Have a good weekend

Regards,

Claudio

cphspain Old Hand Points: 342 More actions · Answer 3

Mark,

Having XPK index cluster at event_history table, how could we enhance the CTE?

Index key columns

System_no

Even_date

Seqno

Thanks

Claudio