Daterange Overlap Another Daterange

Question

Post reply

Daterange Overlap Another Daterange

joe_wayman

Say Hey Kid

Points: 663
More actions
March 19, 2008 at 9:39 pm

#204332

I have what I thought was a simple query to write...but I am perplexed.
I have a table [roster] with a population of people and it includes military service dates...
Servicefrom and ServiceTo...(both datetime fields)
I need to know how many people served during a war.
If they served in several...I am only counting the first.
below is the Query I am using.
SELECT
State,
Period=CASE
WHEN [ServiceFrom] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'
WHEN [ServiceFrom] BETWEEN '1950-06-27' AND '1955-01-31' THEN 'KOREA'
WHEN [ServiceFrom] BETWEEN '1964-08-05' AND '1975-05-07' THEN 'VIETNAM'
WHEN [ServiceTo] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'
WHEN [ServiceTo] BETWEEN '1950-06-27' AND '1955-01-31' THEN 'KOREA'
WHEN [ServiceTo] BETWEEN '1964-08-05' AND '1975-05-07' THEN 'VIETNAM'
ELSE 'OTHER'
END,
Count=count(1)
FROM roster
WHERE [STATUS]='ACTIVE'
GROUP BY
State,
CASE
WHEN [ServiceFrom] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'
WHEN [ServiceFrom] BETWEEN '1950-06-27' AND '1955-01-31' THEN 'KOREA'
WHEN [ServiceFrom] BETWEEN '1964-08-05' AND '1975-05-07' THEN 'VIETNAM'
WHEN [ServiceTo] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'
WHEN [ServiceTo] BETWEEN '1950-06-27' AND '1955-01-31' THEN 'KOREA'
WHEN [ServiceTo] BETWEEN '1964-08-05' AND '1975-05-07' THEN 'VIETNAM'
ELSE 'OTHER'
END
ORDER BY State, Period
results
----------------------
state Period Count
----------------------
AZKOREA1
AZOTHER31
AZVIETNAM4
CAOTHER37
WAOTHER1
----------------------
At first glance it gives me what I wanted...until I realized that if I have a person that served during one of the specified war times...but their start and end dates happened to both be outside of that range.
So I guess I need to know if any day within their service range...falls within the specified war-time
I think I just need another pair of eyes....
Thanks.
Joe

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

joe_wayman Say Hey Kid Points: 663 More actions · Answer 1

Well,

sometimes you just need to write down what you're trying to do..to gain a little perspective.

I added another WHEN statement for each war...and if the war start date was within the service period...then I counted it...since the only exception I was trying to catch, was if a persons service date completely spanned a war...then that war's start date must have fallen within the service dates...so it's caught.

Thanks for reading...hopefully it can help someone else. 🙂

CASE

WHEN [Service_From] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'

WHEN [Service_To] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'

WHEN '1941-12-07' BETWEEN [Service_From] AND [Service_To] THEN 'WWII'

WHEN [Service_From] BETWEEN '1950-06-27' AND '1955-01-31' THEN 'KOREA'

WHEN [Service_To] BETWEEN '1950-06-27' AND '1955-01-31' THEN 'KOREA'

WHEN '1950-06-27' BETWEEN [Service_From] AND [Service_To] THEN 'WWII'

WHEN [Service_From] BETWEEN '1964-08-05' AND '1975-05-07' THEN 'VIETNAM'

WHEN [Service_To] BETWEEN '1964-08-05' AND '1975-05-07' THEN 'VIETNAM'

WHEN '1964-08-05' BETWEEN [Service_From] AND [Service_To] THEN 'WWII'

ELSE 'OTHER'

END

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Since there are likely to be more wars (unfortunately) why not set up a table of wars with the appropriate date ranges and then join against it? That way you wouldn't have to rewrite your code if you decide to include the invasion of Panama or some other conflict.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

joe_wayman Say Hey Kid Points: 663 More actions · Answer 3

Great idea...And if this becomes more than a one time ad-hoc request...that is surely going to happen.

Thanks.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

I think you missed the possibility that service start could before the start of the war and service end is after the end of the war.

When checking for date range overlaps, there are three overlap conditions to check:

Service start >= war start and service start < war end

Service end > war start and service end <= war end

Service start before war start and service end after war end

The following is a general solution to this type of problem:

select

a.*

from

MyTable a

cross Join

MyDateRanges b

where

-- Start Date in range of Start thru End

( a.MyStartDate >= b.StartDate and a.MyStartDate < b.EndDate ) or

-- End Date in range of Start thru End

( a.MyEndDate > b.StartDate and a.MyEndDate <= b.EndDate ) or

-- Start Date before Start and End Date after End

( a.MyStartDate b.EndDate )

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Can't you just logically do it with 2 conditions:

(StartOfService < WarEnd) and (EndOfService > WarStart)

Should cover all possibilities, IMO....

Of course - this only answers the questions of "who served DURING a war", which is a little different from "who served IN a war". You could be stateside, never deployed, still in training, etc... I know - whole different can o' worms...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

joe_wayman Say Hey Kid Points: 663 More actions · Answer 6

Thank you for the suggestion. Unless I am missing something, I think that our two solutions are the same...I check for the same 3 conditions that you are...the only difference is that I am using a Case statement, instead of creating a table of wartime dates and joining to it.

I like your way better though...and if this this request comes up again, I will surely employ it instead of this kludge of case logic.

Thanks again!

Service start >= war start and service start < war end

WHEN [Service_From] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'

Service end > war start and service end <= war end

WHEN [Service_To] BETWEEN '1941-12-07' AND '1946-12-31' THEN 'WWII'

Service start before war start and service end after war end

WHEN '1941-12-07' BETWEEN [Service_From] AND [Service_To] THEN 'WWII'

joe_wayman Say Hey Kid Points: 663 More actions · Answer 7

I just implemented your solution, and it is much, much cleaner and easier than using the case logic.

Thanks again

😀

select

a.*

from

MyTable a

cross Join

MyDateRanges b

where

-- Start Date in range of Start thru End

( a.MyStartDate >= b.StartDate and a.MyStartDate < b.EndDate ) or

-- End Date in range of Start thru End

( a.MyEndDate > b.StartDate and a.MyEndDate <= b.EndDate ) or

-- Start Date before Start and End Date after End

( a.MyStartDate b.EndDate )

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

I think if you look at your case logic, you will realize it did not cover all the conditions. Also, it did not really allow for service that covered multiple wars, example: service from 1944 through 1966.

joe_wayman Say Hey Kid Points: 663 More actions · Answer 9

I noticed that...but my original request stated that if a person served during multiple wars...I would only count the first....so the case logic was sufficient.

But I prefer your cross join...that way I get a realistic count...I still have to omit the secondary war records...but that's ok...

We are applying for non-profit status as a veterans organization...and this report is to fill out the tax request forms...they don't pay attention to a person that served during a second or third war...they are only interested whether or not we have members that are wartime veterans...

Again, Thanks for that..I probably would have never thought of a cross join.

Tom Mostek Newbie Points: 7 More actions · Answer 10

Hi,

You're conditional for testing whether a person served during a war is still not quite right - the issue if someone's service start/end was outside the bounds of the war. You are close with testing if the start or end of service is within the war, but missing the case when the service subsumed the war.

You are dealing with overlapping time intervals (i.e. service_Start to service_end and war_start to war_end). In this particular case, you are interested in keeping any record that has any overlap of these two time periods, which means:

CASE

WHEN service_start <= war_end -- active before the war ended

AND service_end >= war_start -- active after the war started

THEN [War Name] -- combine the two and he's active during the war

END

You have been finding only people who service was started and ended during the war itself.

joe_wayman Say Hey Kid Points: 663 More actions · Answer 11

Thank you for taking the time to respond.

You are correct, the Case Logic was lacking....but the Cross Join solution that was offered up...was exactly what I should have been doing all along.

I modified my code to use this logic instead, and all is well now.

Thanks, again.