Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

max(date) lines of code lose recent records

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
capturetheflag
Asked:
capturetheflag
4 Solutions
 
Ken ButtersCommented:
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
 
PortletPaulCommented:
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
 
awking00Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SharathData EngineerCommented:
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
 
awking00Commented:
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
 
PortletPaulCommented:
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
 
capturetheflagAuthor Commented:
Thanks to everyone for their help
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now