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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wembleybear
Yak Posting Veteran

United Kingdom
93 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
6065 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
93 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
6065 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
93 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
30421 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  
 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.1 seconds. Powered By: Snitz Forums 2000