Akhter HUssain

Akhter HUssain

  • 681
  • 1.3k
  • 95.3k

Allowance and Deduction amount set in one row ,against each employee

Aug 29 2020 8:09 AM
Below is my query,I want Allowance and Deduction of each employee in one row,employee ID must not be repeated ,as you may see image Employee ID 30007 ,is being repeated.
 
  1. SELECT  (M.Emp_ID) as ID , M.EMp_Name as Name , M.EMP_Salary as Salary,D.Designation_Name as Designation,  
  2.  CASE WHEN T.DA_Type_ID = 'Allowance' THEN Sum(ASD.assign_amt) ELSE 0 END AS Allowance,  
  3.   CASE WHEN T.DA_Type_ID = 'Deduction' THEN Sum(ASD.assign_amt) ELSE 0 END AS Deduction,  
  4. isnull(M.emp_OT,0) as emp_OT  
  5. ,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour'  
  6. FROM tbl_Employee_Master M  
  7.  inner join tbl_designation D on D.Designation_ID=M.Designation_ID  
  8.  inner join tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID  
  9.  inner join tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID  
  10.  group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT  
 image link
https://ibb.co/6rXt7nz 
 

Answers (4)