CODEWITHSUNDEEP
sqloracle10g
Craig B
Craig B

Reputation: 47

SQL: Return only rows with changes in a field

I've tried a lot of google-fu, but can't quite find the solution I'm looking for. Most are just too advanced for my scenario. I'm also a newb with SQL, so I apologize for the newbiness of this question.

This is for Oracle 10g.

PS_JOB employees have a unique EMPLID, but their FILE_NBR can change. I need to return the EMPLID of each EE that has a FILE_NBR change. This is a small part of a larger query that joins PS_JOB with several other tables, so please be specific where the solution should be put: in the main select statement, the join, or with the other where clauses.

For a simple table example of PS_JOB

EMPLID      FILE_NBR     Action    Date
0005         12345        Hire     01/01/2013
0005         67890        term     04/05/2015
0006         55555        Hire     02/05/2014
0006         55555        term     04/15/2015

I want to return EMPLID 0005 since it has a FILE_NBR change

ADDED EDIT: I wanted to avoid doing that as the purpose of the entire query is a bit different. The purpose of the entire query is to return EEs that have a value in b.rehire_dt, do NOT have a value of REH in a.ACTION_REASON, and DID have an a.FILE_NBR change 

Select a.paygroup, a.EMPLID, a.FILE_NBR, c.name, e.TLM_STATUS, b.rehire_dt  as "Emplmnt_Rltd_Dtes-Rehire Date",
CASE 
WHEN a.ACTION_REASON = 'REH' THEN 'Y'
ELSE 'N'
END -- confirms if EE is true rehire
FROM PS_EMPLOYMENT b --no effective dated rows

 right outer join SYSADM.PS_JOB a
 on a.emplid = b.emplid

  right outer join SYSADM.PS_PERSONAL_DATA c -- no effective dated rows
on a.emplid = c.emplid

  right outer join SYSADM.PS_SMS_SUBSCRB_TBL d
on a.paygroup = d.SUBSCRIBER_ID

  right outer join PORTAL.PS_TS_EMPL_TLM_STATUS e
 on a.emplid = e.emplid

  where

 b.rehire_dt IS NOT NULL
   --and a.EFFDT = (select max (a2.effdt) from SYSADM.PS_JOB a2 where     
  a.EMPLID = a2.EMPLID)
   and d.EFFDT = (select max (d2.effdt) from PS_SMS_SUBSCRB_TBL d2 where             
  d.SUBSCRIBER_ID = d2.SUBSCRIBER_ID)
  and e.EFFDT = (select max (e2.effdt) from PORTAL.PS_TS_EMPL_TLM_STATUS e2     
  where e.emplid = e2.EMPLID)
   and d.EFF_STATUS <> 'A'
--and a.action <> 'REH'
--and a.ACTION_REASON <> 'REH'
  --and b.rehire_dt = a.EFFDT 
  --and b.emplid = '50731/246'
  order by a.paygroup, a.emplid

Thanks for your help!

Kind Regards, Craig

SOLUTION: Thanks to @Adam_Martin I pretty much just started from scratch and used Adam's Case code in the select statement

SELECT distinct JOB.EMPLID, JOB.PAYGROUP, PER.NAME, EMP.REHIRE_DT as "Empl_Rlated_Dates_Rehire_Date", SUB.EFF_STATUS as "Paygroup Status",TLM.TLM_STATUS,
CASE 
  WHEN JOB.ACTION_REASON = 'REH' THEN 'Y'
  ELSE 'N'
END as "True Rehire?", -- confirms if EE is true rehire

CASE -------------------------------Adam's code
  WHEN EXISTS(SELECT 1
            FROM PS_JOB otherJobs
           WHERE otherJobs.EMPLID = JOB.EMPLID
             AND otherJobs.FILE_NBR <> JOB.FILE_NBR) THEN 'Y'
ELSE 'N'
END as "Had FN Change?"

FROM SYSADM.PS_EMPLOYMENT EMP

 INNER JOIN SYSADM.PS_JOB JOB
   ON EMP.EMPLID = JOB.EMPLID

 INNER JOIN SYSADM.PS_PERSONAL_DATA PER -- no effective dated rows
  on JOB.emplid = PER.emplid

  INNER JOIN SYSADM.PS_SMS_SUBSCRB_TBL SUB
   on JOB.paygroup = SUB.SUBSCRIBER_ID

  INNER JOIN PORTAL.PS_TS_EMPL_TLM_STATUS TLM
   on JOB.emplid = TLM.emplid

WHERE
JOB.EMPLID = '50731/246'
and EMP.REHIRE_DT IS NOT NULL
and JOB.EFFDT = (select max (JOB2.effdt) from SYSADM.PS_JOB JOB2 where     
JOB.EMPLID = JOB2.EMPLID)
and SUB.EFFDT = (select max (SUB2.effdt) from PS_SMS_SUBSCRB_TBL SUB2 where   
SUB.SUBSCRIBER_ID = SUB2.SUBSCRIBER_ID and SUB2.EFF_STATUS = 'A')

Upvotes: 0

Views: 96

Answers (1)

Adam Martin
Adam Martin

Reputation: 1218

Some remarks; most people use LEFT JOINs instead of RIGHT, because they find them easier to read. It may be good practice to get in that habit. Also, use a consistent spacing style so that it's easy to read what you're doing. I align my keywords on one side and my query info on the other.

This query will give you all EMPLID where there are at least two different FILE_NBR. 

  SELECT PS_JOB.EMPLID
    FROM PS_JOB
GROUP BY PS_JOB.EMPLID
  HAVING MIN(PS_JOB.FILE_NBR) <> MAX(PS_JOB.FILE_NBR)

However, I'm not sure how you want it integrated into your query, that depends on what you want. If you want to only select entries with changes, you'll need to add something like 

AND EXISTS(SELECT 1
             FROM PS_JOB otherJobs
            WHERE otherJobs.EMPLID = a.EMPLID
              AND otherJobs.FILE_NBR <> a.FILE_NBR)

If you just want it in your select, you can simply put that exists in a case statement, indicating the existence however you want.

For example: 

CASE
  WHEN EXISTS(SELECT 1
                FROM PS_JOB otherJobs
               WHERE otherJobs.EMPLID = a.EMPLID
                 AND otherJobs.FILE_NBR <> a.FILE_NBR) THEN 'Y'
  ELSE 'N'
END

Upvotes: 1

Related Questions