Overlapping date ranges in MSSQL

I have the following data:

contract STDate   contractEndDate              
A.12/12/2012       03/31/2014        Capture - Yes
B. 04/2/2013        04/02/2014        Capture - No
C. 04/21/2013      03/31/2014        Capture  - Yes


I'd like to see results as follows:
I need to capture the fiscal year : 04-01-2013 ---> 03-03-2014


What do I add to the core  part of this query to get the appropriate dated contracts to be considered?

SELECT A.*,
 
 FROM PS_CONTRACT  A, PS_CONTRACT_DIS B
 
  WHERE   A.CONTRACT_NUM = B.CONTRACT_NUM
     AND A.EFFSEQ = B.EFFSEQ
    AND B.EFFDT = A.EFFDT
 
     AND  A.START_DATE >= '2013-04-01'
    -AND A.END_DATE <= '2014-03-31'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CONTRACT A_ED
        WHERE A.CONTRACT_NUM = A_ED.NC_CONTRACT_NUM
          AND A_ED.EFFDT <= '2014-03-31')
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_CONTRACT A_ES
        WHERE A.CONTRACT_NUM = A_ES.CONTRACT_NUM
          AND A.EFFDT = A_ES.EFFDT)
     AND A.APPR_STATUS = 'A'

I don't capture dates that are before the start date  like  A (above)--
A.12/12/2012       03/31/2014        


Any help would be greatly appreciated.

Nigluc
BrockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>04-01-2013 ---> 03-03-2014
Check your demo data and tell us how you would get the above result.

The simple answer might be to do a single query that contains Min(contract STDate) as start_date, max(contractEndDate) as end_date, and grouped by whatever defines contracts.

As an aside, I wrote an article T-SQL: Identify bad dates in a time series a couple of months ago that demonstrates how to take a time series, and grouped by some ID field rank all rows by the begin date, and do a ton of date validating.   If your needs are very detailed, you might need to go this route.
0
BrockAuthor Commented:
I need to capture all contracts within the fiscal year : 04-01-2013 ---> 03-31-2014

Any contracts that start before 04-01-2013  and end within the fiscal year are also needed.

I will look into what you have referenced.

Thank you,
Nigluc
0
tourmalinecanyonCommented:
The main cause of your issue is that you are limiting the start date and it won't capture a contract that started in the prior FY but ended in the FY you are looking at (like A. above).  Simplest correction in my eyes is to remove the start date line and modify the end date like below:

AND A.END_DATE is between '2013-04-01' and '2014-03-31'

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

BrockAuthor Commented:
Thank you ! Believe or not when I chose sometime to de-stress and move on to another problem I also visualized this soln :-)

NIGLUC
0
PortletPaulfreelancerCommented:
here's a visualization for you :)
    LOW                 HIGH
-----|-------------------|-----
     |                   |
S-----------E            |          starts before, ends in
     | S---------------E |          starts in, ends in
     |    S----------------------E  starts in, ends after
     |                   |
S--------------------------------E  spans
     |                   |

            S < HIGH
            E > LOW

Open in new window

but I really wanted to say - don't use "between" for date ranges.

Use a combination of >= with < like this:

AND ( A.END_DATE >= '2013-04-01' and A.END_DATE < '2014-04-01' )

for more extensive reasons why:
"Beware of Between"
"What do BETWEEN and the devil have in common?"
0
BrockAuthor Commented:
Thanks PortalPaul for takiing time out to draw this out for me :-) .  I will adjust my query and will read up on the between story

Nigluc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.