sql date range/overlapping dates

I have the following sql that needs to be altered to do some additional date processing.  It has to be done in one query because it's being used behind a PB datawindow and in a view and I don't know if it's possible so looking for help from the sql experts.

This is the current query: (what it does is give me rows based on the following lob logic.  If a 1 exists, I don't want 2,3,or 5.  If no 1 exists, I want 2,3,5.  I always want 4.  It works great however we just realized we have the possibility of overlapping dates so now we need to apply the logic to date ranges.

select sa.affiliation_nmbr, sa.affiliation_member_seq,      sa.group_nmbr, sa.section_nmbr,
lcr.lob_cd,lcr.lob_type_begin_dt, lcr.lob_type_end_dt
      from lob_contract_range lcr inner join sub_affiliation sa
            on lcr.sub_affiliation_sak = sa.sub_affiliation_sak
            where (lob_cd in ('1','4') or NOT EXISTS (SELECT     1
                         FROM  lob_contract_range lcr2
                              WHERE      lcr2.lob_cd = '1' AND
      lcr2.sub_affiliation_sak = lcr.sub_affiliation_sak   ))
and affiliation_nmbr = '885'

This is the sample data that presented a problem:
affiliation_nmbr affiliation_member_seq group_nmbr section_nmbr lob_cd lob_type_begin_dt lob_type_end_dt
885  A      36560        0930      1    2003-03-01     2003-07-31
885  A      36560       0930      2    2003-03-01     2004-02-29

Note that the dates overlap.  What my results should be after apply lob logic is a lob 1 for 3/1 throug 7/31 because a 1 exists, therefore I ignore the 2.  I should get a 2 for 8/1 through 2/29/2004 because only a 2 exists for that period.  I use this query as a derived table in a bigger query where I then have a date that looks to exist between the date results to see what lob I should use. (See below)

Some initial thoughts I had were if I could get the results to return a derived begin and end dates so for example, if i could get it to return
lob 1 3/1/2003 to 7/31/2003
lob 2 3/1/2003 to 7/31/2003
lob 2 8/1/2003 to 2/29/2004
in an additional derived query or subquery of some sort and then I could apply the lob logic to those results.  or get a month by month such as for 3/1/03-3/31/03 it's a 1, for april it's a 1, for august it's a 2.  or subtract the dates somehow.  

Easy enough but getting it done in one query presents a problem.  Maybe I need a whole new way of looking at it.

fyi...here's the full query:
select A.*
FROM dbo.contract_count_detail A inner join
(select sa.affiliation_nmbr, sa.affiliation_member_seq,      sa.group_nmbr, sa.section_nmbr, lcr.lob_cd,lcr.lob_type_begin_dt, lcr.lob_type_end_dt
      from lob_contract_range lcr inner join sub_affiliation sa
      on lcr.sub_affiliation_sak = sa.sub_affiliation_sak                         where (lob_cd in ('1','4') or                                     NOT EXISTS (SELECT     1                                                 FROM          lob_contract_range lcr2
                          WHERE      lcr2.lob_cd = '1' AND
            lcr2.sub_affiliation_sak = lcr.sub_affiliation_sak ))) as filtered_lobs
            on A.affiliation_nmbr = filtered_lobs.affiliation_nmbr AND
            A.affiliation_member_seq = filtered_lobs.affiliation_member_seq AND
                                A.group_nmbr = filtered_lobs.group_nmbr AND
            A.section_nmbr = filtered_lobs.section_nmbr and
            substring(A.ulp,4,1) = filtered_lobs.lob_cd
WHERE     A.coverage_dt between filtered_lobs.lob_type_begin_dt and filtered_lobs.lob_type_end_dt and
      (A.status_flg <> 'D' OR  A.status_flg IS NULL)

Any thoughts?

your help is very greatly appreciated!!!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

lets say I understood 20% of it....:((((

Try to give to one of your firends what you wrote here (one that does not know a thing about this)...and ask him if he understood something....

I read twice the above question.....
I will try again tomorrow...maybe is because I'm tired... :((

be back tomorrow..
babybirdAuthor Commented:
Sorry about that...let me clarify the goal.  We are trying to apply what we refer to as lob logic to a set of rows and return the chosen lob_cd and the dates to which it applies so that we can further join to another table.  lob_cd can be a value from 1 through 5.  And there are dates for which that lob (aka: line of business) existed.  So my goal is to identify the lobs to be used based on the following logic... If the lob is a 1, I do not want to pull the lobs of 2,3, or 5.  I just want the 1.  If an lob of 1 does not exist, then I want the 2,3,and 5s.  And if an lob of 4 exists, I always want to keep the 4's.  So basically 1 is mutually exclusive of 2,3,5.  I either have a 1 and nothing else or I don't have a 1 so I return everything else.  This all works grand in the above queries but that was done with the assumption that the dates were always equal.  Now we are saying the dates aren't always equal and they overlap.  So now I need to determine the chosen lobs based on date ranges.  

So let me give a couple of examples of a set of just the lobs and dates for a higher level set.

Example A
lob_cd     begin date      end date
1            1/1/2003        12/31/2003
2            1/1/2003        12/31/2003
3            1/1/2003        12/31/2003
4            1/1/2003        12/31/2003
5            1/1/2003        12/31/2003

In Example A, with my initial query I return a 1 and a 4.  The 2,3,and 5 are ignored because I have a 1.  The 4 is returned because if a 4 exists, I always return it, I don't care what else exists.

Example B
lob_cd     begin date      end date
2            1/1/2003        12/31/2003
3            1/1/2003        12/31/2003
4            1/1/2003        12/31/2003
5            1/1/2003        12/31/2003

In Example B, I would return a 2,3,4,5. 2,3,5 are returned because I do not have a 1.  Again the 4 is always returned simply because it exists.

Now I have a dilemma if the dates are different.  

lob_cd     begin date      end date
1            1/1/2003        7/31/2003 *note this lob_cd ends early
2            1/1/2003        12/31/2003
3            1/1/2003        12/31/2003
4            1/1/2003        12/31/2003
5            1/1/2003        12/31/2003

What I need to return here is an lob_cd of 1 for the time frame 1/1/2003 through 7/31/2003 because when I apply the logic to that timeframe 1 emerges.  For the timeframe of 8/1/2003 through 12/31/2003, 1 no longer existed so when I apply the logic for that timeframe 2,3,5 should be returned.  In the initial query, because it doesn't look at dates, I'm getting a 1 for the entire timeframe.   This is incorrect.

Is that clear as mud?  Sorry, very hard to explain but a simple problem with a difficult solution because we would like to accomplish it in one query.  An interesting puzzle!
Scott PletcherSenior DBACommented:
Is this specifically related to lob_cd 1 and 2, or could it apply to other lob_cds as well?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
OK, I *think* *maybe* the query below will adjust the dates as needed **for adjacent log_cds, i.e., 1-2, 2-3, etc. ONLY **.  You could replace the original lob_contract_range table in the main query with this as a derived table with an alias of lcr, which should mean the rest of the query can stay exactly as it is.  I ran a very small test on this, but have not done extensive testing.:

SELECT affiliation_nmbr, affiliation_member_seq, group_nmbr,
      section_nmbr, lob_cd, lob_type,      ISNULL(
      (SELECT DATEADD(DAY, 1, lcr3.lob_type_end_dt)
       FROM lob_contract_range lcr3
       WHERE lcr2.affiliation_nmbr = lcr3.affiliation_nmbr
       AND lcr2.affiliation_member_seq = lcr3.affiliation_member_seq
       AND lcr2.group_nmbr = lcr3.group_nmbr
       AND lcr2.section_nmbr = lcr3.section_nmbr
       AND lcr3.lob_cd = lcr2.lob_cd - 1
       AND lcr2.lob_type_begin_dt < DATEADD(DAY, 1, lcr3.lob_type_end_dt)
       AND DATEADD(DAY, 1, lcr3.lob_type_end_dt) < lcr2.lob_type_end_dt),
           lcr2.lob_type_begin_dt) AS lob_type_begin_dt,
FROM lob_contract_range lcr2

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
babybirdAuthor Commented:
I think you are on to something.  Certainly brings back the proper dates but I could have for a given set all of the following lobs 1,2,3,4,5 so I can't use the
lcr3.lob_cd = lcr2.lob_cd - 1 but if I could get all the lobs and the dates split up like you have, I could use it as a derived table and then apply lob logic to the results.  So for example let's say this is my data:

lob_cd  begin_dt   end_dt
1         1/1/2003   7/31/2003
2        1/1/2003   2/29/2004
3        1/1/2003   2/29/2004
4        1/1/2003   2/29/2004

In this case I need lob_cd 1 for 1/1/2003 - 7/31/2003 as you have accomplished and 2 and 3 for 8/1/2003 - 2/29/2004.  The lob logic rules say that if I have a 4 I keep it regardless of the others so in this case I want the 4 for the entire time period.

If we could get the results of your query to return
lob_cd  begin_dt   end_dt
1         1/1/2003   7/31/2003
2         1/1/2003   7/31/2003
3         1/1/2003   7/31/2003
4         1/1/2003   7/31/2003
2         8/1/2003   2/29/2004
3         8/1/2003   2/29/2004
4         8/1/2003   2/29/2004

I can then apply the lob logic to the result set where the dates are equal.  Just a thought.

If not, somehow I have to apply the lob logic for each date range which has the following rules:  If a lob_cd 1 exists ignore 2,3,5.  If 1 does not exist, keep 2,3,5.  4 is stand alone so if a 4 exists, always keep the 4.
man I'm too tired right now....just now I got to the internet..and it seems that ScottPletcher
 understood what you need....

I'm sure he can help you..... if you still will need help that I will reconect to this question...but I doubt you'll need my help...

good luck ScottPletcher
babybirdAuthor Commented:
The responses did not completely resolve the problem however it is very difficult to completely express what we needed to obtain and I'm not sure if it was even possible.  However, the need for this resolution became unnecessary as the requirements changed.  I'd be glad to split the points across all those who participated.  It was difficult to write, difficult to understand and I admire all those that took the time to contemplate it.

Thanks for your help!!
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.