員工薪資歷史-Union實現(xiàn)

/**
 統(tǒng)計員工的期間薪資
*/

IF EXISTS (select * from sysobjects where id = object_id('sp_count_salary') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE sp_count_salary
GO

CREATE PROCEDURE sp_count_salary 
 @P_EMP_ID INT, --員工id
 @P_SPLA_PER_ID INT, --開始期間id
 @P_EPLA_PER_ID INT --結束期間id
WITH encryption 
AS
BEGIN 
 BEGIN TRANSACTION T1

 DECLARE @V_S_YEAR INT --開始期間年份
 DECLARE @V_S_MONTH INT --開始期間月份
 DECLARE @V_E_YEAR INT --結束期間年份
 DECLARE @V_E_MONTH INT --結束期間月份
 
 select @V_S_YEAR=pla_per_year,@V_S_MONTH=pla_per_month from pla_period where pla_per_id=@P_SPLA_PER_ID
 select @V_E_YEAR=pla_per_year,@V_E_MONTH=pla_per_month from pla_period where pla_per_id=@P_EPLA_PER_ID 
 
 select (CAST(pla_per_year AS NVARCHAR(4)) + '-' + RIGHT('0' + CAST(pla_per_month AS NVARCHAR(2)), 2)) as '時間', sum(pay_res_btax) N'基本薪資',sum(pay_res_atax) N'應發(fā)薪資',sum(pay_res_result) N'實發(fā)薪資',sum(pay_res_tax) N'繳稅額'
 from pla_period pp,pay_res pr
 where pp.pla_per_id = pr.pay_res_perid
 and pay_res_empid = @P_EMP_ID
 and (pla_per_year > @V_S_YEAR or (pla_per_year = @V_S_YEAR and pla_per_month >= @V_S_MONTH))
 and (pla_per_year < @V_E_YEAR or (pla_per_year = @V_E_YEAR and pla_per_month <= @V_E_MONTH))
 group by pla_per_year,pla_per_month
 union
 (
 select cast(pla_per_year as varchar(10))+N'小計' as '時間', sum(pay_res_btax) N'基本薪資',sum(pay_res_atax) N'應發(fā)薪資',sum(pay_res_result) N'實發(fā)薪資',sum(pay_res_tax) N'繳稅額'
 from pla_period pp,pay_res pr
 where pp.pla_per_id = pr.pay_res_perid
 and pay_res_empid = @P_EMP_ID
 and (pla_per_year > @V_S_YEAR or (pla_per_year = @V_S_YEAR and pla_per_month >= @V_S_MONTH))
 and (pla_per_year < @V_E_YEAR or (pla_per_year = @V_E_YEAR and pla_per_month <= @V_E_MONTH))
 group by pla_per_year
 )
 union
 (
 select N'合計' as '時間', sum(pay_res_btax) N'基本薪資',sum(pay_res_atax) N'應發(fā)薪資',sum(pay_res_result) N'實發(fā)薪資',sum(pay_res_tax) N'繳稅額'
 from pla_period pp,pay_res pr
 where pp.pla_per_id = pr.pay_res_perid
 and pay_res_empid = @P_EMP_ID
 and (pla_per_year > @V_S_YEAR or (pla_per_year = @V_S_YEAR and pla_per_month >= @V_S_MONTH))
 and (pla_per_year < @V_E_YEAR or (pla_per_year = @V_E_YEAR and pla_per_month <= @V_E_MONTH))
 )
 order by (CAST(pla_per_year AS NVARCHAR(4)) + '-' + RIGHT('0' + CAST(pla_per_month AS NVARCHAR(2)), 2))

 
 IF @@ERROR > 0 
 BEGIN
 ROLLBACK TRANSACTION T1
 END 
 ELSE
 BEGIN
 COMMIT TRANSACTION T1
 END
END

GO

--EXEC sp_count_salary 4901,1131,1153