Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get previous value Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 4:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:21 AM
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')






Post #1501520
Posted Friday, October 4, 2013 4:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:29 AM
Points: 1,678, Visits: 19,554

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')
))



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1501527
Posted Friday, October 4, 2013 8:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:21 AM
Points: 18, Visits: 59
It worked fine !! thanks a lot Mark
Have a good weekend

Regards,

Claudio
Post #1501610
Posted Tuesday, October 8, 2013 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:21 AM
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
Post #1502653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse