CODEWITHSUNDEEP
sqlsql-serverjoin
chrismasters
chrismasters

Reputation: 133

Joining same table does not return expected results

I have a table with fields and data as below.

appid     determid      empid   empchargeperc 
1          14           79        1.0
1          15           71        0.42
1          16           70        0.04
1          16           71        0.41
1          16           79        0.13

What i want is based on last 2 determination id (15 and 16 in above table) i want to get all the employer ids (empids) as per below 3 conditions.

  1. get all empids which are present in old determid (15) and not in new determid (16). Output: None
  2. get all empids which are present in new determid (16) and not in old determid (15). Output: empids: 70, 79
  3. compare employer charges percentage (empchargeperc) for same employers for old and new determid and if it changed, return the employer. As per above table there is just one empid 71 for determ 15, so that would be compared to empid 71 of determid 16 and as 0.42 <> 0.41, that employer would be returned as well.

So i want to return empids 70, 71, 79. I wrote below query and i get expected output.

SELECT DISTINCT a.appid, a.empid
FROM dbo.emp a JOIN dbo.emp b ON b.appid= a.appid
WHERE a.determid IN (SELECT TOP(2) determid FROM dbo.determ 
WHERE appid = @appid ORDER BY createdate DESC) AND 
a.empchargeperc <> b.empchargeperc

But not correct output for below table. Empids i get is 47 and 81 but i expect no empids should be returned as for determids 30, empid 47's empchargeperc matches determid 45's empid 47's empchargeperc. Same for empid 81. 

appid     determid      empid   empchargeperc 
1          11           47        0.16
1          11           81        0.83
1          30           47        0.16
1          30           81        0.83
1          45           47        0.16
1          45           81        0.83

Any help would be really appreciated.

Upvotes: 0

Views: 68

Answers (2)

Hogan
Hogan

Reputation: 70513

You can do complex stuff clearly with CTEs -- below I've done step 1 and 2 using this technique as an example

WITH NEW_D AS
(
  SELECT MAX(determid) as determnd
  FROM dbo.determ 
  WHERE appid = @appid
), OLD_D AS
(
  SELECT MAX(determid) as determid
  FROM dbo.determ, NEW_D
  WHERE appid = @appid AND determid <> NEW_D.determid
), EMP_NEW AS
(
  SELECT empid
  FROM emp, NEW_D
  WHERE appid = @appid AND determid = NEW_D.determid 
), EMP_OLD AS
(
  SELECT empid
  FROM emp, OLD_D
  WHERE appid = @appid AND determid = OLD_D.determid 
)
SELECT empid
FROM EMP_OLD
WHERE empid NOT IN (SELECT empid FROM EMP_NEW)

UNION ALL

SELECT empid
FROM EMP_NEW
WHERE empid NOT IN (SELECT empid FROM EMP_OLD)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

From the description of your conditions, this sounds like aggregation with a having clause:

select empid
from (select d.*, dense_rank() over (order by determid desc) as rnk
      from determ d
     ) d
where rnk <= 2
group by empid
having min(determid) = max(determid) or
       min(empchargeperc) <> max(empchargeperc);

Upvotes: 1

Related Questions