Display all employee

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Below is data and query, i want to display all employee from #tbl_employee_Master ,but asd.sal_ID is null from #tbl_assign_Allowance_Deduction


Below is image,in which three employee is coming,but i want to display all five employe
<a href="https://ibb.co/tp9YQ3T"></a>

What I have tried:

SQL

Drop table #tbl_designation  
Drop table #tbl_Type_allowance_Deduction  
Drop table #tbl_assign_Allowance_Deduction  
Drop table #tbl_Employee_Master 


Create Table #tbl_Designation (Designation_ID int, Designation_Name varchar(50));  
CREATE TABLE #tbl_Employee_Master (emp_ID INT,emp_Name Varchar(50),emp_salary int,Designation_ID int,emp_OT int );  
CREATE TABLE #tbl_Type_allowance_Deduction (DA_ID INT,DA_Name Varchar(50), DA_TYPE_ID Varchar(50));  
CREATE TABLE #tbl_assign_Allowance_Deduction (assign_ID INT,EMP_ID int ,DA_ID int, Assign_amt int,Sal_ID int);  
  
Insert into #tbl_Designation values  
(1,'officer'),  
(2,'Executive')  
  
Insert into #tbl_Employee_Master values  
(1001,'Irfan',10000,1,20),  
(1002,'Khan',10500,2,20),  
(1003,'Akhter',1200,1,20) , 
(1004,'Ali',8000,2,20),  
(1005,'Jhain',3300,1,20) 
  
insert into #tbl_Type_allowance_Deduction values  
(1,'Loan','Deduction'),  
(2,'Medical','Allowance'),  
(3,'Rent','Allowance'),  
(4,'Conveyance','Allowance'),  
(5,'Advance','Deduction'),  
(6,'Mobile','Allowance')  
  
insert into #tbl_assign_Allowance_Deduction values  
(111,1001,1,500,111),  
(112,1001,2,500,111),  
(113,1002,3,540,111),  
(114,1003,4,200,null),  
(115,1003,6,400,null)  

 SELECT  (M.Emp_ID) , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation,
       SUM(CASE T.DA_Name  WHEN 'Loan' THEN (ASD.assign_amt)ELSE 0 END) Loan, 
        SUM(CASE T.DA_Name  WHEN 'Advance' THEN (ASD.assign_amt)ELSE 0 END) Advance,
		   SUM(CASE T.DA_Name  WHEN 'Mobile' THEN (ASD.assign_amt)ELSE 0 END) Mobile,
		   	   SUM(CASE T.DA_Name  WHEN 'Conveyance' THEN (ASD.assign_amt)ELSE 0 END) Conveyance,
			   SUM(CASE T.DA_Name  WHEN 'Medical' THEN (ASD.assign_amt)ELSE 0 END) Medical,
			      SUM(CASE T.DA_Name  WHEN 'Rent' THEN (ASD.assign_amt)ELSE 0 END) House_Rent,
isnull(M.emp_OT,0) as emp_OT
,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour',asd.Sal_ID
FROM #tbl_Employee_Master M
 inner join #tbl_designation D on D.Designation_ID=M.Designation_ID
   left   join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
   left outer join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
where asd.sal_ID is null
 group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,M.emp_OT,asd.Sal_ID

Posted 29-Aug-20 23:28pm

akhter86

Updated 30-Aug-20 23:30pm

Patrice T

v4

Add a Solution

Comments

OriginalGriff 30-Aug-20 5:34am

And?
What does it do that you didn't expect, or not do that you did?
What have you tried to fix it?
Are there any error messages?
Where are you stuck?
What help do you need?

You've asked 26 questions now, you know what we need to know from you.
Use the "Improve question" widget to edit your question and provide better information.

akhter86 30-Aug-20 5:46am

i tried below query ,many time modify
SELECT (M.Emp_ID) , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation,
SUM(CASE T.DA_Name WHEN 'Loan' THEN (ASD.assign_amt)ELSE 0 END) Loan,
SUM(CASE T.DA_Name WHEN 'Advance' THEN (ASD.assign_amt)ELSE 0 END) Advance,
SUM(CASE T.DA_Name WHEN 'Mobile' THEN (ASD.assign_amt)ELSE 0 END) Mobile,
SUM(CASE T.DA_Name WHEN 'Conveyance' THEN (ASD.assign_amt)ELSE 0 END) Conveyance,
SUM(CASE T.DA_Name WHEN 'Medical' THEN (ASD.assign_amt)ELSE 0 END) Medical,
SUM(CASE T.DA_Name WHEN 'Rent' THEN (ASD.assign_amt)ELSE 0 END) House_Rent,
isnull(M.emp_OT,0) as emp_OT
,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour',asd.Sal_ID
FROM #tbl_Employee_Master M
inner join #tbl_designation D on D.Designation_ID=M.Designation_ID
left join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
left outer join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
where asd.sal_ID is null
group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,M.emp_OT,asd.Sal_ID

OriginalGriff 30-Aug-20 6:15am

At the risk of repeating myself:

And?
What does it do that you didn't expect, or not do that you did?
What have you tried to fix it?
Are there any error messages?
Where are you stuck?
What help do you need?

You've asked 26 questions now, you know what we need to know from you.
Use the "Improve question" widget to edit your question and provide better information.

akhter86 30-Aug-20 7:49am

hi OrigingalGriff,

Currently query which i provided above,is fetching that employee which asd.sal_Id is null,

I tried to use different join,but result which is i am expecting is not coming.

THere is no any error messaage

I am stuck ,that my expecting result is not coming.

I need help to retrieve all employees ,with condition of asd.sal_id is null

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-08-30T23:30:00

akhter86 wrote:
Currently query which i provided above,is fetching that employee which asd.sal_Id is null,

I tried to use different join,but result which is i am expecting is not coming.

THere is no any error messaage

I am stuck ,that my expecting result is not coming.

I need help to retrieve all employees ,with condition of asd.sal_id is null

Take a deep look at your initial data!

SQL

insert into @tbl_assign_Allowance_Deduction values  
(111,1001,1,500,111),  
(112,1001,2,500,111),  
(113,1002,3,540,111),  
(114,1003,4,200,null),  
(115,1003,6,400,null)

EmpId=1001 and 1002 has Sal_id equal to 111. That's the reason you don't get these data in your output.

You can check it by using simple query:

SQL

SELECT  M.Emp_ID , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation, T.DA_Name, ASD.assign_amt, isnull(M.emp_OT,0) as emp_OT, asd.Sal_ID
FROM @tbl_Employee_Master M
inner join @tbl_designation D on D.Designation_ID=M.Designation_ID
left join @tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
left join @tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID

If you would like to turn DA_Name field rows into columns, you can use PIVOT - SQL Server | Microsoft Docs[^]

SQL

SELECT Emp_ID , EMp_Name , EMP_Salary, Sal_ID, emp_OT, Designation, [Loan], [Advance], [Mobile], [Conveyance], [Medical], [Rent]
	, EMP_Salary + ISNULL([Loan], 0) + ISNULL([Advance], 0) + ISNULL([Mobile], 0) + ISNULL([Conveyance], 0) + ISNULL([Medical], 0) + ISNULL([Rent], 0) AS TotalSalary
FROM (
	SELECT  M.Emp_ID , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation, T.DA_Name, ASD.assign_amt, isnull(M.emp_OT,0) as emp_OT, asd.Sal_ID
	FROM @tbl_Employee_Master M
	inner join @tbl_designation D on D.Designation_ID=M.Designation_ID
	left join @tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
	left join @tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
) DT

Result:

Emp_ID	EMp_Name	EMP_Salary	Sal_ID	emp_OT	Designation	Loan	Advance	Mobile	Conveyance	Medical	Rent	TotalSalary
1001	Irfan	10000	111	20	officer	500	NULL	NULL	NULL	500	NULL	11000
1002	Khan	10500	111	20	Executive	NULL	NULL	NULL	NULL	NULL	540	11040
1003	Akhter	1200	NULL	20	officer	NULL	NULL	400	200	NULL	NULL	1800
1004	Ali	8000	NULL	20	Executive	NULL	NULL	NULL	NULL	NULL	NULL	8000
1005	Jhain	3300	NULL	20	officer	NULL	NULL	NULL	NULL	NULL	NULL	3300

Display all employee

1 solution

Solution 2

Add your solution here

Preview 0

Display all employee

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us