执行程序时无法获得所需的输出 [英] Can't get required output while executing procedure

问题描述

当我执行程序时，我得到的输出就像 -

When i execute the procedure what i get the output is like -

EmployeeCode  Additions      Deductions   AdditionsAmount  DeductionsAmount
M1010         ALLOWANCE                     960.00               0
M1010         BASIC SALARY                  1390.00              0
M1010         OT1                           171.35               0
M1010                        OTHERS-DED     0.00                 1800.00

所需产出 -

Required Output -

EmployeeCode  Additions      Deductions   AdditionsAmount  DeductionsAmount
M1010         ALLOWANCE      OTHERS-DED     960.00               1800.00
M1010         BASIC SALARY                  1390.00              0
M1010         OT1                           171.35               0

如有任何帮助，我们将不胜感激。



我尝试过：

Any help would be appreciated.

What I have tried:

ALTER PROC [dbo].[PROC_EMPLOYEE_PAY_SLIP]                          
@EmplopyeeCode varchar(100),
@MonthName varchar(50),
@Year Float                             
As                                                
Begin                                            
 SELECT ISNULL(HR_EmployeeMaster.em_EmplopyeeCode,'') As EmplopyeeCode,
  ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Additions,
  ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Deductions,
  ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As AdditionsAmount,
  ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As DeductionsAmount
   FROM HR_EmployeeMaster                                             
    LEFT JOIN Fa_MonthSalary ON Fa_MonthSalary.ms_EmpCode = @EmplopyeeCode                                                    
    LEFT JOIN Fa_MonthDetails ON Fa_MonthDetails.mo_id = Fa_MonthSalary.Ms_MonCode                                                  
    LEFT JOIN fa_ComponentMaster ON fa_ComponentMaster.cm_code = Fa_MonthSalary.Ms_CompCode                   
  WHERE HR_EmployeeMaster.em_EmplopyeeCode = @EmplopyeeCode AND
		(SELECT CONVERT(CHAR(3), Fa_MonthDetails.mo_name)) = @MonthName AND
		Fa_MonthDetails.mon_year = @Year  
End

推荐答案

当Deductions =OTHERS-DED时，将Additions（列）设置为ALLOWANCE并在EmployeeCode和Additions上分组。

When Deductions = "OTHERS-DED", then set Additions (column) to "ALLOWANCE" and group on EmployeeCode and Additions.

这篇关于执行程序时无法获得所需的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！