Solved

max(date) lines of code lose recent records

Posted on 2013-05-22
7
246 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
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 40

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

791 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