Use Test
Go
Set Nocount On
/*自定義公式測試2000版V1.0 Andy 2009-7-20*/
If object_id('Expressions') Is Not Null
Drop Table Expressions
If object_id('ExpressionsMTR') Is Not Null
Drop Table ExpressionsMTR
If object_id('Salary') Is Not Null
Drop Table Salary
If object_id('Employee') Is Not Null
Drop Table Employee
If object_id('SysExpressionsMTR') Is Not Null
Drop Table SysExpressionsMTR
If object_id('SalaryItemMTR') Is Not Null
Drop Table SalaryItemMTR
Go
Create Table Employee
(
ID int Identity(1,1) Not Null,
EmployeeNo nvarchar(50) Not Null,
Name nvarchar(50) Not Null,
Sex nchar(1) Not Null,
Department int Not Null,
Position nvarchar(50) Null,
OutDutyDate datetime,
Constraint PK_Employee_ID Primary Key(ID Asc)
)
Create Table SalaryItemMTR
(
ID int Identity(1,1) Not Null,
Name nvarchar(50) Null,
EffectiveDate datetime,
ExpiryDate datetime,
Constraint PK_SalaryItemMTR_ID Primary Key(ID Asc)
)
Create Table Salary
(
ID int Identity(1,1) Not Null,
EmployeeID int Not Null,
Period datetime Not Null,
SalaryItem int Not Null,
Amount money,
Constraint PK_Salary_ID Primary Key(ID Asc),
COnstraint FK_Salary_EmployeeID Foreign Key(EmployeeiD) References Employee(ID),
COnstraint FK_Salary_SalaryItem Foreign Key(SalaryItem) References SalaryItemMTR(ID)
)
Create Table ExpressionsMTR
(
ID int Identity(1,1) Not Null,
Item int Not Null,
Computable bit Null,
Constraint PK_SysExpressionsMTR_ID Primary Key(ID Asc),
)
Create Table Expressions
(
ID int Identity(1,1) Not Null,
ExpressionsMTRID int Not Null,
Seq int ,
Definition nvarchar(2048),
EffectiveDate datetime,
ExpiryDate datetime,
Constraint PK_Expressions_ID Primary Key(ID Asc),
Constraint FK_Expressions_ExpressionsMTRID Foreign Key (ExpressionsMTRID) References ExpressionsMTR(ID)
)
GO
Insert Into Employee (EmployeeNo,Name,Sex,Department,Position,OutDutyDate)
Select N'N0001',N'張11',N'男',1,N'經理','20080125' Union All
Select N'N0002',N'李12',N'女',1,N'助理',Null Union All
Select N'N0003',N'黃22',N'男',3,N'技師',Null Union All
Select N'N0004',N'劉33',N'男',4,N'保安',Null Union All
Select N'N0005',N'黃32',N'男',6,N'廚師',Null
Insert Into SalaryItemMTR (Name,EffectiveDate,ExpiryDate)
Select N'底薪','20080101','30001231' Union All
Select N'平日加班費','20080101','30001231' Union All
Select N'周末加班費','20080101','30001231' Union All
Select N'技術津貼','20080101','30001231' Union All
Select N'崗位津貼','20080101','30001231' Union All
Select N'上月余額','20080101','30001231' Union All
Select N'應得工資','20080101','30001231' Union All
Select N'本月余額','20080101','30001231' Union All
Select N'實際工資','20080101','30001231'
Insert Into Salary (EmployeeID,Period,SalaryItem,Amount)
Select 1,'20080101',1,5000 Union All
Select 1,'20080101',2,0 Union All
Select 1,'20080101',3,0 Union All
Select 1,'20080101',4,0 Union All
Select 1,'20080101',5,2602 Union All
Select 1,'20080101',6,18.65 Union All
Select 1,'20080101',7,0 Union All
Select 1,'20080101',8,0 Union All
Select 1,'20080101',9,0 Union All
Select 2,'20080101',1,3500 Union All
Select 2,'20080101',2,300 Union All
Select 2,'20080101',3,250 Union All
Select 2,'20080101',4,0 Union All
Select 2,'20080101',5,200 Union All
Select 2,'20080101',6,6.30 Union All
Select 2,'20080101',7,0 Union All
Select 2,'20080101',8,0 Union All
Select 2,'20080101',9,0
Insert Into ExpressionsMTR (Item,Computable)
Select 1,1 Union All
Select 2,1 Union All
Select 3,1 Union All
Select 4,1 Union All
Select 5,1 Union All
Select 6,1 Union All
Select 7,1 Union All
Select 8,1 Union All
Select 9,1
Insert Into Expressions (ExpressionsMTRID,Seq,Definition,EffectiveDate,ExpiryDate)
Select 1,1,N'Isnull([1],0)','20080101','30001231' Union All
Select 2,2,N'Isnull([2],0)','20080101','30001231' Union All
Select 3,3,N'Isnull([3],0)','20080101','30001231' Union All
Select 4,4,N'Isnull([4],0)','20080101','30001231' Union All
Select 5,5,N'Isnull([5],0)','20080101','30001231' Union All
Select 6,6,N'Isnull([6],0)','20080101','30001231' Union All
Select 7,7,N'([1]+[2]+[3]+[4]+[5]+[6])','20080101','30001231' Union All
Select 8,8,N'Case When Period=Convert(char(6),OutDutyDate,112)+''01'' Then 0 Else Cast(Round([7],0,1) As int)%10+[7]-Round([7],0,1) End','20080101','30001231' Union All
Select 9,9,N'[7]-[8]','20080101','30001231'
/*
1.在MSSQL2000中使用"%"取模運算符的時候，要求被除數/除數必須是int類型，到了MSSQL2005就有很大的改進，不用轉換成int,只要是數值就可以。
2.這里使用到的Cast()隱藏一些知識，
表達式 四舍五入(Y/N)
--------------------------------------------
Cast(字段名 As int) N
Cast(直接寫的數值 As int) N
Cast(變量 As int) Y
這里為了能明確表明不要四舍五入，使用了Round()函數。
*/
Go
If object_id('uSalaryForExpressionsByPeriod') Is Not Null
Drop Proc uSalaryForExpressionsByPeriod
Go
Create Proc uSalaryForExpressionsByPeriod
(
@Period datetime
)
As
Set Nocount On
Declare @Sql nvarchar(4000),
@Columns nvarchar(4000),
@StrPeriod nvarchar(8),
@UpdateSql nvarchar(4000),
@PivotSql nvarchar(4000),
@UnpivotSql nvarchar(4000)
Set @StrPeriod=Convert(char(8),@Period,112)
If object_id('tempdb..#Salary') Is Not Null
Drop Table #Salary
Create Table #Salary(Period datetime,EmployeeID int Primary Key,EmployeeNo nvarchar(50),Name nvarchar(50),Department int,Position nvarchar(50),OutDutyDate datetime)
Select @Columns=Isnull(@Columns+',','') +Quotename(Item),
@UpdateSql=Isnull(@UpdateSql+Char(13)+Char(10),'')+'Update #Salary Set '+Quotename(a.Item)+'='+b.Definition,
@PivotSql=Isnull(@PivotSql,'')+',Sum(Case SalaryItem When '+Rtrim(a.Item)+' Then Amount Else 0 End) As '+Quotename(a.Item),
@UnpivotSql=Isnull(@UnpivotSql+ ' Union All ','')+'Select EmployeeID,'+Rtrim(a.Item)+' As SalaryItem,Convert(money,'+Quotename(a.Item)+') As Amount From #Salary'
From ExpressionsMTR As a
Inner Join Expressions As b On b.ExpressionsMTRID =a.ID
Where b.EffectiveDate<=@Period
And b.ExpiryDate>@Period
/*
注:
@PivotSql賦值過程的Sum()寫法
在MSSQL2005版本中使用Pivot方法，可以使用Max()函數，在2000版中要特別小心，這里要使用Sum()函數。因為，當工資項目出現<0的數據時候，使用Max()就錯誤了。
如果非要使用Max()，那樣需要這樣寫：
@PivotSql=Isnull(@PivotSql,'')+',Sum(Case SalaryItem When '+Rtrim(a.Item)+' Then Amount Else 0 End) As '+Quotename(a.Item),
*/
Set @Sql='Alter Table #Salary Add '+Replace(@Columns,',',' money,')+' Money'
Exec(@Sql)
Insert Into #Salary
Exec(N'
Select a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate'+@PivotSql+'
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Where a.Period='''+@strPeriod+'''
Group By a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate
')
Exec(@UpdateSql)
Exec (N'
Update a
Set a.Amount=b.Amount
From Salary As a
Inner Join ('+@UnpivotSql+') As b On b.EmployeeID=a.EmployeeID And b.SalaryItem=a.SalaryItem
Where a.Period='''+@strPeriod+'''
')
Go
--調用
Exec uSalaryForExpressionsByPeriod '20080101'
--Debug
Declare @Period datetime,
@Sql nvarchar(4000),
@PivotSql nvarchar(4000)
Set @Period='20080101'
Select @PivotSql=Isnull(@PivotSql,'')+',Sum(Case c.name When '''+Rtrim(Name)+''' Then a.Amount Else 0 End) As '+Quotename(Name)
From SalaryItemMTR
Where EffectiveDate<=@Period
And ExpiryDate>@Period
Set @Sql=(N'
Select a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate'+@PivotSql+'
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Inner Join SalaryItemMTR As c On c.ID=a.SalaryItem
Where Period=@Period
Group By a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate
')
Exec sp_executesql @Sql,N'@Period datetime',@Period
Go
Set Nocount On
/*自定義公式測試2000版V1.0 Andy 2009-7-20*/
If object_id('Expressions') Is Not Null
Drop Table Expressions
If object_id('ExpressionsMTR') Is Not Null
Drop Table ExpressionsMTR
If object_id('Salary') Is Not Null
Drop Table Salary
If object_id('Employee') Is Not Null
Drop Table Employee
If object_id('SysExpressionsMTR') Is Not Null
Drop Table SysExpressionsMTR
If object_id('SalaryItemMTR') Is Not Null
Drop Table SalaryItemMTR
Go
Create Table Employee
(
ID int Identity(1,1) Not Null,
EmployeeNo nvarchar(50) Not Null,
Name nvarchar(50) Not Null,
Sex nchar(1) Not Null,
Department int Not Null,
Position nvarchar(50) Null,
OutDutyDate datetime,
Constraint PK_Employee_ID Primary Key(ID Asc)
)
Create Table SalaryItemMTR
(
ID int Identity(1,1) Not Null,
Name nvarchar(50) Null,
EffectiveDate datetime,
ExpiryDate datetime,
Constraint PK_SalaryItemMTR_ID Primary Key(ID Asc)
)
Create Table Salary
(
ID int Identity(1,1) Not Null,
EmployeeID int Not Null,
Period datetime Not Null,
SalaryItem int Not Null,
Amount money,
Constraint PK_Salary_ID Primary Key(ID Asc),
COnstraint FK_Salary_EmployeeID Foreign Key(EmployeeiD) References Employee(ID),
COnstraint FK_Salary_SalaryItem Foreign Key(SalaryItem) References SalaryItemMTR(ID)
)
Create Table ExpressionsMTR
(
ID int Identity(1,1) Not Null,
Item int Not Null,
Computable bit Null,
Constraint PK_SysExpressionsMTR_ID Primary Key(ID Asc),
)
Create Table Expressions
(
ID int Identity(1,1) Not Null,
ExpressionsMTRID int Not Null,
Seq int ,
Definition nvarchar(2048),
EffectiveDate datetime,
ExpiryDate datetime,
Constraint PK_Expressions_ID Primary Key(ID Asc),
Constraint FK_Expressions_ExpressionsMTRID Foreign Key (ExpressionsMTRID) References ExpressionsMTR(ID)
)
GO
Insert Into Employee (EmployeeNo,Name,Sex,Department,Position,OutDutyDate)
Select N'N0001',N'張11',N'男',1,N'經理','20080125' Union All
Select N'N0002',N'李12',N'女',1,N'助理',Null Union All
Select N'N0003',N'黃22',N'男',3,N'技師',Null Union All
Select N'N0004',N'劉33',N'男',4,N'保安',Null Union All
Select N'N0005',N'黃32',N'男',6,N'廚師',Null
Insert Into SalaryItemMTR (Name,EffectiveDate,ExpiryDate)
Select N'底薪','20080101','30001231' Union All
Select N'平日加班費','20080101','30001231' Union All
Select N'周末加班費','20080101','30001231' Union All
Select N'技術津貼','20080101','30001231' Union All
Select N'崗位津貼','20080101','30001231' Union All
Select N'上月余額','20080101','30001231' Union All
Select N'應得工資','20080101','30001231' Union All
Select N'本月余額','20080101','30001231' Union All
Select N'實際工資','20080101','30001231'
Insert Into Salary (EmployeeID,Period,SalaryItem,Amount)
Select 1,'20080101',1,5000 Union All
Select 1,'20080101',2,0 Union All
Select 1,'20080101',3,0 Union All
Select 1,'20080101',4,0 Union All
Select 1,'20080101',5,2602 Union All
Select 1,'20080101',6,18.65 Union All
Select 1,'20080101',7,0 Union All
Select 1,'20080101',8,0 Union All
Select 1,'20080101',9,0 Union All
Select 2,'20080101',1,3500 Union All
Select 2,'20080101',2,300 Union All
Select 2,'20080101',3,250 Union All
Select 2,'20080101',4,0 Union All
Select 2,'20080101',5,200 Union All
Select 2,'20080101',6,6.30 Union All
Select 2,'20080101',7,0 Union All
Select 2,'20080101',8,0 Union All
Select 2,'20080101',9,0
Insert Into ExpressionsMTR (Item,Computable)
Select 1,1 Union All
Select 2,1 Union All
Select 3,1 Union All
Select 4,1 Union All
Select 5,1 Union All
Select 6,1 Union All
Select 7,1 Union All
Select 8,1 Union All
Select 9,1
Insert Into Expressions (ExpressionsMTRID,Seq,Definition,EffectiveDate,ExpiryDate)
Select 1,1,N'Isnull([1],0)','20080101','30001231' Union All
Select 2,2,N'Isnull([2],0)','20080101','30001231' Union All
Select 3,3,N'Isnull([3],0)','20080101','30001231' Union All
Select 4,4,N'Isnull([4],0)','20080101','30001231' Union All
Select 5,5,N'Isnull([5],0)','20080101','30001231' Union All
Select 6,6,N'Isnull([6],0)','20080101','30001231' Union All
Select 7,7,N'([1]+[2]+[3]+[4]+[5]+[6])','20080101','30001231' Union All
Select 8,8,N'Case When Period=Convert(char(6),OutDutyDate,112)+''01'' Then 0 Else Cast(Round([7],0,1) As int)%10+[7]-Round([7],0,1) End','20080101','30001231' Union All
Select 9,9,N'[7]-[8]','20080101','30001231'
/*
1.在MSSQL2000中使用"%"取模運算符的時候，要求被除數/除數必須是int類型，到了MSSQL2005就有很大的改進，不用轉換成int,只要是數值就可以。
2.這里使用到的Cast()隱藏一些知識，
表達式 四舍五入(Y/N)
--------------------------------------------
Cast(字段名 As int) N
Cast(直接寫的數值 As int) N
Cast(變量 As int) Y
這里為了能明確表明不要四舍五入，使用了Round()函數。
*/
Go
If object_id('uSalaryForExpressionsByPeriod') Is Not Null
Drop Proc uSalaryForExpressionsByPeriod
Go
Create Proc uSalaryForExpressionsByPeriod
(
@Period datetime
)
As
Set Nocount On
Declare @Sql nvarchar(4000),
@Columns nvarchar(4000),
@StrPeriod nvarchar(8),
@UpdateSql nvarchar(4000),
@PivotSql nvarchar(4000),
@UnpivotSql nvarchar(4000)
Set @StrPeriod=Convert(char(8),@Period,112)
If object_id('tempdb..#Salary') Is Not Null
Drop Table #Salary
Create Table #Salary(Period datetime,EmployeeID int Primary Key,EmployeeNo nvarchar(50),Name nvarchar(50),Department int,Position nvarchar(50),OutDutyDate datetime)
Select @Columns=Isnull(@Columns+',','') +Quotename(Item),
@UpdateSql=Isnull(@UpdateSql+Char(13)+Char(10),'')+'Update #Salary Set '+Quotename(a.Item)+'='+b.Definition,
@PivotSql=Isnull(@PivotSql,'')+',Sum(Case SalaryItem When '+Rtrim(a.Item)+' Then Amount Else 0 End) As '+Quotename(a.Item),
@UnpivotSql=Isnull(@UnpivotSql+ ' Union All ','')+'Select EmployeeID,'+Rtrim(a.Item)+' As SalaryItem,Convert(money,'+Quotename(a.Item)+') As Amount From #Salary'
From ExpressionsMTR As a
Inner Join Expressions As b On b.ExpressionsMTRID =a.ID
Where b.EffectiveDate<=@Period
And b.ExpiryDate>@Period
/*
注:
@PivotSql賦值過程的Sum()寫法
在MSSQL2005版本中使用Pivot方法，可以使用Max()函數，在2000版中要特別小心，這里要使用Sum()函數。因為，當工資項目出現<0的數據時候，使用Max()就錯誤了。
如果非要使用Max()，那樣需要這樣寫：
@PivotSql=Isnull(@PivotSql,'')+',Sum(Case SalaryItem When '+Rtrim(a.Item)+' Then Amount Else 0 End) As '+Quotename(a.Item),
*/
Set @Sql='Alter Table #Salary Add '+Replace(@Columns,',',' money,')+' Money'
Exec(@Sql)
Insert Into #Salary
Exec(N'
Select a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate'+@PivotSql+'
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Where a.Period='''+@strPeriod+'''
Group By a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate
')
Exec(@UpdateSql)
Exec (N'
Update a
Set a.Amount=b.Amount
From Salary As a
Inner Join ('+@UnpivotSql+') As b On b.EmployeeID=a.EmployeeID And b.SalaryItem=a.SalaryItem
Where a.Period='''+@strPeriod+'''
')
Go
--調用
Exec uSalaryForExpressionsByPeriod '20080101'
--Debug
Declare @Period datetime,
@Sql nvarchar(4000),
@PivotSql nvarchar(4000)
Set @Period='20080101'
Select @PivotSql=Isnull(@PivotSql,'')+',Sum(Case c.name When '''+Rtrim(Name)+''' Then a.Amount Else 0 End) As '+Quotename(Name)
From SalaryItemMTR
Where EffectiveDate<=@Period
And ExpiryDate>@Period
Set @Sql=(N'
Select a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate'+@PivotSql+'
From Salary As a
Inner Join Employee As b On b.ID=a.EmployeeID
Inner Join SalaryItemMTR As c On c.ID=a.SalaryItem
Where Period=@Period
Group By a.Period,a.EmployeeID,b.EmployeeNo,b.Name,b.Department,b.Position,b.OutDutyDate
')
Exec sp_executesql @Sql,N'@Period datetime',@Period