Showing posts with label self join on one table. Show all posts
Showing posts with label self join on one table. Show all posts

Sunday, October 6, 2013

self join on one table

I have a table STARTSTOP

ACTION  DATA                    ID_PPSTARTSTOPPOZ0   2013-03-18 08:38:00 104511   2013-03-18 09:00:00 104530   2013-03-18 09:50:00 104661   2013-03-18 10:38:00 104670   2013-03-19 11:54:00 104991   2013-03-19 12:32:00 10505

Action 0 -> START ACTION
Action 1 -> STOP ACTION
DATA is a timestamp of action

I would like to run a select statement that would return records something like:

ACTION_1   ACTION_2    DURATION10451        10453       2210466        10466       48             ...

OR summary for all actions duration in one row.

Is it feasible with a single database query? (without creating additional tables)

select A1.ID_PPSTARTSTOPPOZ as Action_0,       A2.Action_1,       datediff (minute, A1.DATA ,A2.DATA)from STARTSTOP A1JOIN (  select ID_PPSTARTSTOPPOZ as Action_1,         DATA,         (select max(ID_PPSTARTSTOPPOZ)           FROM STARTSTOP           where ID_PPSTARTSTOPPOZ<T.ID_PPSTARTSTOPPOZ                AND                ACTION=0) AS PREV_ACTION  from STARTSTOP T  where ACTION=1 ) A2 on A1.ID_PPSTARTSTOPPOZ=A2.PREV_ACTIONwhere ACTION = 0order by A1.ID_PPSTARTSTOPPOZ 

DATEDIFF function

SQLFiddle Example for MSSQL yet it has to work under Firebird too