CODEWITHSUNDEEP
sqljoinfirebirdself-join
zeno33
zeno33

Reputation: 23

firebird - self join on one table

I have a table STARTSTOP

ACTION  DATA                    ID_PPSTARTSTOPPOZ
0   2013-03-18 08:38:00 10451
1   2013-03-18 09:00:00 10453
0   2013-03-18 09:50:00 10466
1   2013-03-18 10:38:00 10467
0   2013-03-19 11:54:00 10499
1   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    DURATION
10451        10453       22
10466        10466       48
             ...

OR summary for all actions duration in one row.

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

Upvotes: 2

Views: 1369

Answers (3)

Rohan Nayak
Rohan Nayak

Reputation: 233

This can be done in much Simpler way

SELECT TAB1.ID AS ACTION_1,TAB2.ID AS ACTION_2,  
(TAB2.DATA_TS - TAB1.DATA_TS)   MINUTE (4) TO SECOND(6) AS DURATION
FROM 

 (SELECT  ID, DATA_TS , ROW_NUMBER () OVER ( ORDER BY ID )AS RNUM FROM   
 PROCESS WHERE ACTION=0
 )TAB1

 INNER JOIN 

 (SELECT  ID, DATA_TS , ROW_NUMBER () OVER ( ORDER BY ID ) AS RNUM  FROM    
  PROCESS WHERE ACTION=1 
 ) TAB2

 ON ( TAB1.RNUM=TAB2.RNUM)
 ORDER BY 1

  ACTION_1  ACTION_2   DURATION
  10,451    10,453     22:00.000000
  10,466    10,467     48:00.000000
  10,499    10,505     38:00.000000

Upvotes: 0

valex
valex

Reputation: 24144

select A1.ID_PPSTARTSTOPPOZ as Action_0,
       A2.Action_1,
       datediff (minute, A1.DATA ,A2.DATA)

from STARTSTOP A1
JOIN 
(
  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_ACTION

where ACTION = 0
order by A1.ID_PPSTARTSTOPPOZ

DATEDIFF function

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

Upvotes: 2

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5491

It could be done with a single select but algorithmic EXECUTE BLOCK would do much faster:

EXECUTE BLOCK
  RETURNS (ACTION_1 INTEGER, ACTION_2 INTEGER, DURATION INTEGER)
AS
  DECLARE VARIABLE act INTEGER;
  DECLARE VARIABLE act_id INTEGER;
  DECLARE VARIABLE d TIMESTAMP = NULL;
  DECLARE VARIABLE d1 TIMESTAMP = NULL;
BEGIN
  FOR
    SELECT action, data, id_ppstartstoppoz
    FROM startstop
    ORDER BY data ASC
    INTO :act, :d, :act_id
  DO BEGIN
    IF (:act = 0) THEN
    BEGIN
      d1 = :d;
      action_1 = :act_id;
    END ELSE
    BEGIN
      IF (NOT :d1 IS NULL) THEN
      BEGIN
        action_2 = :act_id;
        duration = DATEDIFF(SECOND, :d1, :d);
        SUSPEND;
        d1 = NULL; 
      END
    END
  END
END

Upvotes: 0

Related Questions