How to Return Query Results with Multiple Conditions on Same Field? 1

[txdave35]

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

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.

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!

 

[gmmastros]

Please write the simplest query you can that shows Current Agency number and Active. It's ok if this returns too many rows. Show me that query and I will show you how to write the rest.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[txdave35]

UPDATE:

I did some more tinkering, and came closer with this:

SELECT A.SSN,
       A.STAT,
       B.HISTORY,
       B.AGENCY



FROM 


(SELECT   distinct A.MBR_SSN_NBR AS ssn, 
         B.MBR_STAT_CD AS stat
       
 
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      b.mbr_stat_cd in ('1', 'A')
 
and a.MBR_HIST_SVC_CR_DT >= '2014-01-31' ) as B


where a.ssn = b.ssn

So now my query is returning 109 members who have active service with their current agency. I still don't know how to show the other members who don't have active service, but meet the first condition of having service history with '00401'. Maybe a Union?

 

[gmmastros]

You need a left join, like this:

SELECT A.SSN,
       A.STAT,
       B.HISTORY,
       B.AGENCY

FROM 
       (
	     SELECT   distinct A.MBR_SSN_NBR AS ssn, 
                  B.MBR_STAT_CD AS stat
         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
       Left Join (
	     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      b.mbr_stat_cd in ('1', 'A')
                  and a.MBR_HIST_SVC_CR_DT >= '2014-01-31' 
	   ) as B
	     On A.ssn = b.ssn

This will return at least one row in the output for each row in the A aliased derived table. If there is no match in the B table, the columns in the outside select (B.History and B.Agency) will have NULL values.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[txdave35]

Thanks so much, George. That did it.