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