Cursor needed to relate different records?

Question

Cursor needed to relate different records?

Mark Finnie

SSC Eights!

Points: 988
More actions
January 31, 2007 at 1:48 pm

#122866

Hi, I need to be able to set the value of a field based on records other than the current record. This is for readings from a GPS system in machines. I am using a stored procedure to populate a temporary table of GPS events in order to join the record for each machine to the previous record. This is done by using a left join to join the events table to itself matching the machine number and eventID, e.g. table1.machineID = table2.machineID and table1.eventID = table2.eventID - 1. However I need to have a column to specify whether the machine is on or off, which is just another type of event.
For example, you could have the following transaction types:
1        monitoring
2        moving
3        lifting
4        ignition on
5        ignition off

So for a particular machine the transactions and the values I want may be like this:
1        off
4        on
1        on
2        on
1        on
5        off
1        off
I can easily calculate the time differences between one record and the next, but am not sure how to set the on/off status. Do I need to use a cursor to do this or is there a better way?
Thanks in advance,
Mark

Viewing 7 posts - 1 through 6 (of 6 total)

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

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Mark,

It would be much easier to assist if we can see the actual table you are working with. Maybe not the entire table, but at least the columns you are referencing. The result set is fine, but we really need to see the raw data to properly help you with the query.

So, table structure and sample data, and we will get back to you quickly.

Wayne

Russel Loski SSCertifiable Points: 7059 More actions · Answer 2

I'm not clear what the columns and rows represent. I think that a list of rows that exemplify what you are wanting (with columns labeled) with the expected output would be helpful.

Russel Loski, MCSE Business Intelligence, Data Platform

Mark Finnie SSC Eights! Points: 988 More actions · Answer 3

Sorry, I was trying to keep the problem brief. Here is some sample data:

CREATE TABLE #GPSEvents

(EventID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

EventDatetime DATETIME,

MachineID INT,

EventType SMALLINT,

)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:03.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:07.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:18.000',1,4)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:20.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:23.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:30.000',1,2)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:35.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:40.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:56.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:21:18.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:21:32.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:21:48.000',1,2)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:21:48.000',1,5)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:30:35.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:30:38.000',1,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:01.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:04.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:08.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:17.000',2,4)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:20.000',2,2)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:23.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:28.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:31.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:33.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:35.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:38.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:40.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:43.000',2,2)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:45.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:48.000',2,5)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:50.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:53.000',2,1)

INSERT INTO #GPSEvents VALUES('2007-01-31 07:20:55.000',2,1)

Select s.EventID, Datediff(s,s.EventDatetime,e.EventDatetime)as seconds, s.MachineID, s.EventType

From #GPSEvents s

LEFT JOIN #GPSEvents e ON s.MachineID = e.MachineID AND s.EventID = (e.EventID - 1)

Order by s.MachineID, s.EventDatetime

GO

The above query will give the following results, except I have manually added the on/off column as that is what I am asking how it can be done:

EventID seconds MachineID EventType On/Off

1 4 1 1 off

2 11 1 1 off

3 2 1 4 on

4 3 1 1 on

5 7 1 1 on

6 5 1 2 on

7 5 1 1 on

8 16 1 1 on

9 22 1 1 on

10 14 1 1 on

11 16 1 1 on

12 0 1 2 on

13 527 1 5 off

14 3 1 1 off

15 NULL 1 1 off

16 3 2 1 off

17 4 2 1 off

18 9 2 1 off

19 3 2 4 on

20 3 2 2 on

21 5 2 1 on

22 3 2 1 on

23 2 2 1 on

24 2 2 1 on

25 3 2 1 on

26 2 2 1 on

27 3 2 1 on

28 2 2 2 on

29 3 2 1 on

30 2 2 5 off

31 3 2 1 off

32 2 2 1 off

33 NULL 2 1 off

I am assuming the machine is off until a transaction (EventType) of 4 is reached. Then it will be on until there is an EventType of 5. I want to sum up the times for each machine for each event type. Lifting and moving are easy, but I need to be able to distinguish between idling (nothing happening but machine is on) and machine off.

Is that better?

Mark.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

This seems to work OK:

declare @t table 
(
 MachineID INT NOT NULL, 
 EventID INT NOT NULL,
 State varchar(3) NOT NULL,
 PRIMARY KEY CLUSTERED (MachineID ,EventID)
)

-- Load on/off state transition events
insert into @t
select
 x.MachineID,
 x.EventID, 
 State = case x.EventType when 4 then 'on' when 5 then 'off' end
from
 #GPSEvents x
where
 x.EventType in (4,5)
union
select
 y.MachineID,
 y.EventID, 
 State = case y.EventType when 4 then 'on' when 5 then 'off' else 'off' end
from
 #GPSEvents y
where
 y.EventID in 
 (select min(z.EventID) from #GPSEvents z where y.MachineID = z.MachineID )
order by
 1,2

Select
 s.EventID,
 Datediff(s,s.EventDatetime,e.EventDatetime)as seconds,
 s.MachineID,
 s.EventType,
 t.State
From
 #GPSEvents s
 LEFT JOIN
 #GPSEvents e 
 ON s.MachineID = e.MachineID AND s.EventID = (e.EventID - 1)
 join
 (
 -- Find last state change event before or at a machine event
 select
  x.MachineID,
  x.EventID,
  ChangeEventID = max(t.EventID)
 from
  #GPSEvents x
  join
  @t t
  on x.MachineID = t.MachineID and x.EventID >= t.EventID
 group by
  x.MachineID,
  x.EventID
 ) z
 on s.MachineID = z.MachineID AND s.EventID = z.EventID
 join
 @t t
 on z.MachineID = t.MachineID AND z.ChangeEventID = t.EventID 
Order by
 s.MachineID,
 s.EventDatetime

Russel Loski SSCertifiable Points: 7059 More actions · Answer 5

This will also work:

Select s.EventID, Datediff(s,s.EventDatetime,e.EventDatetime)as seconds, s.MachineID, s.EventType,

case when

(Select emt.EventType from

(select Max(em.EventID) MaxEventID from #GPSEvents em

where em.EventID <= s.EventID

and EventType in (4,5)

and em.MachineID = s.MachineID) et

left join

#GPSEvents emt

on MaxEventID = emt.EventID)

= 4 then 'on' else 'off' end as OnOff

From #GPSEvents s

LEFT JOIN #GPSEvents e ON s.MachineID = e.MachineID

AND s.EventID = (e.EventID - 1)

Order by s.MachineID, s.EventDatetime

Try both of the solutions. One might perform better than the other.

Russel Loski, MCSE Business Intelligence, Data Platform

Mark Finnie SSC Eights! Points: 988 More actions · Answer 6

Thank you Michael and Russel. They both work well.

Thanks for taking the time to help me out.

Mark.