Go Premium for a chance to win a PS4. Enter to Win


sql date range/overlapping dates

Posted on 2003-10-23
Medium Priority
Last Modified: 2008-03-04
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!!!
Question by:babybird
  • 3
  • 2
  • 2

Expert Comment

ID: 9611937
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..

Author Comment

ID: 9614199
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!
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9615115
Is this specifically related to lob_cd 1 and 2, or could it apply to other lob_cds as well?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 70

Accepted Solution

Scott Pletcher earned 1500 total points
ID: 9615211
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


Author Comment

ID: 9615408
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.

Expert Comment

ID: 9618895
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

Author Comment

ID: 10021694
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!!

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question