SQL & PL/SQL

Hi All,

We are using Oracle DB 11g version 11.2.0.1 and have a requirement to fetch particular data on such condition. As per below script, we need to check if there won't be any job before given p_start_date then, the query(i.e. it will be dynamic query originally) should pick up creation date instead of actual end date and for remaining records, it should pick up actual end date but when we use LAG function, it's not giving proper output,

Create table and Insert Scripts:

create table table_a(asset_number varchar2(30),asset_code varchar2(20),job_number varchar2(50),actual_start_Date date,actual_end_date date)

/

create table table_b(asset_number varchar2(30),asset_code varchar2(20),job_number varchar2(50),creation_date date)

/

insert into table_a values('A1021','M08','A-7988428',to_date('5/8/2020 8:53:52','mm/dd/yyyy hh24:mi:ss'),to_date('5/9/2020 12:38:00','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_a values('A1021','M08','A-8004429',to_date('5/26/2020 10:30:00','mm/dd/yyyy hh24:mi:ss'),to_date('5/27/2020 3:30:00','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_a values('A1021','M08','A-8004430',to_date('5/27/2020 10:30:00','mm/dd/yyyy hh24:mi:ss'),to_date('5/27/2020 3:30:00','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_a values('A1021','M08','A-8004431',to_date('5/28/2020 7:47:00','mm/dd/yyyy hh24:mi:ss'),to_date('5/28/2020 3:30:00','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_b values('A1021','M08','A-7988428',to_date('4/6/2020 9:51:49','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_b values('A1021','M08','A-8004429',to_date('4/6/2020 9:51:49','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_b values('A1021','M08','A-8004430',to_date('4/6/2020 9:51:49','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_b values('A1021','M08','A-8004431',to_date('4/6/2020 9:51:49','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_b values('A1022','M09','AB-8004432',to_date('4/26/2020 10:03:47','mm/dd/yyyy hh24:mi:ss'))

/

insert into table_b values('A1022','M08','AB-8004434',to_date('4/26/2020 10:03:47','mm/dd/yyyy hh24:mi:ss'))

/

commit

/

Query:

SELECT a.asset_number,

     a.asset\_code,

     a.job\_number,

     (MAX (a.actual\_start\_date) - MIN (a.actual\_end\_date))

         diff,

     a.actual\_start\_date,

     --a.actual\_end\_date,

     NVL (LAG (a.actual\_end\_date, 1) OVER (ORDER BY a.actual\_end\_date),

          b.CREATION\_DATE)

         actual\_end\_date1

FROM table\_a a, table\_b b

WHERE a.asset_code = b.asset_code AND a.job_number = b.job_number

and a.actual\_start\_date >=  : p\_start\_date (i.e. pass the value as '08-May-2020')

GROUP BY a.asset_number,

     a.asset\_code,

     a.job\_number,

     a.actual\_start\_date,

     a.actual\_end\_date,

     b.CREATION\_DATE

ORDER BY a.asset_number, a.actual_start_date;

Actual Output:

Expected Output:

Need to calculate diff as below from bottom to top,

1. Diff1 = (actual start date - Previous record actual end date) = (05/28/2020 07:47:00 - 05/28/2020 03:30:00)

2. Diff2 = (actual start date - Previous record actual end date) = (05/27/2020 10:30:00 - 05/27/2020 03:30:00)

3. Diff3 = (actual start date - Previous record actual end date) = (05/26/2020 10:30:00 - 05/09/2020 12:38:00)

4. Diff4 = (actual start date - Previous record actual end date) = (05/08/2020 10:30:00 - 04/06/2020 9:51:49) -- if actual end date won't find then consider creation date.

Is there any other option other than LAG to achieve expected output?

Please suggest.

Thanks