Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Problems with a Simple Query

    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!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Usage of ROW_NUMBER() in this thread may help you.
    http://www.dbforums.com/db2/1644880-...r-account.html

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 http://www.dbforums.com/db2/1645007-...le-select.html
    for description.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My question.
    Is distinct necessary?
    If so, why?(in other word, from where duplicate rows come?)

  5. #5
    Join Date
    Jan 2009
    Posts
    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!

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •