Calculate weeks that overlap in two date ranges

[sven2]

Hello,

can somebody help me with the following problem:

given two date ranges, each with start and end dates, how do you determine if the ranges overlap and calculate the number of weeks in these overlap in a query?

For example:

Time to develop a paperbox:
startdate 1/10/2009
enddate 30/10/2009

The filter:
startdate 15/9/2009
enddate 15/10/2009

So the overlap is from 1/10 until 15/10/2009 (2 weeks) ...

How can I make this in a query?

Thanks in advance,
Sven.

 

[namliam]

FilterEndate >= DevelopStartDate ??

Try looking into the datediff function to find the period to overlap?

 

[pbaldy]

FilterEndate >= DevelopStartDate ??

Actually you have to test that plus that the FilterStartDate <= DevelopEndDate. Filter start/end dates in November would meet that test, but don't actually overlap.

 

[sven2]

Hello,

can I solve this with the datediff function?

It is so that the datediff function calculate the time between 2 dates ... but how can I make it so that is take into account the overlap time in weeks?

Best regards,
Sven.

 

[pbaldy]

I was bored and had started this before, so:

http://www.baldyweb.com/OverLap.htm

I suspect you could use DateDiff, but you'd have to account for the different possibilities of overlap. I have to take off, but I'll try to ponder it later if the MailMan or somebody else hasn't sorted it out.

 

[namliam]

Datediff > Read the help, tells you more and better than I would here.

 

[DCrake]

From what you state you can have the following

Overlaps prior to filter start date
Overlaps after filter end date
Overlaps either side of the filter range
No overlap date are same

Then if your date range is samller than the start stop range you can have

Gap between Start date and filter start date
Gap between End date and filter end date
Gaps either side of the start and stop date

So there are a possible 7 variations.

David