SQL（难，急) 本年休假享有

[一叶孤城92]

有一张表PPLVPLCY，里面有leave_policy(0,1,2..6)，Leave_type(AL,SL,OL等等一个leave_policy有多个Leave_Benefit_Code)，工作年限(0-1,1-5,5-99)，假期天数assigned_days，最大带有天数MAXIMUM
另一张表PPMONTHS中有leave_policy，入职时间，离职时间，最早参加工作日，工号
还有一张表PPLVTRAN有去年休假开始时间，休假结束时间，已用天数day_used，Leave_type


怎样根据这几张表算出今年享有的休假天数(输入员工编号和休假类型），要考虑入职日期和最早工作日期来获取此人的假期天数assigned_days，今年享有的休假天数要包含上年带入（上年带入和最大带有天数要做个比较判断）

[--小F--]

最好是连同测试数据一起给出来，自然有高手帮你解答的。

[一叶孤城92]

PPLVPLCY表
0 SL 99 30 0
0 UL 1 30 0
0 UL 99 30 0
1 AL 1 14 7
1 AL 99 14 7
1 ALB 1 5 0
PPMONTHS
0 2011-01-01 00:00:00.000 2011-01-01 00:00:00.000 0 MAHONE
1 2014-05-01 00:00:00.000 2007-11-19 00:00:00.000 0 100001
3 2010-05-01 00:00:00.000 2012-05-01 00:00:00.000 0 100002
2 2007-12-10 00:00:00.000 2007-12-10 00:00:00.000 0 100003
1 2009-12-28 00:00:00.000 2009-12-28 00:00:00.000 0 100004
2 2010-09-20 00:00:00.000 2010-09-20 00:00:00.000 0 100005
PPLVTRAN
2015-09-22 00:00:00.000 2015-09-22 00:00:00.000 1 AL 100002
2014-06-01 00:00:00.000 2014-09-30 00:00:00.000 50 SL 100001
2014-01-12 00:00:00.000 2014-01-12 00:00:00.000 1 SL 100002
2014-03-17 00:00:00.000 2014-03-17 00:00:00.000 0.5 OL 100002
2014-03-07 00:00:00.000 2014-03-07 00:00:00.000 0.5 OL 100003
2014-02-22 00:00:00.000 2014-02-22 00:00:00.000 1 SL 100003
2014-04-27 00:00:00.000 2014-04-27 00:00:00.000 0.5 AL 100001

[Tiger_Zhao]

字段都分不清，最好用下面的格式提供数据。

;WITH t(col1,col2) AS (
  SELECT '2015-09-22', '10002' UNION ALL --怎么有2015年的
  SELECT '2014-06-01', '10001'
)
SELECT *
  INTO #PPLVTRAN
  FROM t

还有最好加个统计表(年,工号,工作年限,上年带入天数,本年假期天数,已休天数)

[一叶孤城92]

PPLVPLCY表 leave_policy Leave_type 工作年限 假期天数 MAXIMUM 1 AL 1 1 7 1 AL 2 10 1 AL 99 14 7 1 ALB 1 5 0 0 SL 99 (1-99) 30 0 0 UL 1(0-1) 30 0 0 UL 99 30 0 PPMONTHS表 工作年限不一定是1和99 Leave_Policy Service_Start_Date DATE_join Last_Working_Date Staff_No 0 2011-01-01 2011-01-01 0 MAHONE 1 2014-05-01 2007-11-19 0 100001 3 2010-05-01 2012-05-01 0 100002 PPLVTRAN(获取上年数据,譬如今年是2014年,只要2013年的,之前的就不需要了) Start_date END_DATE 已用天数 Staff_No Leave_Benefit_Code 2015-09-22 2015-09-22 1 100002 AL 2014-06-01 2014-09-30 50 100001 SL 2014-01-12 2014-01-12 1 100002 SL 2014-03-17 2014-03-17 0.5 100002 OL 表中数据都是测试数据有可能不正确 怎样根据这几张表算出今年享有的休假天数(输入员工编号和休假类型），要考虑入职日期和最早工作日期来获取此人的假期天数assigned_days，今年享有的休假天数要包含上年带入（上年结余和最大带有天数要做个比较判断）

[Tiger_Zhao]

每个人必须从 工作年限 = 1 的年度开始算起。 假如只从2013年开始算，可能会有假期天数7、休假天数10，2014年的上年带入算多少？ 所以应该加统计表，避免反复从头算。

[haitao]

最好是这种方式的典型数据：

declare @p table(字段名 类型...)
declare @m table(字段名 类型...)
declare @t table(字段名 类型...)

insert @p
select x,y,...
union all select 1,2,...
...

[一叶孤城92]

/*******************************计算去年休假剩余*************************************/ DEClARE @LastYear_dayhave float-- 上年享有 DECLARE @LastYear_dayused float-- 上年已用 DECLARE @LastYear_dayjieyu float--上年结余 DECLARE @LastYear_daydairu float--上年带入 SELECT @LastYear_dayhave=Assigned_days,@LastYear_daydairu=Maximum from PPLVPLCY 这部分写错了，上年享有不是Assigned_days，要计算出来

[一叶孤城92]

新建的表CREATE TABLE [dbo].[PPLEVBAL_NEW]( [Staff_No] [char](10) NOT NULL,--员工编码 [Leave_Benefit_Code] [char](4) NOT NULL,--休假类型 [last_year_nolock] [real] NOT NULL,--上年可带入天数 [last_year_count] [real] NOT NULL,--上年结余数 [last_year_used] [real] NOT NULL,--上年已用天数 [last_year_day] [real] NOT NULL,--上年想有数 [new_year_day] [real] NOT NULL--今年享有数 ) ON [PRIMARY]

[一叶孤城92]

ALTER PROCEDURE [dbo].[p_leavepolicy] (@leave_type char(10) ,@employee_no varchar(20)) AS DECLARE @Staff_No char(10) /******************************* 日期相关计算参数 ************************************/ DECLARE @dDate_CurrYear_FirstDate DATETIME DECLARE @dDate_CurrYear_LastDate DATETIME DECLARE @dDate_LastYear_FirstDate DATETIME DECLARE @dDate_LastYear_LastDate DATETIME SET @dDate_CurrYear_FirstDate = CONVERT(DATETIME, DATENAME(YYYY,GETDATE())+'-01-01', 120) SET @dDate_CurrYear_LastDate = CONVERT(DATETIME, DATENAME(YYYY,GETDATE())+'-12-31', 120) SET @dDate_LastYear_FirstDate = DATEADD(yyyy, -1, @dDate_CurrYear_FirstDate) SET @dDate_LastYear_LastDate = DATEADD(yyyy, -1, @dDate_CurrYear_LastDate) DECLARE @iDate_CurrYear_FirstDate INT DECLARE @iDate_CurrYear_LastDate INT DECLARE @iDate_LastYear_FirstDate INT DECLARE @iDate_LastYear_LastDate INT SET @iDate_CurrYear_FirstDate = dbo.Date2Int(@dDate_CurrYear_FirstDate) SET @iDate_CurrYear_LastDate = dbo.Date2Int(@dDate_CurrYear_LastDate) SET @iDate_LastYear_FirstDate = dbo.Date2Int(@dDate_LastYear_FirstDate) SET @iDate_LastYear_LastDate = dbo.Date2Int(@dDate_LastYear_LastDate) DECLARE @CurrYear_TotalDays INT SET @CurrYear_TotalDays = @iDate_CurrYear_LastDate - @iDate_CurrYear_FirstDate + 1 /******************************* 抓取员工日期信息 ************************************/ DECLARE @iDate_Join_WORK INT --最早工作日期 DECLARE @iDate_Join_Com INT --公司入职日期 DECLARE @dDate_Join_WORK DATETIME --最早工作日期 DECLARE @dDate_Join_Com DATETIME --公司入职日期 SELECT @iDate_Join_Com=Date_Join ,@iDate_Join_WORK=Service_Start_Date FROM PPMONTHS WHERE dbo.PPMONTHS.Staff_No=@employee_no and contract_terms!='z' IF @iDate_Join_Com IS NULL OR @iDate_Join_WORK IS NULL BEGIN SELECT 0 RETURN END SET @dDate_Join_WORK = dbo.Int2Date(@iDate_Join_WORK) SET @dDate_Join_Com = dbo.Int2Date(@iDate_Join_Com) /******************************* 分析员工日期信息 ************************************/ DECLARE @iDate_start INT --休假享有的开始日期 DECLARE @iDate_end INT --休假享有的结束日期 DECLARE @iDate_Split INT --休假享有的分割日期 SET @iDate_start = @iDate_CurrYear_FirstDate SET @iDate_end = @iDate_CurrYear_LastDate SET @iDate_Split = dbo.Date2Int(CONVERT(DATETIME, DATENAME(YYYY,@dDate_CurrYear_FirstDate)+'-'+DATENAME(MM,@dDate_Join_WORK)+'-'+DATENAME(DD,@dDate_Join_WORK), 120)) IF Year(@dDate_Join_Com) = Year(GETDATE()) -- 今年入职的员工，其假期结余应进行折算 SET @iDate_start = @iDate_Join_Com IF @iDate_start > @iDate_Split SET @iDate_Split = @iDate_start DECLARE @leave_policy INT SELECT @leave_policy=leave_policy from PPMONTHS WHERE dbo.PPMONTHS.Staff_No=@employee_no and contract_terms!='z' /*******************************计算去年休假剩余*************************************/ DEClARE @LastYear_dayhave float-- 上年享有 DECLARE @LastYear_dayused float-- 上年已用 DECLARE @LastYear_dayjieyu float--上年结余 DECLARE @LastYear_daydairu float--上年带入 SELECT @LastYear_dayhave=Assigned_days,@LastYear_daydairu=Maximum from PPLVPLCY WHERE Leave_Policy=@leave_policy and Leave_Benefit_Code=@leave_type SELECT @LastYear_dayused=Total_no_of_days from PPLVTRAN WHERE Past_Year='' and Start_date between @dDate_LastYear_FirstDate and @dDate_LastYear_LastDate and End_date between @dDate_LastYear_FirstDate and @dDate_LastYear_LastDate SET @LastYear_dayjieyu=@LastYear_dayhave-@LastYear_dayused IF @LastYear_dayjieyu<@LastYear_daydairu SET @LastYear_daydairu=@LastYear_dayjieyu /*******************************累计工作年限*****************************************/ DECLARE @dDate_end DATETIME --休假享有的结束日期 SET @dDate_end = dbo.Int2Date(@iDate_start) DECLARE @total_work_years INT --累计工作年限 SET @total_work_years = DATEDIFF(yyyy, @dDate_Join_WORK, @dDate_end) IF Month(@dDate_Join_WORK)>Month(@dDate_end) OR (Month(@dDate_Join_WORK)=Month(@dDate_end) AND DAY(@dDate_Join_WORK)>DAY(@dDate_end)) SET @total_work_years = @total_work_years - 1 我自己写了一部分，希望有人能帮我把后面的补完

[Tiger_Zhao的马甲]

你的代码根本没法看，英文、拼音混用的命名 还是先解释清楚表结构和数据关系吧。

PPLVPLCY(leave_policy, Leave_type, 工作年限, 假期天数, 最大带有天数)
    PK(leave_policy, Leave_type)

是不是一个leave_policy下将不同的工作年限时段划分，标记为不同的Leave_type？

PPMONTHS(leave_policy,入职时间,离职时间,最早参加工作日,工号)
    PK(工号, 入职时间)

一个工号可以变动leave_policy吗？这个表用来查询不同时段的leave_policy的吧。

PPLVTRAN(休假开始时间,休假结束时间,day_used,Leave_type,工号)
    PK(工号, 休假开始时间)

这里的时段应该不会跨越PPMONTHS中的时段吧？ 一个人无论由于修改了变动leave_policy还是工作年限的自然增长，当前的Leave_type 会发生变化。 那么前后不同Leave_type直接的假期结余就不向后带入了吗？