1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help in this SQL logic

Discussion in 'SQL PL/SQL' started by vishalshinde.2004, May 29, 2014.

  1. Hi all,

    I need to create a report which shows the batch duration grouped by batch id.
    The logic is It should show. Currently we have BatchId, BatchStartTime and BatchEndTime stored in a batch_t table.

    I need to group these BatchId's if their start times and end times are overlapping each other and want output as shown below.

    GroupID BatchId BatchStartTime BatchEndTime
    1 12 28-MAR-13 10.03.24.000000000 AM 28-MAR-13 12.22.43.000000000 PM
    1 13 28-MAR-13 10.14.38.000000000 AM 28-MAR-13 11.11.03.000000000 AM
    2 15 28-MAR-13 02.19.32.000000000 PM 28-MAR-13 02.27.24.000000000 PM
    3 16 28-MAR-13 03.46.23.000000000 PM 28-MAR-13 03.55.07.000000000 PM
    4 17 28-MAR-13 03.55.23.000000000 PM 28-MAR-13 03.57.46.000000000 PM
    5 20 28-MAR-13 04.10.53.000000000 PM 28-MAR-13 04.11.30.000000000 PM
    5 19 28-MAR-13 04.02.54.000000000 PM 28-MAR-13 04.17.00.000000000 PM

    I am able to find out the overlapping batches using the OVERLAPS operator but I am not able to group them as shown above (see the first column).

    Heres the create table and insert scripts':
    CREATE TABLE BATCH_T
    (
    BATCHID NUMBER(38,0),
    BATCHSTARTTIME TIMESTAMP(6),
    BATCHENDTIME TIMESTAMP(6)
    );

    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (12,to_timestamp('28-MAR-13 10.03.24.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 12.22.43.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (13,to_timestamp('28-MAR-13 10.14.38.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 11.11.03.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (15,to_timestamp('28-MAR-13 02.19.32.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 02.27.24.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (16,to_timestamp('28-MAR-13 03.46.23.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 03.55.07.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (19,to_timestamp('28-MAR-13 04.02.54.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 04.17.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (17,to_timestamp('28-MAR-13 03.55.23.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 03.57.46.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
    Insert into EXPORT_TABLE (BATCHID,BATCHSTARTTIME,BATCHENDTIME) values (20,to_timestamp('28-MAR-13 04.10.53.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 04.11.30.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vishal,

    Try this:

    Code (SQL):
    SELECT
      DENSE_RANK() OVER ( ORDER BY bid) ID,
      batchid,
      batchstarttime,
      batchendtime
    FROM
      (SELECT batchid,
        batchstarttime,
        batchendtime,
        (
        CASE
          WHEN lead(batchstarttime,1) OVER (ORDER BY batchstarttime) < batchendtime
          THEN lead(batchid,1) OVER (ORDER BY batchstarttime)
          ELSE batchid
        END) bid ,
        first_value(batchid) OVER (ORDER BY batchid) first_val
      FROM batch_t
      );
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is how I did it -- possibly there's another way:


    Code (SQL):

    SQL> CREATE TABLE BATCH_T
      2   (
      3   BATCHID NUMBER(38,0),
      4   BATCHSTARTTIME TIMESTAMP(6),
      5   BATCHENDTIME TIMESTAMP(6)
      6   );


    TABLE created.


    SQL>
    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (12,to_timestamp('28-MAR-13 10.03.24.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 12.22.43.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (13,to_timestamp('28-MAR-13 10.14.38.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 11.11.03.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (15,to_timestamp('28-MAR-13 02.19.32.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 02.27.24.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (16,to_timestamp('28-MAR-13 03.46.23.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 03.55.07.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (19,to_timestamp('28-MAR-13 04.02.54.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 04.17.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (17,to_timestamp('28-MAR-13 03.55.23.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 03.57.46.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>  INSERT INTO BATCH_T (BATCHID,BATCHSTARTTIME,BATCHENDTIME) VALUES (20,to_timestamp('28-MAR-13 04.10.53.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('28-MAR-13 04.11.30.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


    1 ROW created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT batchid, batchstarttime, batchendtime
      2  FROM batch_t;


       BATCHID BATCHSTARTTIME                   BATCHENDTIME
    ---------- -------------------------------- --------------------------------
            12 28-MAR-13 10.03.24.000000 AM     28-MAR-13 12.22.43.000000 PM
            13 28-MAR-13 10.14.38.000000 AM     28-MAR-13 11.11.03.000000 AM
            15 28-MAR-13 02.19.32.000000 PM     28-MAR-13 02.27.24.000000 PM
            16 28-MAR-13 03.46.23.000000 PM     28-MAR-13 03.55.07.000000 PM
            19 28-MAR-13 04.02.54.000000 PM     28-MAR-13 04.17.00.000000 PM
            17 28-MAR-13 03.55.23.000000 PM     28-MAR-13 03.57.46.000000 PM
            20 28-MAR-13 04.10.53.000000 PM     28-MAR-13 04.11.30.000000 PM


    7 ROWS selected.


    SQL>
    SQL> SELECT bt1.batchid, bt1.batchstarttime, bt1.batchendtime
      2  FROM batch_t bt1, batch_t bt2
      3  WHERE bt2.batchid > bt1.batchid
      4  AND wm_overlaps(wm_period(bt1.batchstarttime, bt1.batchendtime),wm_period(bt2.batchstarttime, bt2.batchendtime)) = 1
      5  UNION
      6  SELECT bt2.batchid, bt2.batchstarttime, bt2.batchendtime
      7  FROM batch_t bt1, batch_t bt2
      8  WHERE bt2.batchid > bt1.batchid
      9  AND wm_overlaps(wm_period(bt1.batchstarttime, bt1.batchendtime),wm_period(bt2.batchstarttime, bt2.batchendtime)) = 1
     10  UNION
     11  SELECT bt3.batchid, bt3.batchstarttime, bt3.batchendtime
     12  FROM batch_t bt3, batch_t bt4
     13  WHERE bt4.batchid > bt3.batchid
     14  AND wm_overlaps(wm_period(bt3.batchstarttime, bt3.batchendtime),wm_period(bt4.batchstarttime, bt4.batchendtime)) = 0;


       BATCHID BATCHSTARTTIME                   BATCHENDTIME
    ---------- -------------------------------- --------------------------------
            12 28-MAR-13 10.03.24.000000 AM     28-MAR-13 12.22.43.000000 PM
            13 28-MAR-13 10.14.38.000000 AM     28-MAR-13 11.11.03.000000 AM
            15 28-MAR-13 02.19.32.000000 PM     28-MAR-13 02.27.24.000000 PM
            16 28-MAR-13 03.46.23.000000 PM     28-MAR-13 03.55.07.000000 PM
            17 28-MAR-13 03.55.23.000000 PM     28-MAR-13 03.57.46.000000 PM
            19 28-MAR-13 04.02.54.000000 PM     28-MAR-13 04.17.00.000000 PM
            20 28-MAR-13 04.10.53.000000 PM     28-MAR-13 04.11.30.000000 PM


    7 ROWS selected.


    SQL>
     
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    SELECT Dense_rank()
    over(ORDER BY To_char(batchstarttime, 'DD-MON-RR HH24')) group
    ,batchid
    ,batchstarttime
    ,batchendtime
    FROM batch_t
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    I had started with something similar to your query:

    but it gives me the following result (which is not exactly what is needed by Vishal):

    Code (SQL):
    GROUP_ID  BATCHID BATCHSTARTTIME                      BATCHENDTIME
    --------  ------- ----------------------------------- ---------------------------------
          1       12    28-MAR-2013 10.03.24.000000000 AM   28-MAR-2013 12.22.43.000000000 PM
          1       13    28-MAR-2013 10.14.38.000000000 AM   28-MAR-2013 11.11.03.000000000 AM
          2       15    28-MAR-2013 02.19.32.000000000 PM   28-MAR-2013 02.27.24.000000000 PM
          3       16    28-MAR-2013 03.46.23.000000000 PM   28-MAR-2013 03.55.07.000000000 PM
          3       17    28-MAR-2013 03.55.23.000000000 PM   28-MAR-2013 03.57.46.000000000 PM
          4       19    28-MAR-2013 04.02.54.000000000 PM   28-MAR-2013 04.17.00.000000000 PM
          4       20    28-MAR-2013 04.10.53.000000000 PM   28-MAR-2013 04.11.30.000000000 PM
    Line 5 of the output does not overlap and it has same id as line 4. It should be:

    Code (SQL):
    GROUP_ID  BATCHID BATCHSTARTTIME                      BATCHENDTIME
    --------  ------- ----------------------------------- ---------------------------------
          1       12    28-MAR-2013 10.03.24.000000000 AM   28-MAR-2013 12.22.43.000000000 PM
          1       13    28-MAR-2013 10.14.38.000000000 AM   28-MAR-2013 11.11.03.000000000 AM
          2       15    28-MAR-2013 02.19.32.000000000 PM   28-MAR-2013 02.27.24.000000000 PM
          3       16    28-MAR-2013 03.46.23.000000000 PM   28-MAR-2013 03.55.07.000000000 PM
          4       17    28-MAR-2013 03.55.23.000000000 PM   28-MAR-2013 03.57.46.000000000 PM
          5       19    28-MAR-2013 04.02.54.000000000 PM   28-MAR-2013 04.17.00.000000000 PM
          5       20    28-MAR-2013 04.10.53.000000000 PM   28-MAR-2013 04.11.30.000000000 PM
     
    jagadekara likes this.
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This probably will not be a 'simpls' SQL solution; it may require PL/SQL to generate the numbering scheme you want.
     
  7. neha13infy

    neha13infy Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Please take a look on the attached code.
     

    Attached Files:

    • SQL.txt
      File size:
      1.7 KB
      Views:
      5
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are far more than 7 records in this table so your 'solution' has to take that into account. You need to use a more advanced loop to process all of the available data:


    Code (SQL):

    SQL> TRUNCATE TABLE batch_t_temp;


    TABLE truncated.


    SQL>
    SQL> DECLARE CURSOR C1 IS
      2  SELECT ROWNUM groupid, T.BATCHID, T.BATCHSTARTTIME, T.BATCHENDTIME, T.CNT
      3  FROM
      4  (SELECT T1.BATCHID, T1.BATCHSTARTTIME, T1.BATCHENDTIME, COUNT(T1.BATCHID) CNT
      5  FROM BATCH_T T1, BATCH_T T2
      6  WHERE (T1.BATCHSTARTTIME, T1.BATCHENDTIME) OVERLAPS (T2.BATCHSTARTTIME, T2.BATCHENDTIME)
      7  GROUP BY T1.BATCHID, T1.BATCHSTARTTIME, T1.BATCHENDTIME
      8  ORDER BY T1.BATCHSTARTTIME, T1.BATCHENDTIME) T;
      9
     10  RECORD INT :=0;
     11  OVERLAPREC INT :=0;
     12
     13  BEGIN
     14  FOR c1rec IN C1 loop
     15  IF c1rec.CNT = 2 AND RECORD = 0 THEN
     16    RECORD := 1;
     17    INSERT INTO BATCH_T_TEMP VALUES (c1rec.GROUPID-OVERLAPREC, c1rec.BATCHID, c1rec.BATCHSTARTTIME, c1rec.BATCHENDTIME);
     18  ELSIF c1rec.CNT = 2 AND RECORD = 1 AND OVERLAPREC = 0 THEN
     19    RECORD := 0;
     20    INSERT INTO BATCH_T_TEMP VALUES (c1rec.GROUPID-1, c1rec.BATCHID, c1rec.BATCHSTARTTIME, c1rec.BATCHENDTIME);
     21    OVERLAPREC := OVERLAPREC + 1;
     22  ELSIF c1rec.CNT = 2 AND RECORD = 1 AND OVERLAPREC > 0 THEN
     23    RECORD := 0;
     24    INSERT INTO BATCH_T_TEMP VALUES ((c1rec.GROUPID-OVERLAPREC)-1, c1rec.BATCHID, c1rec.BATCHSTARTTIME, c1rec.BATCHENDTIME);
     25    OVERLAPREC := OVERLAPREC + 1;
     26  END IF;
     27  IF c1rec.CNT = 1 THEN
     28    INSERT INTO BATCH_T_TEMP VALUES (c1rec.GROUPID-OVERLAPREC, c1rec.BATCHID, c1rec.BATCHSTARTTIME, c1rec.BATCHENDTIME);
     29  END IF;
     30  COMMIT;
     31  END LOOP;
     32  END;
     33  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM BATCH_T_TEMP
      2  ORDER BY GROUPID;


       GROUPID    BATCHID BATCHSTARTTIME                   BATCHENDTIME
    ---------- ---------- -------------------------------- --------------------------------
             1         12 28-MAR-13 10.03.24.000000 AM     28-MAR-13 12.22.43.000000 PM
             1         13 28-MAR-13 10.14.38.000000 AM     28-MAR-13 11.11.03.000000 AM
             2         15 28-MAR-13 02.19.32.000000 PM     28-MAR-13 02.27.24.000000 PM
             3         16 28-MAR-13 03.46.23.000000 PM     28-MAR-13 03.55.07.000000 PM
             4         17 28-MAR-13 03.55.23.000000 PM     28-MAR-13 03.57.46.000000 PM
             5         19 28-MAR-13 04.02.54.000000 PM     28-MAR-13 04.17.00.000000 PM
             5         20 28-MAR-13 04.10.53.000000 PM     28-MAR-13 04.11.30.000000 PM


    7 ROWS selected.


    SQL>
     

    It would be even better if there did not need to be a separate select after the PL/SQL ran:


    Code (SQL):

    SQL> SET serveroutput ON SIZE 1000000
    SQL> DECLARE CURSOR C1 IS
      2  SELECT ROWNUM groupid, T.BATCHID, T.BATCHSTARTTIME, T.BATCHENDTIME, T.CNT
      3  FROM
      4  (SELECT T1.BATCHID, T1.BATCHSTARTTIME, T1.BATCHENDTIME, COUNT(T1.BATCHID) CNT
      5  FROM BATCH_T T1, BATCH_T T2
      6  WHERE (T1.BATCHSTARTTIME, T1.BATCHENDTIME) OVERLAPS (T2.BATCHSTARTTIME, T2.BATCHENDTIME)
      7  GROUP BY T1.BATCHID, T1.BATCHSTARTTIME, T1.BATCHENDTIME
      8  ORDER BY T1.BATCHSTARTTIME, T1.BATCHENDTIME) T;
      9
     10  RECORD INT :=0;
     11  OVERLAPREC INT :=0;
     12
     13  BEGIN
     14  FOR c1rec IN C1 loop
     15  IF c1rec.CNT = 2 AND RECORD = 0 THEN
     16    RECORD := 1;
     17    dbms_output.put_line(c1rec.GROUPID-OVERLAPREC||' '||c1rec.BATCHID||'     '||c1rec.BATCHSTARTTIME||'      '||c1rec.BATCHENDTIME);
     18  ELSIF c1rec.CNT = 2 AND RECORD = 1 AND OVERLAPREC = 0 THEN
     19    RECORD := 0;
     20    dbms_output.put_line(c1rec.GROUPID-1||'       '||c1rec.BATCHID||'     '||c1rec.BATCHSTARTTIME||'      '||c1rec.BATCHENDTIME);
     21    OVERLAPREC := OVERLAPREC + 1;
     22  ELSIF c1rec.CNT = 2 AND RECORD = 1 AND OVERLAPREC > 0 THEN
     23    RECORD := 0;
     24    dbms_output.put_line((c1rec.GROUPID-OVERLAPREC)-1||'     '||c1rec.BATCHID||'     '||c1rec.BATCHSTARTTIME||'      '||c1rec.BATCHENDTIME);
     25    OVERLAPREC := OVERLAPREC + 1;
     26  END IF;
     27  IF c1rec.CNT = 1 THEN
     28    dbms_output.put_line(c1rec.GROUPID-OVERLAPREC||' '||c1rec.BATCHID||'     '||c1rec.BATCHSTARTTIME||'      '||c1rec.BATCHENDTIME);
     29  END IF;
     30  END LOOP;
     31  END;
     32  /
    1       12      28-MAR-13 10.03.24.000000 AM    28-MAR-13 12.22.43.000000 PM
    1       13      28-MAR-13 10.14.38.000000 AM    28-MAR-13 11.11.03.000000 AM
    2       15      28-MAR-13 02.19.32.000000 PM    28-MAR-13 02.27.24.000000 PM
    3       16      28-MAR-13 03.46.23.000000 PM    28-MAR-13 03.55.07.000000 PM
    4       17      28-MAR-13 03.55.23.000000 PM    28-MAR-13 03.57.46.000000 PM
    5       19      28-MAR-13 04.02.54.000000 PM    28-MAR-13 04.17.00.000000 PM
    5       20      28-MAR-13 04.10.53.000000 PM    28-MAR-13 04.11.30.000000 PM


    PL/SQL PROCEDURE successfully completed.


    SQL>