Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Finding Lowest Continuous Date in SQL

Posted on 2004-10-08
Medium Priority
829 Views
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
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
• 5
• 4
• 2
• +2

LVL 6

Assisted Solution

izblank earned 80 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

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

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

LVL 43

Expert Comment

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

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

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

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

kselvia earned 540 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

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

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

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

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

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

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll