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

No comments:

Post a Comment