Home » SQL & PL/SQL » SQL & PL/SQL » Date difference from Prior and Current rows (merged)
Date difference from Prior and Current rows (merged) [message #390745] Mon, 09 March 2009 10:17 Go to next message
dev_plsql
Messages: 2
Registered: January 2009
Junior Member
Hi,

We have series of rows available in the below structure
and the data is sorted on start date, end date

ID Start Date End Date Result Final Result
1 5-Dec-07 7-Dec-07 2 0
1 6-Dec-07 8-Dec-07 3 0
1 6-Dec-07 10-Dec-07 5 5

2 5-Dec-07 7-Dec-07 2 2
2 8-Dec-07 10-Dec-07 2 0
2 9-Dec-07 11-Dec-07 3 3
2 12-Dec-07 14-Dec-07 2 2


Here, "Result" should be computed, not just by deducting end date from start date.

Instead, within the current "ID" value, we have to take the current start date and check whether it falls between start and end date from the prior rows. If so, we need to find out the number of days difference between start date of the matching row and end date of the current row.

After computing the result, to get the final result, in the iteration,we need to take the start date and check whether the prior date range values fall in between, if so, set "0" for those matching rows and not for the current row being processed.

Finally, we will sum up the final result field and get the below result,

ID Actual Result

1 2
2 7

Now, this requirement can be met easily using PL/SQL code, we would like to get this done using SQL statement alone and without applying Catesian Product method as we will process a large volume of data.

Please share your views and comments, thanks.

Regards,
Dev
Re: Date difference from Prior and Current rows [message #390747 is a reply to message #390745] Mon, 09 March 2009 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Please keep in mind that rows in a table have NO inherent order.


[Updated on: Mon, 09 March 2009 10:25]

Report message to a moderator

Re: Date difference from Prior and Current rows [message #390748 is a reply to message #390745] Mon, 09 March 2009 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do NOT cross/multi-post
Re: Date difference from Prior and Current rows (merged) [message #390754 is a reply to message #390745] Mon, 09 March 2009 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data, and explain it.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel

[Updated on: Mon, 09 March 2009 11:15]

Report message to a moderator

Re: Date difference from Prior and Current rows (merged) [message #390766 is a reply to message #390745] Mon, 09 March 2009 11:35 Go to previous messageGo to next message
dev_plsql
Messages: 2
Registered: January 2009
Junior Member
Hi,

I have provided the SQL statements below and attached examples herewith for your quick reference,

create table FindDateDiff
(ID NUMBER(5),
Start_Date DATE,
End_Date DATE,
Result NUMBER(4),
Final_Result NUMBER(4));

begin

insert into FindDateDiff(ID, Start_Date, End_Date)
values(1, '5-Dec-07', '7-Dec-07');

insert into FindDateDiff(ID, Start_Date, End_Date)
values(1, '6-Dec-07', '8-Dec-07');

insert into FindDateDiff(ID, Start_Date, End_Date)
values(1, '6-Dec-07', '10-Dec-07');

insert into FindDateDiff(ID, Start_Date, End_Date)
values(2, '5-Dec-07', '7-Dec-07');

insert into FindDateDiff(ID, Start_Date, End_Date)
values(2, '8-Dec-07', '10-Dec-07');

insert into FindDateDiff(ID, Start_Date, End_Date)
values(2, '9-Dec-07', '11-Dec-07');

insert into FindDateDiff(ID, Start_Date, End_Date)
values(2, '12-Dec-07', '14-Dec-07');
end;


From the example, we need to iterate data by grouping against "ID" field. We have to take the current start date and check whether it falls between start and end date from the prior rows. If so, we need to find out the number of days difference between the first start date (Minimum) within the ID and end date of the current row. Please refer the attached excel document and it contains a few examples.

Now, we need to eliminate the overlapping date values, for #1, 6th Dec, comes between both 2nd and 3rd rows, so they are ignored. For #2, 8th Dec falls between 5th and 7th, so they are ignored and the rest do not overlap, so difference is retained and summed up finally.

Since, we process a large volume of data, I cannot use self-join to iterate the rows, LEAD and LAG analytical functions look slightly complicated to use as I need to compare against the entire set of prior rows within the ID, not just the immediate prior values alone.

I am thinking about different ways of doing it like keeping some intermediary table in between and getting the expected results, please share your thoughts and it will definitely
be helpful.

I think this message was posted twice accidently as the first attempt was unsuccessful and terminated, I am sorry about that.

Thank you.

Regards,
Dev
Re: Date difference from Prior and Current rows (merged) [message #390779 is a reply to message #390766] Mon, 09 March 2009 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
check whether it falls between start and end date from the prior rows.

"prior" regarding which order?

I still don't understand your requirements. For instance, "for #1, 6th Dec, comes between both 2nd and 3rd rows, " what does this mean?

In addition, your post is not formatted (althought some will say it does not matter) and:
SQL> insert into FindDateDiff(ID, Start_Date, End_Date)
  2  values(1, '5-Dec-07', '7-Dec-07');
values(1, '5-Dec-07', '7-Dec-07')
          *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected

'5-Dec-07' is a string not a date.

Regards
Michel
Re: Date difference from Prior and Current rows (merged) [message #390780 is a reply to message #390745] Mon, 09 March 2009 14:22 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member

>"I am thinking about different ways of doing it like keeping some intermediary table in between and getting the expected results, please share your thoughts"


More often than not, an "intermediary table" is unnecessary & inefficient.
Previous Topic: get row above stated on
Next Topic: Execute or Call shell script from oracle PLSQL procedure
Goto Forum:
  


Current Time: Thu Dec 08 20:11:35 CST 2016

Total time taken to generate the page: 0.13739 seconds