Link to home
Create AccountLog in
Avatar of capturetheflag
capturetheflag

asked on

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

Avatar of Ken Butters
Ken Butters
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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 :-)
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of capturetheflag
capturetheflag

ASKER

Thanks to everyone for their help