Query is not giving proper output using LAG function

Hi,

AB115 wrote:

 

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

Thanks for posting the sample data and desired results; that really helps.

I don't see how you get the desired results.  For example, you want diff=-.2083 on the last row, to reflect the difference between 7:47 AM and 3:30 AM on the same day (that is, 247 minutes).  But .2083 days is 300 minutes; 247 minutes is about .1785 days, exactly as it is the current output.  If you really want -.2083, explain how you get it.

What is the algorithm using the data existing in the database? Can you explain that? Is that a formula column in that excel? If it is a formula column, then that formula should indicate how to compute that in SQL.

If that which you have in excel is not a formula column, then I think you must have an additional column in some table in the database. Where does that info come from?

If all you need is just to have in the database all that you receive in some excel files, then export from excel to csv and import that to the Oracle database using sqlldr and that's the end of the story.

Hi,

AB115 wrote:

 

Expected output is not generated from the query.

I understand.

we have put this output in excel and shared but we need expected output from the query.

Okay, but as long as we don't know what you want to do, we can't show you how to do it.

Once again (for example), how exactly do you get the expected value -.2083 on the last row?  You said

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

That's just the kind of explanation we need, but the difference between those dates is +.1785 days, not -.2083 days.

Also, what you said was the previous actual_end_date isn't from the previous row; it's from the same row.; the previous value was  a full day earlier.

What does "previous" mean, anyway?  Does it mean in order by actual_end_date? What if a row with an earlier actual_end_date has a later actual_start_date?  For example, does this row

ACTUAL_START_DATE      ACTUAL_END_DATE 
5/26/2020 8:00:00 AM   5/27/2020 8:00:00 AM

come before or after the following row

ACTUAL_START_DATE      ACTUAL_END_DATE 
5/26/2020 2:00:00 PM   5/26/2020 8:00:00 PM

?  Could it depend on the other columns?  What if there's a tie?

By the way, if you're asking about output as a number of days, it would be life easier for everyone (yourself included) if the times were always multiples of (say) .25 days apart, to make the calculations easier for us humans.