CODEWITHSUNDEEP
sqloracle-database
user9634982
user9634982

Reputation: 579

Need to count the filtered employees

I wrote a query which need filter out the employee data on behalf on their employee codes. For instance, in my XYZ table i have 200 employees, i need to insert these 200 employee in ABC table, but before inserting, i need to check whether all 200 employees are existed in the system,I first filter out the employee and then insert into my ABC table.

suppose, 180 out of 200 employee matched, then i will insert 180 in the ABC table. Now i want the count 200-180=20, so i need that difference count.

I wrote a query but it fetches only the matched record, not those employee count who filters out.

Select distinct SD.EMP_code 
FROm SALARY_DETAIL_REPORT_012018 SD  /*219 Employees*/
JOIN 
(SELECT * FROM EMPLOYEE) tbl
ON tbl.EMP_CODE=to_char(SD.EMP_CODE)
WHERE SD.REFERENCE_ID like '1-%';

final output : 213 employees

I want 219-213=6 i want those 6 employees. I also tried INTERSECT but i got same result.

Select distinct to_char(SD.EMP_code) 
FROm SALARY_DETAIL_REPORT_012018 SD
WHERE SD.REFERENCE_ID like '1-%'
INTERSECT
SELECT EMP_CODE FROm EMPLOYEE;

OUTPUT

213 Employees

Kindly help me to find out the count of filtered employees

Upvotes: 0

Views: 90

Answers (2)

anjali
anjali

Reputation: 84

use except opertaor

Select distinct to_char(SD.EMP_code) 
FROM SALARY_DETAIL_REPORT_012018 SD
WHERE SD.REFERENCE_ID like '1-%'
except
SELECT EMP_CODE FROm EMPLOYEE;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use NOT EXISTS :

SELECT DISTINCT SD.EMP_code 
FROM SALARY_DETAIL_REPORT_012018 sd
WHERE NOT EXISTS (SELECT 1 FROM EMPLOYEE e WHERE e.EMP_CODE = TO_CHAR(SD.EMP_CODE)) AND
      SD.REFERENCE_ID LIKE '1-%';

Upvotes: 1

Related Questions