vendredi 31 juillet 2015

Loop and group by

I have a trip that has sequence of stops

Trip  Stop  Time 
1     A     1:10
1     B     1:15
1     B     1:20
1     B     1:25
1     C     1:30
2     A     2:10
2     B     2:15
2     C     2:20
2     B     2:25  

I want to transfer the table to:

Trip  Stop  Time  WaitTime
1     A     1:10   0
1     B     1:15   10min
1     C     1:30   0
2     A     2:10   0
2     B     2:15   0
2     C     2:20   0
2     B     2:25   0

I'm wondering if a oracle query can achieve it or cursor? pseudo code: SELECT case when previousstop = stop then time-lag(time) over (partition by trip order by trip,time) as waittime, but I don't know how to group by B.

Aucun commentaire:

Enregistrer un commentaire