WITH AS 同时定义多个CTE实现年假查询

CREATE view [dbo].[hr_vw_YearHoliday]
as
--工龄年数
with tbl_emp as
(
  select *
    ,datediff(month,indate,getdate()) *1.0/12 as yr_qty /***/
    ,left(convert(char(10),getdate(),120),4)+right(convert(char(10),indate,120),6) yr
  from employee
)
--年假天数及日期区间
, tbl_hol as (
  select empid
    ,convert(char(10),indate,120) indate
    ,case 
       when yr_qty>=1 then 5+ case when cast(yr_qty as int)-1 >5 then 5 else cast(yr_qty as int)-1 end
       else 0 
     end holday /***/
    ,case when yr>getdate() then case when dateadd(year,-1,yr)<indate then indate else dateadd(year,-1,yr) end else yr end dt1
    ,case when yr>getdate() then yr else dateadd(year,1,yr) end dt2
  from tbl_emp a 
)
--已休年假天数
,tbl_rest as (
  select b.empid
    ,count(distinct left(YearMonth,4)+'-'+right(YearMonth,2)+'-'+right(dt,2)) days
    ,min(left(YearMonth,4)+'-'+right(YearMonth,2)+'-'+right(dt,2)) startdt
    ,max(left(YearMonth,4)+'-'+right(YearMonth,2)+'-'+right(dt,2)) enddt
  from dbo.schedule(nolock) unpivot (shiftid for dt in (D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,  
          D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31 
       )) b,tbl_hol t
  where b.empid=t.empid
    and b.shiftid='HOL' /***/
    and cast(left(YearMonth,4)+'-'+right(YearMonth,2)+'-'+right(dt,2) as datetime) between t.dt1 and t.dt2
  group by b.empid
)
--显示结果
select h.*,isnull(rt.days,0) restday,h.holday-isnull(rt.days,0) as leftqty,cast(isnull(rt.startdt+'/'+rt.enddt,'-') as varchar(500)) ht
from tbl_hol h
  left outer join tbl_rest rt on h.empid=rt.empid
where 1=1
GO