SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get previous value


Get previous value

Author
Message
cphspain
cphspain
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 59
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

100035370 2013-10-03 16:44:35.640 273769808 7441 O E441 NULL ARM
100035370 2013-10-03 16:44:35.663 273769809 C O O/C NULL ARM
100035370 2013-10-03 16:44:53.840 273769868 7130 R 11 143069012 A
100035370 2013-10-03 16:44:58.193 273769880 7130 R 11 143069012 A
100035370 2013-10-03 16:44:58.570 273769883 C O O/C 143069012 ARM
100035370 2013-10-03 16:45:05.137 273769907 7441 O E441 143069012 ARM
100035370 2013-10-03 16:45:05.183 273769908 C O O/C 143069012 ARM
100035370 2013-10-03 16:45:10.880 273769931 C O O/C 143069012 ARM
100035370 2013-10-03 16:45:12.920 273769935 7130 R 11 143069012 A
100035370 2013-10-03 16:45:12.970 273769936 7130 R 11 143069012 A
100035370 2013-10-03 16:45:19.430 273769944 7130 R 11 143069012 A
100035370 2013-10-03 16:45:30.990 273769973 7130 R 11 143069012 A
100035370 2013-10-03 16:45:31.320 273769978 7130 R 11 143069012 A
100035370 2013-10-03 16:45:33.843 273769989 7130 R 11 143069012 A
100035370 2013-10-03 16:45:50.100 273770058 7130 R 11 143069012 A
100035370 2013-10-03 16:46:09.147 273770111 7130 R 11 143069012 A
100035370 2013-10-03 16:46:09.643 273770118 7130 R 11 143069012 A

300010596 2013-10-03 16:16:16.240 273763678 CLMAIL O O/C NULL ARM
300010596 2013-10-03 16:16:18.363 273763681 7131 R 11 143067032 A
300010596 2013-10-03 16:16:20.700 273763700 7131 R 11 143067032 A
300010596 2013-10-03 16:16:20.760 273763701 7131 R 11 143067032 A
300010596 2013-10-03 16:16:25.560 273763721 7131 R 11 143067032 A

So for the system_no 100035370 we should get
100035370 2013-10-03 16:44:35.663 273769809 C O O/C NULL ARM
100035370 2013-10-03 16:44:53.840 273769868 7130 R 11 143069012 A
100035370 2013-10-03 16:45:10.880 273769931 C O O/C 143069012 ARM
100035370 2013-10-03 16:45:12.920 273769935 7130 R 11 143069012 A

For system_no 300010596 we should get
300010596 2013-10-03 16:16:16.240 273763678 CLMAIL O O/C NULL ARM
300010596 2013-10-03 16:16:18.363 273763681 7131 R 11 143067032 A

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')
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3101 Visits: 24088


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
cphspain
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 59
It worked fine !! thanks a lot Mark :-)
Have a good weekend

Regards,

Claudio
cphspain
cphspain
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 59
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search