我有一张有很多工具的桌子,一个是出租日期,另一个是停租日期。
我想展示所有的工具,是租用在一个给定的月份。
例如:
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我有一个变量set @startdate = 20160101和@enddate = 20160131
可以用WHERE获取1月开始的所有内容,但我需要捕获工具2,它在DEC中开始,但在1月结束。
有什么想法吗?
帮助是非常感谢的,干杯
发布于 2017-01-18 09:38:25
带我回去,但这是您想要显示的所有租用期间,包括未退还的项目。
select *
from MyTable
where on_hire < @EndDate
and (off_hire >= @StartDate or off_hire is null)对于后续行动,每个工具的总天数
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发布于 2017-01-18 09:38:50
试着像这样
SELECT * FROM TABLE1
WHERE on_hire BETWEEN @startdate AND @enddate
OR off_hire BETWEEN @startdate AND @enddate 发布于 2017-01-18 09:50:11
尝试这个查询
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'https://stackoverflow.com/questions/41715764
复制相似问题