有一张表,名叫vacation,存储的是休假信息。表中字段主要有:userid, startdate, enddate。我现在想查出某个时间段内员工请假信息。例如
vacation:
userid startdate enddate
1 7/1/2005 7/5/2005
2 6/5/2005 7/3/2005我想查出来7/2 - 7/4几天内休假的信息,产生结果为:
userid 持续时间
1 3
2 2请问这个怎么写呢?谢谢答复
vacation:
userid startdate enddate
1 7/1/2005 7/5/2005
2 6/5/2005 7/3/2005我想查出来7/2 - 7/4几天内休假的信息,产生结果为:
userid 持续时间
1 3
2 2请问这个怎么写呢?谢谢答复
from vacation
如果不用,怎么写好呢?谢谢
from vacation
declare @b table (userid int,startdate datetime, enddate datetime)
insert into @b
select 1,'7/1/2005','7/5/2005' union all
select 2,'6/5/2005','7/3/2005'select userid ,datediff(dd,(case when startdate>'7/2/2005' then startdate else '7/2/2005' end ) ,(case when enddate>'7/4/2005'then enddate else '7/4/2005'end )) as 持续时间
from @b
declare @b table (userid int,startdate datetime, enddate datetime)
insert into @b
select 1,'7/1/2005','7/5/2005' union all
select 2,'6/5/2005','7/3/2005'select userid ,datediff(dd,(case when startdate>'7/2/2005' then startdate else '7/2/2005' end ) ,(case when enddate>'7/4/2005'then enddate else '7/4/2005'end )) as 持续时间
from @b------------------
1 3
2 2
---------------