how to compare date periods ?

Hi,
I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.
any idea ?
thankswhere [P1].[StartDate] <= [P2].[EndDate]
and [P1].[EndDate] >= [P2].[StartDate]|||blindman, that's only a complete overlap (2nd period completely within the first)

what about partial overlaps

july 15 to august 15 partially overlaps august 1 to august 31|||What about:

select case
when P1.Startdate between P2.startdate and P2.Enddate and P1.Enddate between P2.startdate and P2.Enddate then 'Include'
when P1.Startdate between P2.startdate and P2.Enddate or P1.Enddate between P2.startdate and P2.Enddate then 'OverLaps'
else 'Does not Overlap or is included'
end|||nice, DMWCincy, but you're missing blindman's conditions :)

this topic has been covered before, e.g. http://www.dbforums.com/t1147226.html, but no point looking that up, because this site has lost its archives for old posts (which is one of the reasons i'll be leaving shortly)|||blindman, that's only a complete overlap (2nd period completely within the first)

what about partial overlaps

july 15 to august 15 partially overlaps august 1 to august 31

Actually, blindman's logic is correct and will handle partial overlaps such as the scenario you specified as well as complete overlaps.|||blindman, that's only a complete overlap (2nd period completely within the first)

what about partial overlaps

july 15 to august 15 partially overlaps august 1 to august 31

Since blindman is offline, I'll note that I use the same technique, and it will find partial overlaps, including your sample. Substituting your values into his WHERE clause:

where july 15 <= august 31
and august 15 >= august 1

You can see that your sample record would be returned by his WHERE clause.

Edit: Sorry RogerWilco, your post wasn't there when I started typing.|||Actually, blindman's logic is correct .aaargh, you're right, i read it wrong

sorry :o :o|||OK, now I don't get to post some snooty and tactless comment to Rudy about how my code DOES work, 'cause you guys already took all the fun away.

--Humph!--|||that's okay, i feel sufficiently blindman-chastised already|||I can't believe it was so easy !
thanks blindman|||this topic has been covered before, e.g. http://www.dbforums.com/t1147226.html, but no point looking that up, because this site has lost its archives for old posts (which is one of the reasons i'll be leaving shortly)
Sorry to hear you will be leaving us rudeboy.

But I just can't help myself - don't you keep copies of posts like this?:p|||yes, i do :) :)

and how did you know rudeboy was one of my nicknames?

will i be seeing you on one of the other sites i mentioned in this thread (http://www.dbforums.com/t1198344.html)?|||and how did you know rudeboy was one of my nicknames?
I imagine your friends have the same colossal imagination as I :p


will i be seeing you on one of the other sites i mentioned in this thread (http://www.dbforums.com/t1198344.html)?Ever so kind of you to ask - I must admit Tek Tips regularly comes up on Googles - wish they would get rid of the little drop-down-over-the-text-you-are-reading-just-as-it-gets-interesting box. I might indeed have a look - as you know I have a penchant for ANSII SQL also.

I quite like playing around on this (http://www.experts-exchange.com/)site (another that came up regularly on searches, so I joined). They give you points and from these you are assigned worth. It's ever so self validating. I'm not sure that there are the sort of heavy weights in residence as there are on this forum though.

I see from your link a lot of you are going to be moving. I may not post in this forum often but I visit frequently - I've learnt lots and have been tremendously entertained, expecially when pickings are poor and you are forced to turn on one another ;)

I hope you all do well - drop us a post in the baby db forum (AKA Access) to let us know your new roaming grounds.|||...They give you points and from these you are assigned worth. It's ever so self validating. devshed forums are like that, you build up "rep points" (reputation) which you can then spread around to others

see http://forums.devshed.com/member.php?userid=22396