Problems with Filtering Data Results

[txdave35]

I am stuck on this one. They want to see a list of people who have service dates occuring before 07-01-1997. I have a field for service date in the table.

The problem is the table contains multiple records for the same person with different service dates. Here is my query:

SELECT   DISTINCT MBR_SSN_NBR, MBR_HIST_SVC_CR_DT 
FROM     DSNP.PR01_T_MBR_HIST 
WHERE    HIST_CATEG_CD = '31' 
AND      MBR_HIST_SVC_CR_DT < '1997-07-01'

The problem is that Joe Blow qualifies for my condition because he does have service records in the table before 1997. However, he also has service records in the table occuring after 1997.

I am puzzled how to get SQL to exclude people from the table that has any service date record after 1997.

The table appears like this:

SSN Service Date

11122 1945-05-03
11122 1966-04-06
11122 2005-12-01


I don't want the query to report 11122 because he has service after 1997. Can anyone help? Thanks!

 

[gmmastros]

Try this:

SELECT   MBR_SSN_NBR, 
         Min(MBR_HIST_SVC_CR_DT) As FirstServiceDate,
         Max(MBR_HIST_SVC_CR_DT) As LastServiceDate,
FROM     DSNP.PR01_T_MBR_HIST
WHERE    HIST_CATEG_CD = '31'
GROUP BY MBR_SSN_NBR
HAVING   Max(MBR_HIST_SVC_CR_DT) < '1997-07-01'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

What is your SQL Server version?

There are few alternative solutions to the one suggested by George:

select MBR_SSN_NBR,
MBR_HIST_SVC_CR_DT from (SELECT   MBR_SSN_NBR,
MBR_HIST_SVC_CR_DT,
row_number over (partition by MBR_SSN_NBR) as rowNum
FROM     DSNP.PR01_T_MBR_HIST
WHERE    HIST_CATEG_CD = '31') X where RowNum = 1 and MBR_HIST_SVC_CR_DT < '1997-07-01'

Another solution - same as your original

SELECT   DISTINCT MBR_SSN_NBR, MBR_HIST_SVC_CR_DT
FROM     DSNP.PR01_T_MBR_HIST
WHERE    HIST_CATEG_CD = '31'
AND      MBR_HIST_SVC_CR_DT < '1997-07-01' 
and not exists (select 1 from DSNP.PR01_T_MBR_HIST A
WHERE    HIST_CATEG_CD = '31' and A.MBR_SSN_NBR = DSNP.PR01_T_MBR_HIST.MBR_SSN_NBR and A.MBR_HIST_SVC_CR_DT >= '1997-07-01')

 

[txdave35]

Forgot to come back last week and thank you guys. Your suggestions were very helpful, as always.