( 2010-8-27 记录)员工薪资历史 -Union 实现:
1、 表结构
Salary ( id , empID , jbxz,yfxz,sfxz,jse , pid )
Perion(id,bm,year,month,sdate,edate…)
表结构大体是这样的,其中 bm 是薪资日期编码如 2010-8 ,但是不一定也可以是 108 ,无规则可言。 Year 是薪资年, month 是薪资月, sdate 和 edate 是开始和结束日期。
2. 本来打算用交叉表实现的,原本已经用交叉表实现了,不过最后未能用交叉表实现“ xxxx 小计”,只能实现的显示“小计”,最后改用 union 方式实现。
3. 实现要点:
注意 group by 中的字段及顺序,注意 order by 中的字段及顺序
4 .代码如下:
/**
统计员工的期间薪资
*/
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'应发薪资',sum(pay_res_result) N'实发薪资',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'应发薪资',sum(pay_res_result) N'实发薪资',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'应发薪资',sum(pay_res_result) N'实发薪资',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