Page 1 of 1
overlapping dates
Posted: Fri Apr 22, 2011 8:00 pm
by Nagaraj
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
Posted: Fri Apr 22, 2011 9:09 pm
by chulett
Server or Parallel job? You marked it one way and posted in the other forum, hence the question.
Posted: Fri Apr 22, 2011 9:14 pm
by Nagaraj
oh sorry chulett ,
its a server job.
Posted: Fri Apr 22, 2011 9:35 pm
by chulett
... and here we are.
Posted: Fri Apr 22, 2011 11:05 pm
by ray.wurlod
Get all the dates into internal format, if they aren't already, then it's a matter of simple arithmetic. For example:
Code: Select all
svStartDateOver5Days <-- Abs(q_start_date - p_start_date)
svpInterval <-- p_end_date - p_start_date
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).
Posted: Sat Apr 23, 2011 6:31 am
by Nagaraj
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,
Posted: Sat Apr 23, 2011 5:00 pm
by ray.wurlod
I disagree. Think about it some more. Do some examples on paper.
Posted: Mon Apr 25, 2011 7:57 am
by Nagaraj
Okay...
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
Posted: Mon Apr 25, 2011 8:03 am
by chulett
What happened to the "which should be more than or equal to 5" part?
Posted: Mon Apr 25, 2011 8:10 am
by Nagaraj
start date or end_date is included so, its together 5 days.
Posted: Mon Apr 25, 2011 9:23 am
by chulett
Ah... true.
Posted: Mon Apr 25, 2011 4:45 pm
by ray.wurlod
Now all you need is correct DataStage BASIC syntax (hint: there's no "fi" and you may need some block-ending "End" statements).