October 4, 2013 at 4:43 am
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')
October 4, 2013 at 4:58 am
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/61537October 4, 2013 at 8:02 am
It worked fine !! thanks a lot Mark
Have a good weekend
Regards,
Claudio
October 8, 2013 at 8:39 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy