Craig B Craig B - 4 months ago 5
SQL Question

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')

Answer

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
Comments