Problems with a Simple Query

rockdave35 · #1 (**permalink**) 07-10-09, 08:57

Hey guys,

I have the following query:

Code:


SELECT   distinct PR0.RECIP_SSN_NBR,
         PR01.AGTY_ID_NM, 
         PR0.RECIP_RETIR_DT,
         PR0.AGTY_ID_CD,
         PR02.ANTY_PYMT_TOT_AMT
         
          
FROM     DSNP.PR01_T_RECIP_SYS PR0, 
         DSNP.PR01_T_EMPR PR01, 
         DSNP.PR01_T_ANTY_PYMT PR02 
WHERE    PR0.AGTY_ID_CD=PR01.AGTY_ID_CD 
AND      PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR 
AND      PR0.AGTY_SYS_CD = 'TAPERS' 
AND      PR02.ANTY_PYMT_DT BETWEEN '2007-07-01' AND '2008-06-30'

They want to see the employee's most recent PR02.ANTY_PYMT_TOT_AMT. The problem is the results are displaying numerous records for the same SSN if they have different TOT_AMT's within my date range.

How can I modify the query to only pull the most recent payment amount going by the PR02.ANTY_PYMT_DT field?

Ex. SSN DATE PAYMENT
3334455 08/12/07 234.44
3334455 09/12/07 123.44

I want the query to display:

3334455 09/12/07 123.44

Thanks!

tonkuma · #2 (**permalink**) 07-10-09, 09:10

Usage of ROW_NUMBER() in this thread may help you.
Last transaction by customer and account

tonkuma · #3 (**permalink**) 07-10-09, 09:28

Or

Code:

SELECT   distinct PR0.RECIP_SSN_NBR,
         PR01.AGTY_ID_NM, 
         PR0.RECIP_RETIR_DT,
         PR0.AGTY_ID_CD,
         PR02.ANTY_PYMT_TOT_AMT
         
          
FROM     DSNP.PR01_T_RECIP_SYS PR0, 
         DSNP.PR01_T_EMPR PR01, 
         LATERAL
         (SELECT *
          FROM   DSNP.PR01_T_ANTY_PYMT PR02
          WHERE  PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR 
          AND    PR02.ANTY_PYMT_DT BETWEEN '2007-07-01' AND '2008-06-30'
          ORDER BY PR02.ANTY_PYMT_DT DESC
          FETCH FIRST 1 ROW ONLY) PR02
WHERE    PR0.AGTY_ID_CD=PR01.AGTY_ID_CD 
-- AND      PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR 
AND      PR0.AGTY_SYS_CD = 'TAPERS' 
-- AND      PR02.ANTY_PYMT_DT BETWEEN '2007-07-01' AND '2008-06-30'
;

LATERAL must be neccesary.
Please see Complex select (divide & return multiple rows with a single select)
for description.

tonkuma · #4 (**permalink**) 07-10-09, 09:38

My question.
Is distinct necessary?
If so, why?(in other word, from where duplicate rows come?)

rockdave35 · #5 (**permalink**) 07-10-09, 09:46

Quote:

Originally Posted by tonkuma

My question.
Is distinct necessary?
If so, why?(in other word, from where duplicate rows come?)

You're right. The distinct doesn't do anything. Forgot to take that out. lol I'll give the Lateral a shot. Thanks!

dav1mo · #6 (**permalink**) 07-10-09, 10:02

Or since you want the most recent go back to just about the easiest way and ask for the most recent

SELECT PR0.RECIP_SSN_NBR,
PR01.AGTY_ID_NM,
PR0.RECIP_RETIR_DT,
PR0.AGTY_ID_CD,
PR02.ANTY_PYMT_TOT_AMT

FROM DSNP.PR01_T_RECIP_SYS PR0,
DSNP.PR01_T_EMPR PR01,
DSNP.PR01_T_ANTY_PYMT PR02
WHERE PR0.AGTY_ID_CD=PR01.AGTY_ID_CD
AND PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR
AND PR0.AGTY_SYS_CD = 'TAPERS'
AND PR02.ANTY_PYMT_DT = (select max(PR03.ANTY_PYMT_DT)
from DSNP.PR01_T_ANTY_PYMT PR03
where PR03.ANTY_PYMT_DT BETWEEN '2007-07-01' AND '2008-06-30' and PR02.RECIP_SSN_NBR = PR03.RECIP_SSN_NBR)

Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech