Is a date within multiple date ranges

Question

Post reply

Is a date within multiple date ranges

sebair

SSC Veteran

Points: 250
More actions
December 22, 2013 at 3:19 pm

#301637

Here is the data...
StartDate........EndDate..........Condition
-------------.....--------------.....-----------
01/01/2012.....03/31/2012........Yes
04/01/2012.....08/31/2012........No
09/01/2012.....12/31/2012........Yes
01/01/2013.....06/30/2013........No
07/01/2013.....09/30/2013........No
etc.
There are no gaps in the data. Each record picks up where the previous record left off.
Given a date, say 06/01/2012, I need to determine if the date falls within one of the ranges where the Condition = 'Yes'. For the life of me, I cannot imagine how to start working on this.
I know how to determine if the date is within a specific range.
If Exists (Select 1 from Table where @myDate >= StartDate and @myDate <= EndDate And Condition = 'Yes')
But how do I determine if @myDate is within any one of the date ranges?
@myDate........Returns
-----------........---------
02/01/2012.....True
05/01/2012.....False
Any ideas will be appreciated.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

You're new, so 2 things.

First, "WELCOME!" to these fine forums!

Second, have a look at the first link under "Helpful" links in my signature line below. It will tell you how to get the most from these forums just by changing the way you post your data. Since this is your first post, I've done it for you.

--=========================================================

-- Create and populate a test table.

-- This is what people mean by "Please provide the

-- DDL and some readily consumable data."

--=========================================================

--===== Create the test table

CREATE TABLE #TestTable

(

StartDate DATETIME

,EndDate DATETIME

,Condition CHAR(3)

)

;

--===== Populate the test table with data

INSERT INTO #TestTable

(StartDate, EndDate, Condition)

SELECT '01/01/2012','03/31/2012','Yes' UNION ALL

SELECT '04/01/2012','08/31/2012','No' UNION ALL

SELECT '09/01/2012','12/31/2012','Yes' UNION ALL

SELECT '01/01/2013','06/30/2013','No' UNION ALL

SELECT '07/01/2013','09/30/2013','No'

;

--===== Create a test table to hold the multiple dates.

-- This might not be a part of the solution if there's

-- a table of dates someplace else.

CREATE TABLE #MyDate

(

MyDate DATETIME

)

;

--===== Populate that table with my dates

INSERT INTO #MyDate

(MyDate)

SELECT '02/01/2012' UNION ALL

SELECT '05/01/2012'

;

With that in mind, here's one possible solution.

--===============================================================================

-- Given a set of dates, determine if each date falls in a date range that

-- has a 'Yes' value and return 'True' if it does and 'False' if it doesn't.

--===============================================================================

SELECT md.MyDate

,IsFound = CASE WHEN tt.Condition = 'Yes' THEN 'True' ELSE 'False' END

FROM #MyDate AS md

LEFT JOIN #TestTable AS tt

ON md.MyDate >= tt.StartDate

AND md.MyDate <= tt.EndDate

;

Please let us know if you have any questions on the code or the article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sebair SSC Veteran Points: 250 More actions · Answer 2

Hi Jeff. Sorry about the looks of the data. I followed your suggested link and read the article. I'll follow that technique from now on.

I didn't expect a response so fast. This looks like it might do it. I'll take it with me to work tomorrow and give it a try.

Thanks. I'll let you how it comes out.

Steve.

Tamil Vengai SSC Enthusiast Points: 123 More actions · Answer 3

try this...

Declare @mydt datetime

set @mydt='2012-02-01';

select @mydt,Condition from #TestTable

where Condition='Yes' and @mydt between StartDate and EndDate

u can refer mr.jeff post for creating #TestTable...

🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Tamil Vengai (12/23/2013)
try this...
Declare @mydt datetime
set @mydt='2012-02-01';
select @mydt,Condition from #TestTable
where Condition='Yes' and @mydt between StartDate and EndDate
u can refer mr.jeff post for creating #TestTable...
🙂

That works great but it won't produce "FALSE" returns as required. I also try to not use BETWEEN on dates just in case someone slips in a time (although I forgot to add a day and use <= EndDate on my code).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sebair SSC Veteran Points: 250 More actions · Answer 5

Thanks everyone.

Between Tamil and Jeff, I have got it working.

Again, thanks for your help.