Solved

sql date range/overlapping dates

Posted on 2003-10-23
8
705 Views
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!!!
0
Comment
Question by:babybird
  • 3
  • 2
  • 2
8 Comments
 
LVL 9

Expert Comment

by:xenon_je
Comment Utility
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..
  xenon
0
 

Author Comment

by:babybird
Comment Utility
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!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Is this specifically related to lob_cd 1 and 2, or could it apply to other lob_cds as well?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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,
      lob_type_end_dt
FROM lob_contract_range lcr2

0
 

Author Comment

by:babybird
Comment Utility
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.
0
 
LVL 9

Expert Comment

by:xenon_je
Comment Utility
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
 :))
  xenon
0
 

Author Comment

by:babybird
Comment Utility
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!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now