Solved

Finding Lowest Continuous Date in SQL

Posted on 2004-10-08
13
820 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 6

Assisted Solution

by:izblank
izblank earned 20 total points
ID: 12262156
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
ID: 12262327
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
ID: 12263019
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 12264332
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
ID: 12284400
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
ID: 12284469
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
 

Author Comment

by:brianlayden
ID: 12288026
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
ID: 12305259
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
ID: 12305282
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
ID: 12309378
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
ID: 12309817
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
ID: 12310081
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
ID: 12310171
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

724 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