自定义工资公式设计
目的：
工资系统中，在计算各工资项目的时候，用户能自定义公式。这里的讲述是从数据库方设计方面考虑，做简要的数据模拟和实体、存储过程的设计。
收集数据：
1.Employee员工信息数据(EmployeeID,Name,Sex,Department,Position,Joindate(到职日期),DimissionDate(离职日期)… …)
2.Salay员工工资清单(SalaryMonth(工资月份),Employee(员工),基本工资,岗位津贴,技术津贴,住宿费,上月余额,应得工资,本月余额,实得工资 … …)
分析：
这里只是作为Demo，就制作一个简单的数据分析。
1.Employee员工信息就认为一个对象实体，不做详细的数据提取筛分。
2.在Salay员工工资清单，可以把“基本工资,岗位津贴,技术津贴,住宿费 。。。”这些项提取出来，归类为SalaryItem工资项目；SalaryItem工资项目还可以再分类为“公式项”与“非公式项”。（注：“公式项”是指可以使用公式来计算非手工输入的工资项目）当然还可以根据实际的需要分类的更详细，清晰。
数据表：
根据上面的简单分析，可以设计出作Demo使用的表，
1.Emplyee:员工信息表。
2.Salary:员工工资表
3.SalaryItem:工资项目表
4.Formulary:公式表
5.SysSalaryItemTypeMTR:工资项目分类表
建表：
USE [test]
GO
/*建表脚本*/
If object_id('Salary') Is Not Null Drop Table Salary
If object_id('Formulary') Is Not Null Drop Table Formulary
If object_id('Employee') Is Not Null Drop Table Employee
If object_id('SalaryItem') Is Not Null Drop Table SalaryItem
If object_id('SysSalaryItemTypeMTR') Is Not Null Drop Table SysSalaryItemTypeMTR
Go
CREATE TABLE [SysSalaryItemTypeMTR](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_SysSalaryItemTypeMTR] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
CREATE TABLE [Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeNo] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Sex] [nchar](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Department] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Position] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[JoinDate] [datetime] NULL,
[DimissionDate] [datetime] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
CREATE TABLE [SalaryItem](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[ItemType] [smallint] NOT NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_SalaryItem] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
ALTER TABLE [SalaryItem] WITH CHECK ADD CONSTRAINT [FK_SalaryItem_SysSalaryItemTypeMTR] FOREIGN KEY([ItemType])
REFERENCES [SysSalaryItemTypeMTR] ([ID])
CREATE TABLE [Salary](
[ID] [int] Identity(1,1)NOT NULL,
[EmployeeID] [int] NOT NULL,
[SalaryMonth] [datetime] NULL,
[SalaryItemID] [smallint] NOT NULL,
[Amount] [money] NULL,
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
ALTER TABLE [Salary] WITH CHECK ADD CONSTRAINT [FK_Salary_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [Employee] ([ID])
ALTER TABLE [Salary] WITH CHECK ADD CONSTRAINT [FK_Salary_SalaryItem] FOREIGN KEY([SalaryItemID])
REFERENCES [SalaryItem] ([ID])
Create Table Formulary
(
ID int Identity(1,1) Not Null,
SalaryItemID Smallint Not Null,
Definition nvarchar(4000) Null,
Sequence smallint Null,
EffectiveDate datetime Null,
ExpiryDate datetime Null,
Constraint PK_Formulary Primary Key(ID Asc),
Constraint FK_Formulary_SalaryItem Foreign Key (SalaryItemID) References SalaryItem(ID)
)
CREATE NONCLUSTERED INDEX [IX_Employee_EmployeeNo] ON [Employee]
(
[EmployeeNo] ASC
)
CREATE NONCLUSTERED INDEX [IX_Formulary_SalaryItemID] ON [Formulary]
(
[SalaryItemID] ASC
)
CREATE NONCLUSTERED INDEX [IX_Salary_EmployeeID] ON [Salary]
(
[EmployeeID] ASC
)
CREATE NONCLUSTERED INDEX [IX_Salary_SalaryMonth_EmployeeID] ON [Salary]
(
[SalaryMonth] ASC,
[EmployeeID] ASC
)
插入测试数据：
Use test
Go
If Not Exists(Select 1 From SysSalaryItemTypeMTR)
Insert Into SysSalaryItemTypeMTR(Name)
Select N'上月项' Union All
Select N'固定项' Union All
Select N'输入项' Union All
Select N'公式项' Union All
Select N'文本项'
If Not Exists(Select 1 From SalaryItem)
Insert Into SalaryItem(ItemType,Name)
Select 2,N'基本工资' Union All
Select 3,N'其他扣款' Union All
Select 3,N'岗位津贴' Union All
Select 3,N'技术津贴' Union All
Select 3,N'住宿费' Union All
Select 1,N'上月余额' Union All
Select 4,N'应得工资' Union All
Select 4,N'本月余额' Union All
Select 4,N'实得工资'
If Not Exists(Select 1 From Formulary)
Insert Into Formulary(SalaryItemID,Definition,Sequence,EffectiveDate,ExpiryDate)
Select 1,N'Isnull([1],0)',1,'20090101','21001231' Union ALl
Select 2,N'Isnull([2],0)',2,'20090101','21001231' Union ALl
Select 3,N'Isnull([3],0)',3,'20090101','21001231' Union ALl
Select 4,N'Isnull([4],0)',4,'20090101','21001231' Union ALl
Select 5,N'Isnull([5],0)',5,'20090101','21001231' Union ALl
Select 6,N'Isnull([8],0)',6,'20090101','21001231' Union ALl
Select 7,N'Isnull([1],0)+Isnull([2],0)+Isnull([3],0)+Isnull([4],0)+Isnull([5],0)+Isnull([6],0)',7,'20090101','21001231' Union ALl
Select 8,N'Case Convert(char(6),DimissionDate,112)+''01'' When SalaryMonth Then 0 Else Isnull([7],0)%10 End ',8,'20090101','21001231' Union ALl
Select 9,N'Case Convert(char(6),DimissionDate,112)+''01'' When SalaryMonth Then Isnull([7],0) Else Isnull([7],0)-Isnull([8],0) End',9,'20090101','21001231'
If Not Exists(Select 1 From Employee)
Insert Into Employee(EmployeeNo,Name,Sex,Department,Position,JoinDate,DimissionDate)
Select N'N0001',N'A1',N'男',N'Dep1',N'Pos1','20080101',Null Union All
Select N'N0002',N'A2',N'女',N'Dep2',N'Pos2','20080101','20090514' Union All
Select N'N0003',N'A3',N'男',N'Dep3',N'Pos3','20080101',Null
If Not Exists(Select 1 From Salary)
Insert Into Salary(EmployeeID,SalaryMonth,SalaryItemID,Amount)
Select 1,'20090401',1,1000 Union All
Select 2,'20090401',1,2000 Union All
Select 3,'20090401',1,3000 Union All
Select 1,'20090401',2,-10 Union All
Select 2,'20090401',2,-20 Union All
Select 3,'20090401',2,-30 Union All
Select 1,'20090401',3,0 Union All
Select 2,'20090401',3,0 Union All
Select 3,'20090401',3,0 Union All
Select 1,'20090401',4,105 Union All
Select 2,'20090401',4,0 Union All
Select 3,'20090401',4,107 Union All
Select 1,'20090401',5,-60 Union All
Select 2,'20090401',5,-60 Union All
Select 3,'20090401',5,-60 Union All
Select 1,'20090401',6,0 Union All
Select 2,'20090401',6,0 Union All
Select 3,'20090401',6,0 Union All
Select 1,'20090401',7,0 Union All
Select 2,'20090401',7,0 Union All
Select 3,'20090401',7,0 Union All
Select 1,'20090401',8,0 Union All
Select 2,'20090401',8,0 Union All
Select 3,'20090401',8,0 Union All
Select 1,'20090401',9,0 Union All
Select 2,'20090401',9,0 Union All
Select 3,'20090401',9,0 Union All
Select 1,'20090501',1,1000 Union All
Select 2,'20090501',1,2000 Union All
Select 3,'20090501',1,3000 Union All
Select 1,'20090501',2,-25 Union All
Select 2,'20090501',2,-25 Union All
Select 3,'20090501',2,-25 Union All
Select 1,'20090501',3,20 Union All
Select 2,'20090501',3,10 Union All
Select 3,'20090501',3,10 Union All
Select 1,'20090501',4,150 Union All
Select 2,'20090501',4,20 Union All
Select 3,'20090501',4,150 Union All
Select 1,'20090501',5,-62 Union All
Select 2,'20090501',5,-62 Union All
Select 3,'20090501',5,-62 Union All
Select 1,'20090501',6,0 Union All
Select 2,'20090501',6,0 Union All
Select 3,'20090501',6,0 Union All
Select 1,'20090501',7,0 Union All
Select 2,'20090501',7,0 Union All
Select 3,'20090501',7,0 Union All
Select 1,'20090501',8,0 Union All
Select 2,'20090501',8,0 Union All
Select 3,'20090501',8,0 Union All
Select 1,'20090501',9,0 Union All
Select 2,'20090501',9,0 Union All
Select 3,'20090501',9,0
Go
GO
/*建表脚本*/
If object_id('Salary') Is Not Null Drop Table Salary
If object_id('Formulary') Is Not Null Drop Table Formulary
If object_id('Employee') Is Not Null Drop Table Employee
If object_id('SalaryItem') Is Not Null Drop Table SalaryItem
If object_id('SysSalaryItemTypeMTR') Is Not Null Drop Table SysSalaryItemTypeMTR
Go
CREATE TABLE [SysSalaryItemTypeMTR](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_SysSalaryItemTypeMTR] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
CREATE TABLE [Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeNo] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Sex] [nchar](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Department] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Position] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[JoinDate] [datetime] NULL,
[DimissionDate] [datetime] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
CREATE TABLE [SalaryItem](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[ItemType] [smallint] NOT NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_SalaryItem] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
ALTER TABLE [SalaryItem] WITH CHECK ADD CONSTRAINT [FK_SalaryItem_SysSalaryItemTypeMTR] FOREIGN KEY([ItemType])
REFERENCES [SysSalaryItemTypeMTR] ([ID])
CREATE TABLE [Salary](
[ID] [int] Identity(1,1)NOT NULL,
[EmployeeID] [int] NOT NULL,
[SalaryMonth] [datetime] NULL,
[SalaryItemID] [smallint] NOT NULL,
[Amount] [money] NULL,
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
ALTER TABLE [Salary] WITH CHECK ADD CONSTRAINT [FK_Salary_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [Employee] ([ID])
ALTER TABLE [Salary] WITH CHECK ADD CONSTRAINT [FK_Salary_SalaryItem] FOREIGN KEY([SalaryItemID])
REFERENCES [SalaryItem] ([ID])
Create Table Formulary
(
ID int Identity(1,1) Not Null,
SalaryItemID Smallint Not Null,
Definition nvarchar(4000) Null,
Sequence smallint Null,
EffectiveDate datetime Null,
ExpiryDate datetime Null,
Constraint PK_Formulary Primary Key(ID Asc),
Constraint FK_Formulary_SalaryItem Foreign Key (SalaryItemID) References SalaryItem(ID)
)
CREATE NONCLUSTERED INDEX [IX_Employee_EmployeeNo] ON [Employee]
(
[EmployeeNo] ASC
)
CREATE NONCLUSTERED INDEX [IX_Formulary_SalaryItemID] ON [Formulary]
(
[SalaryItemID] ASC
)
CREATE NONCLUSTERED INDEX [IX_Salary_EmployeeID] ON [Salary]
(
[EmployeeID] ASC
)
CREATE NONCLUSTERED INDEX [IX_Salary_SalaryMonth_EmployeeID] ON [Salary]
(
[SalaryMonth] ASC,
[EmployeeID] ASC
)
插入测试数据：
Use test
Go
If Not Exists(Select 1 From SysSalaryItemTypeMTR)
Insert Into SysSalaryItemTypeMTR(Name)
Select N'上月项' Union All
Select N'固定项' Union All
Select N'输入项' Union All
Select N'公式项' Union All
Select N'文本项'
If Not Exists(Select 1 From SalaryItem)
Insert Into SalaryItem(ItemType,Name)
Select 2,N'基本工资' Union All
Select 3,N'其他扣款' Union All
Select 3,N'岗位津贴' Union All
Select 3,N'技术津贴' Union All
Select 3,N'住宿费' Union All
Select 1,N'上月余额' Union All
Select 4,N'应得工资' Union All
Select 4,N'本月余额' Union All
Select 4,N'实得工资'
If Not Exists(Select 1 From Formulary)
Insert Into Formulary(SalaryItemID,Definition,Sequence,EffectiveDate,ExpiryDate)
Select 1,N'Isnull([1],0)',1,'20090101','21001231' Union ALl
Select 2,N'Isnull([2],0)',2,'20090101','21001231' Union ALl
Select 3,N'Isnull([3],0)',3,'20090101','21001231' Union ALl
Select 4,N'Isnull([4],0)',4,'20090101','21001231' Union ALl
Select 5,N'Isnull([5],0)',5,'20090101','21001231' Union ALl
Select 6,N'Isnull([8],0)',6,'20090101','21001231' Union ALl
Select 7,N'Isnull([1],0)+Isnull([2],0)+Isnull([3],0)+Isnull([4],0)+Isnull([5],0)+Isnull([6],0)',7,'20090101','21001231' Union ALl
Select 8,N'Case Convert(char(6),DimissionDate,112)+''01'' When SalaryMonth Then 0 Else Isnull([7],0)%10 End ',8,'20090101','21001231' Union ALl
Select 9,N'Case Convert(char(6),DimissionDate,112)+''01'' When SalaryMonth Then Isnull([7],0) Else Isnull([7],0)-Isnull([8],0) End',9,'20090101','21001231'
If Not Exists(Select 1 From Employee)
Insert Into Employee(EmployeeNo,Name,Sex,Department,Position,JoinDate,DimissionDate)
Select N'N0001',N'A1',N'男',N'Dep1',N'Pos1','20080101',Null Union All
Select N'N0002',N'A2',N'女',N'Dep2',N'Pos2','20080101','20090514' Union All
Select N'N0003',N'A3',N'男',N'Dep3',N'Pos3','20080101',Null
If Not Exists(Select 1 From Salary)
Insert Into Salary(EmployeeID,SalaryMonth,SalaryItemID,Amount)
Select 1,'20090401',1,1000 Union All
Select 2,'20090401',1,2000 Union All
Select 3,'20090401',1,3000 Union All
Select 1,'20090401',2,-10 Union All
Select 2,'20090401',2,-20 Union All
Select 3,'20090401',2,-30 Union All
Select 1,'20090401',3,0 Union All
Select 2,'20090401',3,0 Union All
Select 3,'20090401',3,0 Union All
Select 1,'20090401',4,105 Union All
Select 2,'20090401',4,0 Union All
Select 3,'20090401',4,107 Union All
Select 1,'20090401',5,-60 Union All
Select 2,'20090401',5,-60 Union All
Select 3,'20090401',5,-60 Union All
Select 1,'20090401',6,0 Union All
Select 2,'20090401',6,0 Union All
Select 3,'20090401',6,0 Union All
Select 1,'20090401',7,0 Union All
Select 2,'20090401',7,0 Union All
Select 3,'20090401',7,0 Union All
Select 1,'20090401',8,0 Union All
Select 2,'20090401',8,0 Union All
Select 3,'20090401',8,0 Union All
Select 1,'20090401',9,0 Union All
Select 2,'20090401',9,0 Union All
Select 3,'20090401',9,0 Union All
Select 1,'20090501',1,1000 Union All
Select 2,'20090501',1,2000 Union All
Select 3,'20090501',1,3000 Union All
Select 1,'20090501',2,-25 Union All
Select 2,'20090501',2,-25 Union All
Select 3,'20090501',2,-25 Union All
Select 1,'20090501',3,20 Union All
Select 2,'20090501',3,10 Union All
Select 3,'20090501',3,10 Union All
Select 1,'20090501',4,150 Union All
Select 2,'20090501',4,20 Union All
Select 3,'20090501',4,150 Union All
Select 1,'20090501',5,-62 Union All
Select 2,'20090501',5,-62 Union All
Select 3,'20090501',5,-62 Union All
Select 1,'20090501',6,0 Union All
Select 2,'20090501',6,0 Union All
Select 3,'20090501',6,0 Union All
Select 1,'20090501',7,0 Union All
Select 2,'20090501',7,0 Union All
Select 3,'20090501',7,0 Union All
Select 1,'20090501',8,0 Union All
Select 2,'20090501',8,0 Union All
Select 3,'20090501',8,0 Union All
Select 1,'20090501',9,0 Union All
Select 2,'20090501',9,0 Union All
Select 3,'20090501',9,0
Go
建测试存储过程：
Use test
GO
If object_id('up_SalarySUM') Is Not Null
Drop Proc up_SalarySUM
Go
Create Proc up_SalarySUM
(
@SalaryMonth datetime
)
As
Set Nocount On
Declare @SalaryItemID nvarchar(50),
@ItemType smallint,
@Sql nvarchar(4000),
@Definition nvarchar(4000),
@Cols nvarchar(4000)
If object_id('tempdb..#Salary') Is Not Null
Drop Table #Salary
Create Table #Salary(EmployeeID int,DimissionDate datetime,SalaryMonth datetime)
Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(a.ID))
From SalaryItem As a
Inner Join Formulary As b On b.SalaryItemID=a.ID
Where b.EffectiveDate<=@SalaryMonth
And b.ExpiryDate>@SalaryMonth
Set @Sql=N'Alter Table #Salary Add '+Replace(@Cols,',',' money,')+' money'
Exec sp_executesql @Sql
Set @Sql=N'
;With CTE_Salary As
(
Select a.EmployeeID,b.DimissionDate,SalaryMonth,a.SalaryItemID,a.Amount
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Where SalaryMonth In(@SalaryMonth,Dateadd(month,-1,@SalaryMonth))
)
Insert Into #Salary
Select *
From CTE_Salary
Pivot(Max(Amount) For SalaryItemID In('+@Cols+')) As b
'
Exec sp_executesql @Sql,N'@SalaryMonth datetime',@SalaryMonth
Declare Cur_SalaryItem Cursor For
Select Quotename(a.ID) As ID,a.ItemType,b.Definition
From SalaryItem As a
Inner Join Formulary As b On b.SalaryItemID=a.ID
Where b.EffectiveDate<=@SalaryMonth
And b.ExpiryDate>@SalaryMonth
And a.ItemType In(1,4)
Order By b.Sequence
For Read Only
Open Cur_SalaryItem
Fetch Next From Cur_SalaryItem Into @SalaryItemID,@ItemType,@Definition
While @@Fetch_status=0
Begin
If @ItemType=4 --本月可计算的工资项
Set @sql='Update #Salary Set '+@SalaryItemID+'='+@Definition +' Where SalaryMonth=@SalaryMonth'
Else --计算上月工资项
Set @sql='Update a Set a.'+@SalaryItemID+'=(Select '+@Definition +' From #Salary Where EmployeeID=a.EmployeeID And SalaryMonth=Dateadd(Month,-1,a.SalaryMonth)) From #Salary As a Where SalaryMonth=@SalaryMonth'
Exec sp_executesql @sql,N'@SalaryMonth datetime',@SalaryMonth
Fetch Next From Cur_SalaryItem Into @SalaryItemID,@ItemType,@Definition
End
Close Cur_SalaryItem
Deallocate Cur_SalaryItem
Set @sql=(N'
;With CTE_Salary As
(
Select * From #Salary Unpivot(Amount For SalaryItemID In('+@Cols+')) As up Where SalaryMonth=@SalaryMonth
)
Update a
Set a.Amount=b.Amount
From Salary As a
Inner Join CTE_Salary As b On b.SalaryMonth=a.SalaryMonth
And b.EmployeeID=a.EmployeeID
And b.SalaryItemID=a.SalaryItemID
')
Exec sp_executesql @sql,N'@SalaryMonth datetime',@SalaryMonth
Drop Table #Salary
Go
GO
If object_id('up_SalarySUM') Is Not Null
Drop Proc up_SalarySUM
Go
Create Proc up_SalarySUM
(
@SalaryMonth datetime
)
As
Set Nocount On
Declare @SalaryItemID nvarchar(50),
@ItemType smallint,
@Sql nvarchar(4000),
@Definition nvarchar(4000),
@Cols nvarchar(4000)
If object_id('tempdb..#Salary') Is Not Null
Drop Table #Salary
Create Table #Salary(EmployeeID int,DimissionDate datetime,SalaryMonth datetime)
Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(a.ID))
From SalaryItem As a
Inner Join Formulary As b On b.SalaryItemID=a.ID
Where b.EffectiveDate<=@SalaryMonth
And b.ExpiryDate>@SalaryMonth
Set @Sql=N'Alter Table #Salary Add '+Replace(@Cols,',',' money,')+' money'
Exec sp_executesql @Sql
Set @Sql=N'
;With CTE_Salary As
(
Select a.EmployeeID,b.DimissionDate,SalaryMonth,a.SalaryItemID,a.Amount
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Where SalaryMonth In(@SalaryMonth,Dateadd(month,-1,@SalaryMonth))
)
Insert Into #Salary
Select *
From CTE_Salary
Pivot(Max(Amount) For SalaryItemID In('+@Cols+')) As b
'
Exec sp_executesql @Sql,N'@SalaryMonth datetime',@SalaryMonth
Declare Cur_SalaryItem Cursor For
Select Quotename(a.ID) As ID,a.ItemType,b.Definition
From SalaryItem As a
Inner Join Formulary As b On b.SalaryItemID=a.ID
Where b.EffectiveDate<=@SalaryMonth
And b.ExpiryDate>@SalaryMonth
And a.ItemType In(1,4)
Order By b.Sequence
For Read Only
Open Cur_SalaryItem
Fetch Next From Cur_SalaryItem Into @SalaryItemID,@ItemType,@Definition
While @@Fetch_status=0
Begin
If @ItemType=4 --本月可计算的工资项
Set @sql='Update #Salary Set '+@SalaryItemID+'='+@Definition +' Where SalaryMonth=@SalaryMonth'
Else --计算上月工资项
Set @sql='Update a Set a.'+@SalaryItemID+'=(Select '+@Definition +' From #Salary Where EmployeeID=a.EmployeeID And SalaryMonth=Dateadd(Month,-1,a.SalaryMonth)) From #Salary As a Where SalaryMonth=@SalaryMonth'
Exec sp_executesql @sql,N'@SalaryMonth datetime',@SalaryMonth
Fetch Next From Cur_SalaryItem Into @SalaryItemID,@ItemType,@Definition
End
Close Cur_SalaryItem
Deallocate Cur_SalaryItem
Set @sql=(N'
;With CTE_Salary As
(
Select * From #Salary Unpivot(Amount For SalaryItemID In('+@Cols+')) As up Where SalaryMonth=@SalaryMonth
)
Update a
Set a.Amount=b.Amount
From Salary As a
Inner Join CTE_Salary As b On b.SalaryMonth=a.SalaryMonth
And b.EmployeeID=a.EmployeeID
And b.SalaryItemID=a.SalaryItemID
')
Exec sp_executesql @sql,N'@SalaryMonth datetime',@SalaryMonth
Drop Table #Salary
Go
--执行工资计算
Exec up_SalarySUM '20090401'
Exec up_SalarySUM '20090501'
Go
Exec up_SalarySUM '20090501'
Go
---查看计算结果
Declare @Cols nvarchar(4000),
@SalaryMonth datetime,
@Sql nvarchar(4000)
Set @SalaryMonth='20090501'
Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(a.Name))
From SalaryItem As a
Inner Join Formulary As b On b.SalaryItemID=a.ID
Where b.EffectiveDate<=@SalaryMonth
And b.ExpiryDate>@SalaryMonth
Set @Sql=N'
;With CTE_Salary As
(
Select a.EmployeeID,b.DimissionDate,SalaryMonth,c.Name As SalaryItem,a.Amount
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Inner Join SalaryItem As c On c.ID=a.SalaryItemID
Where SalaryMonth=@SalaryMonth
)
Select *
From CTE_Salary
Pivot(Max(Amount) For SalaryItem In('+@Cols+')) As b
'
Exec sp_executesql @Sql,N'@SalaryMonth datetime',@SalaryMonth
Go
@SalaryMonth datetime,
@Sql nvarchar(4000)
Set @SalaryMonth='20090501'
Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(a.Name))
From SalaryItem As a
Inner Join Formulary As b On b.SalaryItemID=a.ID
Where b.EffectiveDate<=@SalaryMonth
And b.ExpiryDate>@SalaryMonth
Set @Sql=N'
;With CTE_Salary As
(
Select a.EmployeeID,b.DimissionDate,SalaryMonth,c.Name As SalaryItem,a.Amount
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Inner Join SalaryItem As c On c.ID=a.SalaryItemID
Where SalaryMonth=@SalaryMonth
)
Select *
From CTE_Salary
Pivot(Max(Amount) For SalaryItem In('+@Cols+')) As b
'
Exec sp_executesql @Sql,N'@SalaryMonth datetime',@SalaryMonth
Go
Demo效果如图：
