Click here to monitor SSC
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 22778


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