overlapping dates

Nagaraj · Post by **Nagaraj** » Fri Apr 22, 2011 8:00 pm

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

chulett · Post by **chulett** » Fri Apr 22, 2011 9:09 pm

Server or Parallel job? You marked it one way and posted in the other forum, hence the question.

Nagaraj · Post by **Nagaraj** » Fri Apr 22, 2011 9:14 pm

oh sorry chulett ,

its a server job.

chulett · Post by **chulett** » Fri Apr 22, 2011 9:35 pm

... and here we are.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 22, 2011 11:05 pm

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).

Nagaraj · Post by **Nagaraj** » Sat Apr 23, 2011 6:31 am

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,

ray.wurlod · Post by **ray.wurlod** » Sat Apr 23, 2011 5:00 pm

I disagree. Think about it some more. Do some examples on paper.

Nagaraj · Post by **Nagaraj** » Mon Apr 25, 2011 7:57 am

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

chulett · Post by **chulett** » Mon Apr 25, 2011 8:03 am

What happened to the "which should be more than or equal to 5" part?

Nagaraj · Post by **Nagaraj** » Mon Apr 25, 2011 8:10 am

start date or end_date is included so, its together 5 days.

chulett · Post by **chulett** » Mon Apr 25, 2011 9:23 am

Ah... true.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 25, 2011 4:45 pm

Now all you need is correct DataStage BASIC syntax (hint: there's no "fi" and you may need some block-ending "End" statements).