CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Andy00001
Andy00001

Reputation: 87

SQL Show all items that are on hire before and up until a certain date

I have a table with a number of tools an on-hire date and an off-hire date.

I want to show all tools that are on hire in a given month.

eg.

tool      on_hire    off_hire
tool 1    02/01/2016 15/01/2016
tool 2    16/12/2015 16/01/2016
tool 3    05/01/2016 20/02/2016

I have a variable set @startdate = 20160101 and @enddate = 20160131

It is possible to get everything that started in JAN with a WHERE but I need to capture tool 2 also which started in DEC but ended in JAN

Any ideas?

Help is much appreciated, cheers

Upvotes: 1

Views: 164

Answers (5)

JohnHC
JohnHC

Reputation: 11205

Takes me back, but this is what you want to show EVERYTHING on hire in the period, including non-returned items

select *
from MyTable
where on_hire < @EndDate
and (off_hire >= @StartDate or off_hire is null)

For the follow up, total number of days for each tool

with CTE as
(
    select *
    from MyTable
    where on_hire < @EndDate
    and (off_hire >= @StartDate or off_hire is null)
)
select Tool,
       sum(datediff(dd,
                    case
                      when off_hire > @EndDate then @EndDate
                      when off_hire is null then @EndDate
                      else off_hire
                    end,
                    case
                      when on_hire < @StartDate then @StartDate
                      else on_hire
                    end)) as DaysOnHire
from CTE
froup by Tool

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Two lines x1-x2 and y1-y2(date ranges etc) intersect when:

x1 <= y2 and y1 <= x2

So:

select * from table
where on_hire <= @enddate and @startdate <= off_hire

Upvotes: 0

Nolan Shang
Nolan Shang

Reputation: 2328

It should be three options:

1.     |______| --hire time
    s|—————|e

2.     |______| --hire time
           s|—————|e

3.     |______|--hire time
    s|—————————————|e


4.     |______|--hire time
        s|———|e    --included in 2 and 3

    DECLARE @startdate date = '01/01/2016', @enddate DATE = '01/31/2016'
    ;WITH tb(tool,on_hire,off_hire)AS(
        SELECT 'tool 1','01/02/2016','01/15/2016'  UNION                            
        SELECT 'tool 2','12/16/2015','01/16/2016'  UNION    
        SELECT 'tool 3','01/05/2016','02/20/2016'
    )
    SELECT * FROM tb
    WHERE 
    DATEDIFF(d,tb.on_hire,@startdate)>0 AND DATEDIFF(d,@startdate,tb.off_hire)>0
    OR DATEDIFF(d,tb.on_hire,@enddate)>0 AND DATEDIFF(d,@enddate,tb.off_hire)>0
    OR DATEDIFF(d,tb.on_hire,@startdate)>0 AND DATEDIFF(d,tb.off_hire,@enddate)>0

Upvotes: -1

Mahesh.K
Mahesh.K

Reputation: 901

Try this query

create table #yourtable (tools varchar(50),on_hire datetime,off_hire datetime)

insert into #yourtable

select 'tool1'  ,'2016-01-02'  ,'2016-01-15' 
union all
select 'tool2'  ,  '2015-12-16' ,'2016-01-16'
union all
select 'tool3' ,   '2016-01-05', '2016-02-20'

select * from #yourtable
where datepart(mm,on_hire)='01' and tools<>'tool2'
union all
select * from #yourtable
where datepart(mm,on_hire)='12' and datepart(mm,off_hire)='01' and tools='tool2'

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Try like this

SELECT * FROM TABLE1 
WHERE on_hire BETWEEN @startdate AND @enddate 
OR off_hire BETWEEN @startdate AND @enddate

Upvotes: 1

Related Questions