Solved

max(date) lines of code lose recent records

Posted on 2013-05-22
7
241 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
Comment Utility
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
Comment Utility
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 31

Assisted Solution

by:awking00
awking00 earned 125 total points
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 125 total points
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks to everyone for their help
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now