Solved

Finding Lowest Continuous Date in SQL

Posted on 2004-10-08
13
776 Views
Last Modified: 2008-03-06
I'm fairly new to SQL so please have pitty if this is a silly question, or if I pose it poorly...

I have a table where there are dates associated with members in my system.  I'm trying to find the lowest continuous effective date associated with these members.  The default value for the termdate, if there isn't one currently is 12/31/2078.  Here is some examples from member table:

Name          Effdate          Termdate
======    =======    ========
John            1/1/2002       12/31/2002
John            1/1/2003       12/31/2003
John            1/1/2004       12/31/2078
Jane            1/1/2001       12/31/2001
Jane            1/1/2002       12/31/2002
Jane            1/1/2004       12/31/2078

For John, I would want to return 1/1/2002.  For Jane, however, I would want to return 1/1/2004 since that is the lowest effective date without any breaks in coverage.

I hope this makes some simblance of sense.  Any help would be greatly appreciated.          
0
Comment
Question by:brianlayden
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 6

Assisted Solution

by:izblank
izblank earned 20 total points
Comment Utility
Try this:

SELECT Name, max(Effdate)
FROM MyTable t1
WHERE NOT EXISTS (SELECT * FROM MyTable t2
           WHERE t2.Name=t1.Name
           AND dateadd(day,-1,t1.Effdate) BETWEEN t2.Effdate and t2.Termdate)
GROUP BY Name

0
 
LVL 6

Expert Comment

by:mcp111
Comment Utility
Can you clarify this further:

For John, I would want to return 1/1/2002.  For Jane, however, I would want to return 1/1/2004 since that is the lowest effective date without any breaks in coverage.
0
 

Author Comment

by:brianlayden
Comment Utility
Basically, what I meant was that I want the lowest date returned where there is no break in the date (smallest date of continous coverage from now).  For John, there is no break in dates, so his min(effdate) would work.  For Jane, however, there is a one year break in the dates, so I would want the lowest date (1/1/03) after any break in coverage.  So:

Name          Effdate          Termdate
======    =======    ========
John            1/1/2002       12/31/2002
John            1/1/2003       12/31/2003
John            1/1/2004       12/31/2078
Jane            1/1/2000       12/31/2000
Jane            1/1/2001       12/31/2001
Jane            1/1/2003       12/31/2003
Jane            1/1/2004       12/31/2078
Billy             1/1/1999       12/31/1999
Billy             1/1/2001       12/31/2001
Billy             7/1/2003       12/31/2003
Billy             1/1/2004       12/31/2004
Billy             1/1/2005       12/31/2078

I would want:
John        1/1/2002
Jane        1/1/2003
Billy         7/1/2003

Does this make any more sense?  I guess I'm having a somwhat difficult time explaining the problem.

0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
looks like you need cursor to go over set of records related to 'Name' and analyze sequence of years, etc  1 year and more of break....
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
One of my favorite sneaky SQL tricks. Cursorless loops.

create table t1 (Name varchar(20), Effdate datetime, Termdate datetime)
insert t1 select 'John'            ,'1/1/2002',       '12/31/2002'
insert t1 select 'John'            ,'1/1/2003',       '12/31/2003'
insert t1 select 'John'            ,'1/1/2004',       '12/31/2078'
insert t1 select 'Jane'            ,'1/1/2000',       '12/31/2000'
insert t1 select 'Jane'            ,'1/1/2001',       '12/31/2001'
insert t1 select 'Jane'            ,'1/1/2003',       '12/31/2003'
insert t1 select 'Jane'            ,'1/1/2004',       '12/31/2078'
insert t1 select 'Billy'             ,'1/1/1999',       '12/31/1999'
insert t1 select 'Billy'             ,'1/1/2001',       '12/31/2001'
insert t1 select 'Billy'             ,'7/1/2003',       '12/31/2003'
insert t1 select 'Billy'             ,'1/1/2004',       '12/31/2004'
insert t1 select 'Billy'             ,'1/1/2005',       '12/31/2078'

create function f_minrun(@Name varchar(20)) returns datetime as
begin
declare @mindate datetime, @priordate datetime , @break int
select @break = -1

select @break =   case when @break <> 1 and dateadd(yy,1,Effdate) <> coalesce(@priordate,dateadd(yy,1,Effdate)) then 1 else @break end,
        @mindate = case when @break = 1 and @mindate is null then Effdate else @mindate end,
        @priordate = Effdate
from t1
where name = @Name
order by Effdate desc

return Coalesce(@mindate,@priordate)

end


select name, convert(varchar,dbo.f_minrun(name),101) MinDate from t1
group by name
order by name desc

name                 MinDate
-------------------- ------------------------------
John                 01/01/2002
Jane                 01/01/2001
Billy                07/01/2003
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
Oh sorry that's not right. Jane was wrong. Replace the function with this:

create function f_minrun(@Name varchar(20)) returns datetime as
begin
declare @mindate datetime, @priordate datetime , @break int
select @break = -1

select @break =   case when @break <> 1 and year(Effdate)+1 <> coalesce(year(@priordate),year(Effdate)+1) then 1 else @break end,
        @mindate = case when @break = 1 and @mindate is null then @priordate else @mindate end,
        @priordate = Effdate
from t1
where name = @Name
order by Effdate desc

return Coalesce(@mindate,@priordate)

end

Gives:

name                 MinDate
-------------------- ------------------------------
John                 01/01/2002
Jane                 01/01/2003
Billy                07/01/2003

 
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:brianlayden
Comment Utility
This is close, but they don't necessarily have to be different years, there can be a one month break, or even as small as a two week break.  So for example:

Name          Effdate          Termdate
======    =======    ========
Tom           01/01/2001    01/10/2001
Tom           02/15/2001    12/31/2001
Tom           01/01/2002    12/31/2078
Jane           05/24/2003    12/31/2003
Jane           01/01/2004    01/02/2004
Jane           01/16/2004    12/31/2078    

would need to return:
Tom           02/15/2001
Jane           01/16/2004

0
 
LVL 12

Accepted Solution

by:
kselvia earned 135 total points
Comment Utility
Sorry for the delay, I have been really busy.

Guess I didn't pay attention the the problem since I completly ignored termdate.  This sould fix it;

create function f_minrun(@Name varchar(20)) returns datetime as
begin
declare @mindate datetime, @priordate datetime,  @break int
select @break = -1
select  @break = case when @break <> 1 and dateadd(dd,1,Termdate) < coalesce(@priordate,dateadd(dd,1,Termdate)) then 1 else @break end,
        @mindate = case when @break = 1 and @mindate is null then @priordate else @mindate end,
        @priordate = Effdate
from t1
where name = @Name
order by Effdate desc
return Coalesce(@mindate,@priordate)
end

select name, convert(varchar,dbo.f_minrun(name),101) MinDate from t1
group by name
order by name desc

name                 MinDate
-------------------- ------------------------------
Tom                  02/15/2001
John                 01/01/2002
Jane                 01/01/2003
Jake                 01/16/2004
Billy                07/01/2003
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
Also, izblank's solution also generates the correct answer and it's easier to understand but my version performs better.  Indexes may improve either soltuion.
0
 
LVL 6

Expert Comment

by:izblank
Comment Utility
One way to speed up kselvia's solution might be this:


select name, convert(varchar,dbo.f_minrun(name),101) MinDate
from
(select distinct name from t1 ) x
0
 

Author Comment

by:brianlayden
Comment Utility
This works great but I have one last question.  Why are there null's returned?  I know for a fact that there are no null effdates in the source table.

Thanks for all your help, everybody.
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
I would not expect nulls to be returned.

return Coalesce(@mindate,@priordate)

means return the mininum determined effective date, if there was none (it was null) return the last effdate seen.  There should always be at least 1 effdate.  If you can post some data that generates nulls I will try to find out why it happens.

izblank, the GROUP BY NAME should have the same effect as select distinct. i.e. The function should only be called 6 times for 6 different people. At least that is my expirence.
0
 

Author Comment

by:brianlayden
Comment Utility
I figured it out...some of the names are more than 20 char long.

Thank you all for your help...it works perfectly.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

13 Experts available now in Live!

Get 1:1 Help Now