我有一个表,存着员工和请假时间,数据表格式如下
name holiday_begin_date holiday_end_date
A 2013-01-16 2013-02-16
b 2013-02-05 2013-02-05
c 2013-02-15 2013-02-17
.. ............ ...........(以下数据省略)假设,现在是2月份
现在想通过SQL来判断,2月的1~28号,所有员工的休假明细
即想通过当月的日期明细来left join 休假明细.目前休假明细实现不了,请指教。谢谢
name holiday_begin_date holiday_end_date
A 2013-01-16 2013-02-16
b 2013-02-05 2013-02-05
c 2013-02-15 2013-02-17
.. ............ ...........(以下数据省略)假设,现在是2月份
现在想通过SQL来判断,2月的1~28号,所有员工的休假明细
即想通过当月的日期明细来left join 休假明细.目前休假明细实现不了,请指教。谢谢
select name,
datediff(day,case when holiday_begin_date <'2013-02-01' then '2013-01-31' else holiday_begin_date end,
case when holiday_end_date >'2013-02-28' then '2013-03-01' else holiday_end_date end)
from 表
(CASE WHEN holiday_begin_date<'2013-02-01' THEN '2013-02-01' ELSE holiday_begin_date END) holiday_begin_date,
(CASE WHEN holiday_end_date>'2013-02-28' THEN '2013-02-28' ELSE holiday_end_date END) holiday_end_date
FROM TABLE
WHERE NOT
((holiday_begin_date<'2013-02-01' AND holiday_end_date<'2013-02-01') OR (holiday_begin_date>'2013-02-28' AND holiday_end_date>'2013-02-28'))
FROM tablea t
CROSS APPLY dbo.fnc_GetEmployeeHoliday(t.Name, t.holiday_begin_date,t.holiday_end_date) AS f
ORDER BY t.Name, f.Holiday_Date
(
@begin_date datetime,
@end_date datetime
)returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select maco.date+1 as date from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
endgo
select * from dbo.generateTime('2013-2-1','2013-2-28')