Home » SQL & PL/SQL » SQL & PL/SQL » Need help in this SQL logic (Oracle 11g)
Need help in this SQL logic [message #614960] Thu, 29 May 2014 02:40 Go to next message
vishalshinde.2004
Messages: 2
Registered: May 2014
Location: India
Junior Member
Hi all,

I need to create a report which shows the batch duration grouped by batch id.
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 as shown below.
below is the expected output.

Group 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

Here is the create table script

Create Table Script:

CREATE TABLE BATCH_T
(
BATCHID NUMBER(38,0),
BATCHSTARTTIME TIMESTAMP(6),
BATCHENDTIME TIMESTAMP(6)
);


Insert Script:

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'));
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'));
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'));
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'));
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'));
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'));
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'));

Report message to a moderator

Re: Need help in this SQL logic [message #614961 is a reply to message #614960] Thu, 29 May 2014 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
below is the expected output.


There is no batch duration in your expected output.
Please post the result you want for the data you gave.
Where does "group" come from?

Report message to a moderator

Re: Need help in this SQL logic [message #614962 is a reply to message #614960] Thu, 29 May 2014 02:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
 
SQL> SELECT Dense_rank()
  2           over(
  3             ORDER BY To_char(batchstarttime, 'DD-MON-RR HH24')) grp,
  4         batchid,
  5         batchstarttime,
  6         batchendtime
  7  FROM   batch_t
  8  /
       GRP                                 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
         3                                      17 28-MAR-13 03.55.23.000000 PM                      28-MAR-13 03.57.46.000000 PM
         4                                      19 28-MAR-13 04.02.54.000000 PM                      28-MAR-13 04.17.00.000000 PM
         4                                      20 28-MAR-13 04.10.53.000000 PM                      28-MAR-13 04.11.30.000000 PM
7 rows selected

Report message to a moderator

Re: Need help in this SQL logic [message #614964 is a reply to message #614962] Thu, 29 May 2014 03:40 Go to previous messageGo to next message
vishalshinde.2004
Messages: 2
Registered: May 2014
Location: India
Junior Member
Thank you Lalit. This is what i was expecting.

Report message to a moderator

Re: Need help in this SQL logic [message #614966 is a reply to message #614964] Thu, 29 May 2014 03:58 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
vishalshinde.2004 wrote on Thu, 29 May 2014 09:40
Thank you Lalit. This is what i was expecting.

Are you sure? Combining batch_ids 16 and 17?

Report message to a moderator

Re: Need help in this SQL logic [message #615001 is a reply to message #614966] Thu, 29 May 2014 05:54 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
John Watson wrote on Thu, 29 May 2014 14:28
vishalshinde.2004 wrote on Thu, 29 May 2014 09:40
Thank you Lalit. This is what i was expecting.

Are you sure? Combining batch_ids 16 and 17?



I misunderstood the requirement initially. The records needs to be grouped if they overlap between the start and end time interval.

SQL> WITH data
  2       AS (SELECT t_1.batchid,
  3                  t_1.batchstarttime,
  4                  t_1.batchendtime,
  5                  CASE
  6                    WHEN t_1.batchstarttime BETWEEN
  7                         t_2.batchstarttime AND t_2.batchendtime
  8                     THEN
  9                    t_2.batchstarttime
 10                    ELSE t_1.batchstarttime
 11                  END val_time
 12           FROM   batch_t t_1,
 13                  batch_t t_2
 14           WHERE  t_1.batchstarttime BETWEEN
 15                  t_2.batchstarttime(+) AND t_2.batchendtime(+)
 16                  AND t_1.batchid != t_2.batchid(+))
 17  SELECT Dense_rank()
 18           over(
 19             ORDER BY val_time) group_id,
 20         batchid,
 21         batchstarttime,
 22         batchendtime
 23  FROM   DATA
 24  /
  GROUP_ID                                 BATCHID BATCHSTARTTIME                                    BATCHENDTIME
---------- --------------------------------------- ------------------------------------------------- -------------------------------------------------
         1                                      13 28-MAR-13 10.14.38.000000 AM                      28-MAR-13 11.11.03.000000 AM
         1                                      12 28-MAR-13 10.03.24.000000 AM                      28-MAR-13 12.22.43.000000 PM
         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

Report message to a moderator

Re: Need help in this SQL logic [message #615008 is a reply to message #615001] Thu, 29 May 2014 06:51 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or much simpler using start of group method:

with t as (
           select  b.*,
                   case
                     when lag(batchendtime) over(order by batchstarttime,batchendtime) >= batchstarttime then 0
                     else 1
                   end start_of_group
             from  batch_t b
          )
select  sum(start_of_group) over(order by batchstarttime,batchendtime) group_id,
        batchid,
        batchstarttime,
        batchendtime
  from  t
  order by batchstarttime,
           batchendtime
/

  GROUP_ID    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>


SY.

Report message to a moderator

Previous Topic: Adding column to existing index
Next Topic: Delete getting stuck
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 08 12:25:12 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.