overlapping dates
Moderators: chulett, rschirm, roy
overlapping dates
Scenario
dates in mm/dd/yyyy
p_startdate p_end_date q_start_date q_end_date
01/06/2011 06/06/2011 01/06/2011 07/06/2011 --- 1 row
04/07/2011 10/07/2011 04/07/2011 11/07/2011 --- 2 row
overlapping dates
1 row ---- 01/06/2011 02/06/2011 03/06/2011 04/06/2011 05/06/2011 06/06/2011
2 row ---- 04/07/2011 05/07/2011 06/07/2011 07/07/2011 08/07/2011 09/07/2011 10/07/2011
OUTPUT
return true if the difference between p dates and q dates are five days or more and five dates overlap in both of them
hope this is clear.
let me know your thoughts on how do i approach this
thanks
dates in mm/dd/yyyy
p_startdate p_end_date q_start_date q_end_date
01/06/2011 06/06/2011 01/06/2011 07/06/2011 --- 1 row
04/07/2011 10/07/2011 04/07/2011 11/07/2011 --- 2 row
overlapping dates
1 row ---- 01/06/2011 02/06/2011 03/06/2011 04/06/2011 05/06/2011 06/06/2011
2 row ---- 04/07/2011 05/07/2011 06/07/2011 07/07/2011 08/07/2011 09/07/2011 10/07/2011
OUTPUT
return true if the difference between p dates and q dates are five days or more and five dates overlap in both of them
hope this is clear.
let me know your thoughts on how do i approach this
thanks
Last edited by Nagaraj on Fri Apr 22, 2011 9:15 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Get all the dates into internal format, if they aren't already, then it's a matter of simple arithmetic. For example:
You need to have a tighter definition of "difference", in my opinion. Is it the difference between start dates, or between one end date and the other start date and, if the latter, can it be either way?
Overlaps can be determine via the end date of one being compared with the start date of the other (each way).
Code: Select all
svStartDateOver5Days <-- Abs(q_start_date - p_start_date)
svpInterval <-- p_end_date - p_start_date
Overlaps can be determine via the end date of one being compared with the start date of the other (each way).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
i didn't get quite understand.
svStartDateOver5Days <-- Abs(q_start_date - p_start_date) >>>> difference in days
svpInterval <-- p_end_date - p_start_date >>> this would give me date difference in days as well
concept
----------
1. difference is between p_startdate p_end_date AND q_start_date q_end_date.
2. Both p and q differences would produce 2 sets of number of days which should be more than or equal to 5.
3. then we should find the overlapping dates between these two sets.
PS: Both the p_startdate and q_start_date will start on or around the same dates, but it can be 2 or 3 days behind or ahead.
so its little hard to determine overlaps by using one end and the other start date,
svStartDateOver5Days <-- Abs(q_start_date - p_start_date) >>>> difference in days
svpInterval <-- p_end_date - p_start_date >>> this would give me date difference in days as well
concept
----------
1. difference is between p_startdate p_end_date AND q_start_date q_end_date.
2. Both p and q differences would produce 2 sets of number of days which should be more than or equal to 5.
3. then we should find the overlapping dates between these two sets.
PS: Both the p_startdate and q_start_date will start on or around the same dates, but it can be 2 or 3 days behind or ahead.
so its little hard to determine overlaps by using one end and the other start date,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Okay...
looks like this should work.
looks like this should work.
Code: Select all
if (p_start_date>=q_start_date)
if((q_end_date-p_start_date)>=4) and ((p_end_date-p_start_date)>=4)
then
"Overlap"
else
"No Overlap"
fi
else
if((p_end_date-q_start_date)>=4) and ((q_end_date-q_start_date)>=4)
then
"Overlap"
else
"No Overlap"
fi
fi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: