Karolis Ramanauskas Karolis Ramanauskas - 1 month ago 10
MySQL Question

Querying related rows based on order and column value

The table below consists of logs of when an operation with a given ID started and finished, as indicated by

operation
column.

Is there a way to produce a SAS view that will have the following columns -
runID
,
type
,
sessionName
,
startTime
,
endtime
and
durationInMinutes
? It should consist of related rows that have the same ID and the
startTime
and
endTime
are the two closest rows by time from each other.

id operation time
1 starts 13:45
2 starts 13:46
3 starts 13:50
4 starts 13:55
2 ends 14:02
1 ends 14:50
3 ends 14:51
1 starts 15:25
3 starts 15:42
2 starts 15:51
4 ends 18:02
1 ends 18:09
3 ends 18:25
2 ends 18:30

Answer

I think that you are efter somethink like this. I do a self join and start off from there. Let me know if any clarifications needed.

data inData;
input id  operation $10.  time time. ;
format time hhmm. ;
datalines; 
1   starts  13:45
2   starts  13:46
3   starts  13:50
4   starts  13:55
2   ends    14:02
1   ends    14:50
3   ends    14:51
1   starts  15:25
3   starts  15:42
2   starts  15:51
4   ends    18:02
1   ends    18:09
3   ends    18:25
2   ends    18:30

;

proc sql;
create view tempView as
select a.id, a.operation as Type , a.Time as StartTime,
    b.operation as Type2,  b.time as EndTime,  
    INTCK('minute',a.Time,b.Time) as durationInMinutes
from indata a inner join indata b
    on a.id=b.id
group by a.id, b.Time
having durationInMinutes > 0 and a.operation='starts' and
    b.operation='ends'
order by a.id , StartTime, durationInMinutes
;
quit;

proc sql;
create view YourView as
select *
from TempView
group by ID , StartTime 
having min(durationInMinutes)=durationInMinutes
;
quit;