Find if any part of a date range falls in period

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find if any part of a date range falls in period

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-01-03 : 12:20:00

I'm trying to find if any part of a date range in my table between

 orig_start_rent

and

stop_rent

falls within a period I specify in two variables:

startPeriod

and

endPeriod

For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. I would appreciate any help to correct my code here:

declare @startPeriod as smalldatetime
declare @endPeriod as smalldatetime

set @startPeriod = '2013-11-01'
set @endPeriod = '2013-11-30';

select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem

Many thanks
Martyn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-03 : 13:57:07

Another way to say it (i think - correct me if I'm wrong) is if orig_start_rent is within your range OR if stop_rent is within your range then 1.
try this as your CASE statement:


       , case 
              when ((stop_rent >= @startPeriod and stop_rent <= @endPeriod) 
                     or
                     (orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod)) then 1
              when orig_start_rent < @endPeriod and stop_rent is null then 1 
              else 0 
              end [OnHire]

EDIT: added the color
EDIT2: fixed cut/paste error

Be One with the Optimizer
TG

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-01-03 : 14:52:06

TG - not quite. What I'm trying to do is find out if a particular item was on hire at all (even if only for 1 day) during the period I specified. In this case, the hire might have started on 2013-05-01 and ended on 2013-12-03 - the result would still be 1, as the item was on hire for all of November.

Only if the entire date span between orig_start_rent and stop_rent falls outside (either before or after) the period specified should the result be 0 - it was not on hire in November.

Some examples with desired results for the specified period of 2013-11-01 to 2013-11-30:

orig_start_rent = 2013-09-03 and stop_rent = 2013-11-15 result = 1
orig_start_rent = 2013-11-05 and stop_rent = 2013-11-18 result = 1
orig_start_rent = 2013-09-03 and stop_rent = 2013-12-15 result = 1
orig_start_rent = 2013-11-21 and stop_rent = NULL result = 1
orig_start_rent = 2013-09-03 and stop_rent = NULL result = 1
orig_start_rent = 2013-12-01 and stop_rent = 2013-12-04 result = 0
orig_start_rent = 2013-08-11 and stop_rent = 2013-10-31 result = 0

Many thanks
Martyn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-03 : 15:08:19

The samples helped - thanks. I see I missed the case where the rent range completely engulfed the date range. this seems to work - although there may be a more direct method - let me think a bit:


declare @startPeriod as smalldatetime
declare @endPeriod as smalldatetime

set @startPeriod = '2013-11-01'
set @endPeriod = '2013-11-30';

select 
        orig_start_rent
       , stop_rent
       , case 
              when ((stop_rent >= @startPeriod and stop_rent <= @endPeriod) 
                     or
                     (orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod)
                     or
                     (orig_start_rent <= @startPeriod and stop_rent >= @endPeriod)) then 1
              when orig_start_rent < @endPeriod and stop_rent is null then 1 
              else 0 
              end [OnHire] 
--from   deltickitem

from (
       select convert(datetime, '2013-09-03'), convert(datetime, '2013-11-15') union all
       select '2013-11-05', '2013-11-18' union all
       select '2013-09-03', '2013-12-15' union all
       select '2013-11-21', NULL union all
       select '2013-09-03', NULL union all
       select '2013-12-01', '2013-12-04' union all
       select '2013-08-11', '2013-10-31'
       ) as deltickitem (orig_start_rent, stop_rent)

OUTPUT:

orig_start_rent         stop_rent               OnHire
----------------------- ----------------------- -----------
2013-09-03 00:00:00.000 2013-11-15 00:00:00.000 1
2013-11-05 00:00:00.000 2013-11-18 00:00:00.000 1
2013-09-03 00:00:00.000 2013-12-15 00:00:00.000 1
2013-11-21 00:00:00.000 NULL                    1
2013-09-03 00:00:00.000 NULL                    1
2013-12-01 00:00:00.000 2013-12-04 00:00:00.000 0
2013-08-11 00:00:00.000 2013-10-31 00:00:00.000 0

Be One with the Optimizer
TG

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-01-03 : 15:21:31

TG - that seems to work. I ran it against my DB and compared with a couple of customers with 429 items on hire in November between them using a manual check in Excel and the numbers are the same.

If you do find a more direct method please let me know, but otherwise thank you very much for your help!

Have a great weekend.

Martyn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-03 : 15:40:35

WHERE orig_start_rent <= endPeriod AND stop_rent >= startPeriod;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources