SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find if any part of a date range falls in period
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wembleybear
Yak Posting Veteran

United Kingdom
58 Posts

Posted - 01/03/2014 :  12:20:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/03/2014 :  13:57:07  Show Profile  Reply with Quote
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

Edited by - TG on 01/03/2014 14:44:28
Go to Top of Page

wembleybear
Yak Posting Veteran

United Kingdom
58 Posts

Posted - 01/03/2014 :  14:52:06  Show Profile  Reply with Quote
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


Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/03/2014 :  15:08:19  Show Profile  Reply with Quote
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
Go to Top of Page

wembleybear
Yak Posting Veteran

United Kingdom
58 Posts

Posted - 01/03/2014 :  15:21:31  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 01/03/2014 :  15:40:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WHERE orig_start_rent <= endPeriod AND stop_rent >= startPeriod;




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000