create table tVacation
(
eNo varchar(10),
eStartDate datetime,
eEndDate datetime,
eVacationKing varchar(10),
eHour int )
insert tVacation
select '1540','2005-9-26','2005-10-10','事假',120 union
select '4695','2005-9-21','2005-9-22','病假',8 CREATE PROCEDURE Temp_T(@T1 as datetime,@T2 as datetime)
as
select T.eNo, T.eStartDate,case when (select 1 from tVacation where T.eNo=tVacation.eNo and tVacation.eStartDate between @T1 and @T2)=1 then T.eStartDate else @T1 end,
T.eEndDate,
case when (select 1 from tVacation where T.eNo=tVacation.eNo and tVacation.eEndDate between @T1 and @T2)=1 then T.eEndDate else @T2 end
from tVacation T where
T.eStartDate between @T1 and @T2
or T.eEndDate between @T1 and @T2exec Temp_T '2005-09-20','2005-09-29'
(
eNo varchar(10),
eStartDate datetime,
eEndDate datetime,
eVacationKing varchar(10),
eHour int )
insert tVacation
select '1540','2005-9-26','2005-10-10','事假',120 union
select '4695','2005-9-21','2005-9-22','病假',8 CREATE PROCEDURE Temp_T(@T1 as datetime,@T2 as datetime)
as
select T.eNo, T.eStartDate,case when (select 1 from tVacation where T.eNo=tVacation.eNo and tVacation.eStartDate between @T1 and @T2)=1 then T.eStartDate else @T1 end,
T.eEndDate,
case when (select 1 from tVacation where T.eNo=tVacation.eNo and tVacation.eEndDate between @T1 and @T2)=1 then T.eEndDate else @T2 end
from tVacation T where
T.eStartDate between @T1 and @T2
or T.eEndDate between @T1 and @T2exec Temp_T '2005-09-20','2005-09-29'
as
select T.eNo, T.eStartDate,case when (select 1 from tVacation where T.eNo=tVacation.eNo and tVacation.eStartDate between @T1 and @T2)=1 then T.eStartDate else @T1 end as 时间1,
T.eEndDate,
case when (select 1 from tVacation where T.eNo=tVacation.eNo and tVacation.eEndDate between @T1 and @T2)=1 then T.eEndDate else @T2 end as 时间2
from tVacation T where
T.eStartDate between @T1 and @T2
or T.eEndDate between @T1 and @T2
set @d1='2005-09-01'
set @d2='2005-10-10'select eno,@d1 as 'estartdate',@d2 as 'eenddate',evacationkind,
ehour=sum(ehour)
from tVacation
where eStartDate<=@d1 and eEndDate>=@d2
order by eno,evacationkind
set @d1='2005-09-01'
set @d2='2005-10-10'
select * from tVacation
where (eStartDate between @d1 and @d2)or(eendDate between @d1 and @d2)