MS-SQL 年假计算问题（难）

[binghuochanmian]

如题，

有 sys_user （员工表）
字段如下UserName （用户ID）
worked_time （入职时间）


OA_Leave （休假表）
字段如下
UserName （用户ID）
AppDate （申请时间）
DaysAmount （休假天数）

-------------------------------
需求描述：员工入职满一年者，有3天年假，每工作满一年增加一天年假，最高7天。每年年假必须在当年休完。
如：员工A 20010-8-17入职，则从2011-8-17 开始有三天年假，必须在2011-8-17 至 2012-8-17 之间休完。

问题：计算每个员工当前可休年假，并同时计算出当已休年假，剩下多下年假。

这个问题愁的我呀。附：计算可休年假SQL,但是再加上已休年假就头大了。

SELECT fullname AS 姓名,worked_time AS 入职日期,T.工作年份,  
(CASE WHEN 工作年份=0 THEN '0'  
WHEN 工作年份>=1 AND 工作年份<6  THEN SUM(3+工作年份-1)  
WHEN 工作年份>=6 THEN '7' END) AS 年假,  
((CONVERT(VARCHAR(4),GETDATE(),120))+SUBSTRING(worked_time,5,6)) AS 开始日期,  
(CONVERT(VARCHAR(4),(CONVERT(VARCHAR(4),GETDATE(),120)+1))+SUBSTRING(worked_time,5,6)) AS 截止日期  FROM  
(SELECT fullname,worked_time,DATEDIFF(day,worked_time,GETDATE())/365 AS 工作年份 FROM sys_user WHERE worked_time<>'' AND worked_time IS NOT NULL) T  
GROUP BY T.fullname,T.worked_time,工作年份

[飘零一叶]

占位 吃饭 ^_^

[一缕青烟]

职员表
职员ID,姓名,入职日期


特休管理
职员ID,开始日期,结束日期,天数,剩余天数


请假管理
职员ID,请假日期,假种,请假段名,小时数,备注

[飘零一叶]

create table sys_user
(
UserName nvarchar(10),
worked_time datetime
)

insert into sys_user
select 'liu',dateadd(dd,-389,getdate()) union all
select 'li',dateadd(dd,-800,getdate()) union all
select 'zhao',dateadd(dd,-350,getdate())


create table OA_Leave
(
UserName nvarchar(10),
AppDate datetime,
DaysAmount int
)


insert into OA_Leave
select 'liu',GETDATE()-10,2 union all
select 'li',GETDATE()-10,2

select a.UserName,a.worked_time,a.[Annual leave],isnull(a.[Annual leave]-sum(b.DaysAmount),0) as [Available leave]
from
(select UserName,worked_time
, case when 1.0*datediff(dd,worked_time,GETDATE())/365>=1 and 1.0*datediff(dd,worked_time,GETDATE())/365<2 then 3
when 1.0*datediff(dd,worked_time,GETDATE())/365>=2 and 1.0*datediff(dd,worked_time,GETDATE())/365<3 then 4
when 1.0*datediff(dd,worked_time,GETDATE())/365>=3 and 1.0*datediff(dd,worked_time,GETDATE())/365<4 then 5
when 1.0*datediff(dd,worked_time,GETDATE())/365>=4 and 1.0*datediff(dd,worked_time,GETDATE())/365<5 then 6
when 1.0*datediff(dd,worked_time,GETDATE())/365>=5 then 7
else 0 end as [Annual leave]
from sys_user) a left join OA_Leave b on a.UserName=b.UserName and b.AppDate>=dateadd(yy,datediff(YY,a.worked_time,GETDATE()),worked_time)
group by a.UserName,a.worked_time,a.[Annual leave]

select a.UserName,a.worked_time,a.[Annual leave],isnull(a.[Annual leave]-sum(b.DaysAmount),0) as [Available leave]
from
(select UserName,worked_time
, case when 1.0*datediff(dd,worked_time,GETDATE())/365>=1 and 1.0*datediff(dd,worked_time,GETDATE())/365<2 then 3
when 1.0*datediff(dd,worked_time,GETDATE())/365>=2 and 1.0*datediff(dd,worked_time,GETDATE())/365<3 then 4
when 1.0*datediff(dd,worked_time,GETDATE())/365>=3 and 1.0*datediff(dd,worked_time,GETDATE())/365<4 then 5
when 1.0*datediff(dd,worked_time,GETDATE())/365>=4 and 1.0*datediff(dd,worked_time,GETDATE())/365<5 then 6
when 1.0*datediff(dd,worked_time,GETDATE())/365>=5 then 7
else 0 end as [Annual leave]
from sys_user) a left join OA_Leave b on a.UserName=b.UserName and b.AppDate>=dateadd(yy,datediff(YY,a.worked_time,GETDATE()),worked_time)
group by a.UserName,a.worked_time,a.[Annual leave]

/*
UserName worked_time Annual leave Available leave
li 2009-06-09 19:47:13.520 4 2
liu 2010-07-25 19:47:13.520 3 1
zhao 2010-09-02 19:47:13.520 0 0
*/

[--小F--]

[Quote=引用 3 楼 dlut_liuq 的回复:]
create table sys_user
(
UserName nvarchar(10),
worked_time datetime
)

insert into sys_user
select 'liu',dateadd(dd,-389,getdate()) union all
select 'li',dateadd(dd,-800,getdate()) union a……
[/Quote]

这么长??佩服

[geniuswjt]

膜拜下，一看到这么多字我就不想看。。。

[飘零一叶]

[Quote=引用 4 楼 fredrickhu 的回复:]

引用 3 楼 dlut_liuq 的回复:
create table sys_user
(
UserName nvarchar(10),
worked_time datetime
)

insert into sys_user
select 'liu',dateadd(dd,-389,getdate()) union all
select 'li',dateadd(dd,-80……
[/Quote]
中间有段复制重复了

[老潘]

2005的实现方法

declare @sys_user table(
UserName varchar(6)
,worked_time datetime
);
declare @OA_Leave table(
UserName varchar(6)
,AppDate datetime
,DaysAmount int
);
insert into @sys_user
select '001', '2007-03-01' union all
select '002', '2008-09-01' union all
select '003', '2011-03-01'
;
insert into @OA_Leave
select '001', '2007-06-01',3 union all
select '001', '2011-06-01',3 union all
select '002', '2010-09-05',3 union all
select '002', '2011-07-05',1 union all
select '003', '2011-06-01',1
;
--先计算工作年数
with sys_user as
(
select UserName,worked_time
,CASE WHEN dateadd(yy,datediff(yy,worked_time,GETDATE()),worked_time)>getdate() then datediff(yy,worked_time,GETDATE())-1
      ELSE datediff(yy,worked_time,GETDATE())
 END as [工作年数]
from @sys_user
)
--在计算应修年假和当前休假的起始日和截止日
,Normal as
(
SELECT UserName,worked_time,[工作年数]
,CASE WHEN [工作年数] IS NULL THEN 0
      ELSE CASE WHEN [工作年数]+3>7 then 7 else [工作年数]+3 END
 END as [应休年假]
,dateadd(yy,[工作年数],worked_time) as [本年休假起始日]
,dateadd(yy,[工作年数]+1,worked_time) as [本年休假截止日]
from sys_user
)
--聚合
SELECT a.UserName,a.[应休年假]
,ISNULL(SUM(b.DaysAmount),0) as [已休年假]
,[应休年假]-ISNULL(SUM(b.DaysAmount),0) as [可休年假] 
from Normal a LEFT JOIN @OA_Leave b
on a.UserName=b.UserName and b.AppDate between a.[本年休假起始日] and a.[本年休假截止日]
group by a.UserName,a.[应休年假]
order by a.UserName
;
/*
UserName 应休年假    已休年假    可休年假
-------- ----------- --------    -----------
001      7           3           4
002      5           4           1
003      3           1           2
*/

[shshjun]

这个我建议逐级用视图实现，如一个计算已休年假，一个计算应有年假，最后级联拿到所有数据。方便理解和维护。
这个太长了，看一眼就不想看了。