Solved

max(date) lines of code lose recent records

Posted on 2013-05-22
7
243 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Select latest stock count records 11 56
SQL Server 2008 R2 - Execution Plan 3 57
sql query Help 12 53
SQL Union 20 44
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
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…

920 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

15 Experts available now in Live!

Get 1:1 Help Now