4
Answers

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

Photo of Akhter HUssain

Akhter HUssain
4y
1k
1
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)

3
Photo of Priyanka Jain
NA 9.6k 904.8k 4y
Check below query
 
  1. SELECT ID, Name,Salary,Designation,SUM(Allowance) AS Allowance,SUM(Deduction)  AS Deduction,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour FROM   
  2. (  
  3.     SELECT  (M.Emp_ID) as ID , M.EMp_Name as Name , M.EMP_Salary as Salary,D.Designation_Name as Designation,    
  4.         CASE WHEN T.DA_Type_ID = 'Allowance' THEN Sum(ASD.assign_amt) ELSE 0 END AS Allowance,    
  5.         CASE WHEN T.DA_Type_ID = 'Deduction' THEN Sum(ASD.assign_amt) ELSE 0 END AS Deduction,    
  6.         isnull(M.emp_OT,0) as emp_OT    
  7.         ,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour'    
  8.         FROM #tbl_Employee_Master M    
  9.          inner join #tbl_designation D on D.Designation_ID=M.Designation_ID    
  10.          inner join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID    
  11.          inner join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID    
  12.          group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT    
  13.  ) as test  
  14.  GROUP BY ID, Name,Salary,Designation,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour  
displaying below output
 
 
Accepted
2
Photo of Vinay Talapaneni
NA 226 113.1k 4y
please check revised one,
 
with cte as (SELECT (M.Emp_ID) as ID , M.EMp_Name as Name , M.EMP_Salary as Salary,D.Designation_Name as Designation,
CASE WHEN T.DA_Type_ID = 'Allowance' THEN Sum(ASD.assign_amt) ELSE 0 END AS Allowance,
CASE WHEN T.DA_Type_ID = 'Deduction' THEN Sum(ASD.assign_amt) ELSE 0 END AS Deduction,
isnull(M.emp_OT,0) as emp_OT
,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour'
FROM #tbl_Employee_Master M
inner join #tbl_designation D on D.Designation_ID=M.Designation_ID
inner join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
inner join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT
)select ID , Name , Salary,Designation, STUFF((select', '+ (cast (Allowance as varchar(max))+':'+ cast(Deduction as varchar(max)))
from cte b where b.Id = a.ID
for xml path ('') ),1,1,'' ),emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour from cte a group by ID,Name,Salary,Designation,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour
1
Photo of Akhter HUssain
658 1.3k 109.5k 4y
Hi 
Vinay Talapaneni
Your provided query is giving error
 
 
Msg 207, Level 16, State 1, Line 13
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Name'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Salary'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Designation'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Name'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Salary'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Designation'.
1
Photo of Akhter HUssain
658 1.3k 109.5k 4y
  1. Create Table #tbl_Designation (Designation_ID int, Designation_Name varchar(50));  
  2. CREATE TABLE #tbl_Employee_Master (emp_ID INT,emp_Name Varchar(50),emp_salary int,Designation_ID int,emp_OT int );  
  3. CREATE TABLE #tbl_Type_allowance_Deduction (DA_ID INT,DA_Name Varchar(50), DA_TYPE_ID Varchar(50));  
  4. CREATE TABLE #tbl_assign_Allowance_Deduction (assign_ID INT,EMP_ID int ,DA_ID int, Assign_amt int,Sal_ID int);  
  5.   
  6. Insert into #tbl_Designation values  
  7. (1,'officer'),  
  8. (2,'Executive')  
  9.   
  10. Insert into #tbl_Employee_Master values  
  11. (1001,'Irfan',10000,1,20),  
  12. (1002,'Khan',10500,2,20),  
  13. (1003,'Akhter',1200,1,20)  
  14.   
  15. insert into #tbl_Type_allowance_Deduction values  
  16. (1,'Loan','Deduction'),  
  17. (2,'Medical','Allowance'),  
  18. (3,'Rent','Allowance'),  
  19. (4,'Conveyance','Allowance'),  
  20. (5,'Advance','Deduction'),  
  21. (6,'Mobile','Allowance')  
  22.   
  23. insert into #tbl_assign_Allowance_Deduction values  
  24. (111,1001,1,500,null),  
  25. (112,1001,2,500,null),  
  26. (113,1002,3,540,null),  
  27. (114,1003,4,200,null),  
  28. (115,1003,6,400,null)  
  29.   
  30. Drop table #tbl_designation  
  31. Drop table #tbl_Type_allowance_Deduction  
  32. Drop table #tbl_assign_Allowance_Deduction  
  33. Drop table #tbl_Employee_Master  
Next Recommended Forum