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  |
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 (merged) [message #390754 is a reply to message #390745] |
Mon, 09 March 2009 11:12   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 16:36:10 CST 2025
|