Solved

max(date) lines of code lose recent records

Posted on 2013-05-22
7
247 Views
Last Modified: 2013-06-03
I am trying to pull only the most recent forms received of the 4 types - ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ') but for some reason when I add in the max(date) lines below I lose several.

Thank you,

capturetheflag

SELECT distinct decode(question_answer.callout_id, '29','JFO','30','NPSC'),
  RGSN.DSTR_NR,
  RGSN.RGSN_ID,
  SUBST.SUBST_TYPE_CD,
  trunc(SUBST.RCVD_DT), subst.long_desc, t.last_nm
FROM
  RGSN,
  SUBST, question_answer, rgsn_sts, rgst t
WHERE
  ( RGSN.DSTR_NR=SUBST.DSTR_NR and RGSN.RGSN_ID=SUBST.RGSN_ID  )
  AND
  ( RGSN.DSTR_NR=T.DSTR_NR and RGSN.RGSN_ID=T.RGSN_ID  )
and
(rgsn.dstr_nr = question_answer.dstr_nr and rgsn.rgsn_id = question_answer.rgsn_id)
and (rgsn.dstr_nr = rgsn_sts.dstr_nr and rgsn.rgsn_id = rgsn_sts.rgsn_id)
and
  (
   RGSN.DSTR_NR  =  4085
   AND
   RGSN.RGSN_ID IN (393930088,
411308940,
411330955,
450388835,
500105199
)
//MY PROBLEM WITH THE CODE IS HERE!
and SUBST.SUBST_TYPE_CD in ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ'))
and SUBST.RCVD_DT =
(select max(rcvd_dt)
from subst
where rgsn_id = rgsn.rgsn_id
and SUBST_TYPE_CD in ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ'))

Open in new window

0
Comment
Question by:capturetheflag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39187700
I would assume that the records returned by this portion of the sql
select max(rcvd_dt)
from subst
where rgsn_id = rgsn.rgsn_id
and SUBST_TYPE_CD in ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ')

Open in new window


Would be larger (and have a wider array of possible dates) than this :
WHERE
  ( RGSN.DSTR_NR=SUBST.DSTR_NR and RGSN.RGSN_ID=SUBST.RGSN_ID  )
  AND
  ( RGSN.DSTR_NR=T.DSTR_NR and RGSN.RGSN_ID=T.RGSN_ID  )
and
(rgsn.dstr_nr = question_answer.dstr_nr and rgsn.rgsn_id = question_answer.rgsn_id)
and (rgsn.dstr_nr = rgsn_sts.dstr_nr and rgsn.rgsn_id = rgsn_sts.rgsn_id)
and
  (
   RGSN.DSTR_NR  =  4085
   AND
   RGSN.RGSN_ID IN (393930088,
411308940,
411330955,
450388835,
500105199
)
//MY PROBLEM WITH THE CODE IS HERE!
and SUBST.SUBST_TYPE_CD in ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ'))

Open in new window


So when you get the max date from the larger pool of records... you also weed that record out of your more specific larger portion of the query.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39187739
I'd suggest a join on both the code and the date, e.g.
inner join (
        select subst_type_cd, max(rcvd_dt) as max_rcvd_dt
        from subst
        where rgsn_id = rgsn.rgsn_id
        and SUBST_TYPE_CD in ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ')
        group by subst_type_cd
                   ) mdate ON SUBST.RCVD_DT = mdate.max_rcvd_dt 
                                and  SUBST.SUBST_TYPE_CD = mdate.SUBST_TYPE_CD

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 39188766
SELECT * FROM
(SELECT decode(q.callout_id, '29','JFO','30','NPSC') as callout,
   r.DSTR_NR,
   r.RGSN_ID,
   s.SUBST_TYPE_CD,
   trunc(s.RCVD_DT) as rcvd_dt, s.long_desc, t.last_nm
 FROM
   RGSN r, SUBST s, question_answer q, rgsn_sts rs, rgst t,
   row_number() over (partition by s.subst_type_ccd order by s.rcvd_dt desc) rn
 WHERE
   ( r.DSTR_NR=s.DSTR_NR and r.RGSN_ID=s.RGSN_ID  )
   AND
   ( r.DSTR_NR=t.DSTR_NR and r.RGSN_ID=t.RGSN_ID  )
 and (r.dstr_nr = q.dstr_nr and r.rgsn_id = q.rgsn_id)
 and (r.dstr_nr = rs.dstr_nr and r.rgsn_id = rs.rgsn_id)
 and
   (
    r.DSTR_NR  =  4085
    AND
    r.RGSN_ID IN (393930088,411308940,411330955,450388835,500105199)
    AND s.SUBST_TYPE_CD in ('DHAPCALC','DHAPAGRMT','DHAPPIINOTE','RECREQ')
   )
)
WHERE rn = 1
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 125 total points
ID: 39189458
corrected syntactical errors in awking00 solution.
SELECT * 
  FROM (SELECT DECODE(question_answer.callout_id, '29', 'JFO', 
                                                  '30', 'NPSC') callout_id, 
               RGSN.DSTR_NR, 
               RGSN.RGSN_ID, 
               SUBST.SUBST_TYPE_CD, 
               TRUNC(SUBST.RCVD_DT), 
               subst.long_desc, 
               t.last_nm, 
               ROW_NUMBER() 
                 OVER ( 
                   PARTITION BY RGSN.RGSN_ID, SUBST.SUBST_TYPE_CD 
                   ORDER BY SUBST.RCVD_DT DESC)                 rn 
          FROM RGSN, 
               SUBST, 
               question_answer, 
               rgsn_sts, 
               rgst t 
         WHERE RGSN.DSTR_NR = SUBST.DSTR_NR 
           AND RGSN.RGSN_ID = SUBST.RGSN_ID 
           AND RGSN.DSTR_NR = T.DSTR_NR 
           AND RGSN.RGSN_ID = T.RGSN_ID 
           AND rgsn.dstr_nr = question_answer.dstr_nr 
           AND rgsn.rgsn_id = question_answer.rgsn_id 
           AND rgsn.dstr_nr = rgsn_sts.dstr_nr 
           AND rgsn.rgsn_id = rgsn_sts.rgsn_id 
           AND RGSN.DSTR_NR = 4085 
           AND RGSN.RGSN_ID IN ( 393930088, 411308940, 411330955, 450388835, 500105199 ) 
           AND SUBST.SUBST_TYPE_CD IN ( 'DHAPCALC', 'DHAPAGRMT', 'DHAPPIINOTE', 'RECREQ' )) t1
 WHERE rn = 1 

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39190712
Thank you, Sharath_123 for catching my error of putting the row_number function on the wrong line. I was never good at copying and pasting :-)
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39190938
slip-ups with copy/paste are virtually inevitable :)

but, if in the mood for syntax comments, I'd suggest using ANSI joins at the very least
SELECT *
FROM (
    SELECT decode(q.callout_id, '29', 'JFO', '30', 'NPSC') AS callout
        , r.DSTR_NR
        , r.RGSN_ID
        , s.SUBST_TYPE_CD
        , trunc(s.RCVD_DT) AS rcvd_dt
        , s.long_desc
        , t.last_nm
        , row_number() OVER (
            PARTITION BY s.subst_type_ccd ORDER BY s.rcvd_dt DESC
            ) rn
    FROM RGSN r
    INNER JOIN SUBST s
        ON r.DSTR_NR = s.DSTR_NR
            AND r.RGSN_ID = s.RGSN_ID
    INNER JOIN question_answer q
        ON r.dstr_nr = q.dstr_nr
            AND r.rgsn_id = q.rgsn_id
    INNER JOIN rgsn_sts rs
        ON r.dstr_nr = rs.dstr_nr
            AND r.rgsn_id = rs.rgsn_id
    INNER JOIN rgst t
        ON r.DSTR_NR = t.DSTR_NR
            AND r.RGSN_ID = t.RGSN_ID
    WHERE (
            r.DSTR_NR = 4085
            AND r.RGSN_ID IN (
                  393930088
                , 411308940
                , 411330955
                , 450388835
                , 500105199
                )
            AND s.SUBST_TYPE_CD IN (
                  'DHAPCALC'
                , 'DHAPAGRMT'
                , 'DHAPPIINOTE'
                , 'RECREQ'
                )
            )
    )
WHERE rn = 1

Open in new window

0
 
LVL 1

Author Closing Comment

by:capturetheflag
ID: 39216727
Thanks to everyone for their help
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query question 12 34
Help with Oracle IF statment 5 37
Limit number of characters returned to 999 9 34
How to keep a record with the highest value 3 44
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question