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

Akhter HUssain

6y1.3kSQL

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.

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

image link

https://ibb.co/6rXt7nz

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain6y ago
Accepted answer
Check below query
SELECT ID, Name,Salary,Designation,SUM(Allowance) AS Allowance,SUM(Deduction)  AS Deduction,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour FROM
(
    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
) as test
GROUP BY ID, Name,Salary,Designation,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour
displaying below output
Akhter HUssain6y ago
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'.
Akhter HUssain6y ago
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);
6. Insert into #tbl_Designation values
7. (1,'officer'),
8. (2,'Executive')
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)
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')
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)
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

Vinay Talapaneni6y ago

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