SQL存储过程求请假单请假时间的计算思路或算法
请假单要求一天内可以请二个时间段的假（比如上午请一个小时，下午请二个小时）。第一个请假时间容易计算，关键是在计算第二个请假时间段时间时涉及第一个请假时间段的判断（第一个请假是08:30--10:30，那在第二个请假时如给出09：30--11：30,這樣如何處理)，不知如何处理，请各位讲下大概的思路，有代码更好啊。
[解决办法]
drop table test1
create table test1(name varchar(10),begin_date datetime,end_date datetime)
insert into test1
select 'aa ', '2007-03-26 08:30:00 ', '2007-03-26 10:30:00 '
create trigger it_test1 on test1
for insert
as
if exists(select *
from test1 t1,
test1 t2
where t1.begin_date > t2.begin_date
and t1.begin_date < t2.end_date)
begin
raiserror( '时间不可交叉! ',16,1)
ROLLBACK TRANSACTION
return
end
insert into test1
select 'aa ', '2007-03-26 09:30:00 ', '2007-03-26 11:30:00 '
--上面insert语句会提示错误
insert into test1
select 'aa ', '2007-03-26 11:30:00 ', '2007-03-26 12:30:00 '
--上面insert语句正确
[解决办法]
create table T(id int,[date]datetime,[From] datetime,[To] datetime)
insert into T
select 1,getdate(), '2007-04-03 08:30 ', '2007-04-03 10:30 '
union all
select 1,getdate(), '2007-04-03 09:30 ', '2007-04-03 11:30 '
select * from T
select (select sum(datediff(minute,[From],[To])) as [Minutes] from T group by ID,[date]) +
(case when (select datediff(minute,[To],isnull((select [from] from T b where ID=a.ID and [date]=a.[date] and [from]> a.[from]),0))
as [diff] from T a
where not exists(select 1 from T where ID=a.ID and [date]=a.[date] and [from] <a.[from])
)> 0 then 0
else
(select datediff(minute,[To],isnull((select [from] from T b where ID=a.ID and [date]=a.[date] and [from]> a.[from]),0))
as [diff] from T a
where not exists(select 1 from T where ID=a.ID and [date]=a.[date] and [from] <a.[from]))
end)
drop table T
--180
