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
SQLFiddle Example for MSSQL yet it has to work under Firebird too
No comments:
Post a Comment