Hi guys,
I am stuck on what seems like a simple request:
Find all employees who have ever worked in the agency '00401'. Out of that set, also show their current agency number if their status is active.
It is the current employer part that is throwing me off. The way to tell if they are current is that they would have a service date >= '2014-01-31'
Here is the first base query which gets me everyone with '00401'.
I'm pretty much stuck after that. lol If I add the condition service date >= '2014-01-31'
then it will give me only people who have a recent service in the agency '00401'. But I want to see any agency number as long as service date >= '2014-01-31'.
I tried doing something like this, but it gave me multiple records for each SSN.
Can anyone help? If I need to provide sample table data, just let me know. Thanks in advance!
I am stuck on what seems like a simple request:
Find all employees who have ever worked in the agency '00401'. Out of that set, also show their current agency number if their status is active.
It is the current employer part that is throwing me off. The way to tell if they are current is that they would have a service date >= '2014-01-31'
Here is the first base query which gets me everyone with '00401'.
Code:
SELECT distinct A.MBR_SSN_NBR,
B.MBR_STAT_CD
FROM DSNP.PR01_T_MBR_HIST A,
DSNP.PR01_T_MBR_SYS B
WHERE A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND A.AGTY_ID_CD = '00401'
I'm pretty much stuck after that. lol If I add the condition service date >= '2014-01-31'
then it will give me only people who have a recent service in the agency '00401'. But I want to see any agency number as long as service date >= '2014-01-31'.
I tried doing something like this, but it gave me multiple records for each SSN.
Code:
SELECT A.SSN,
A.STAT,
A.HISTORY,
A.AGENCY
FROM
(SELECT distinct A.MBR_SSN_NBR AS ssn,
B.MBR_STAT_CD AS stat,
a.MBR_HIST_SVC_CR_DT AS HISTORY,
a.agty_id_cd AS AGENCY
FROM DSNP.PR01_T_MBR_HIST A,
DSNP.PR01_T_MBR_SYS B
WHERE A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND A.AGTY_ID_CD = '00401' ) as A,
(SELECT distinct A.MBR_SSN_NBR AS ssn,
B.MBR_STAT_CD AS stat,
a.MBR_HIST_SVC_CR_DT AS HISTORY,
a.agty_id_cd AS AGENCY
FROM DSNP.PR01_T_MBR_HIST A,
DSNP.PR01_T_MBR_SYS B
WHERE A.MBR_SSN_NBR=B.MBR_SSN_NBR
and a.MBR_HIST_SVC_CR_DT >= '2014-01-31' ) as B
where a.ssn = b.ssn
Can anyone help? If I need to provide sample table data, just let me know. Thanks in advance!