combine querys

I have two queries and I need to combine them.  the PK in RNDC14 is NDC and when I run the query I get a unique value for NDC what I need to do is run the second part to get the record in the RNP2 database that is linked by the NDC.  the only problem is that there are multiple occurances of the NDC in the RNP2 database so I have to get the latest date.   How can I combine the 2 queries below.  Thank you for your help
SELECT     r.NPT_PRICEx, r.NDC, r.NPT_DATEC
FROM         dbo.RNP2 r INNER JOIN
                          (SELECT     MAX(npt_datec) AS maxbbdc, NDC
                            FROM          rnp2
                            GROUP BY NDC) a ON r.NDC = a.NDC AND r.NPT_DATEC = a.maxbbdc
WHERE     (r.NPT_TYPE = '01')
 
SELECT     TOP 100 PERCENT r.ndc, r.ln, r.bbdc
FROM         dbo.RNDC14 r INNER JOIN
                          (SELECT     ln, MAX(bbdc) maxbbdc
                            FROM          RNDC14
                            GROUP BY ln) a ON r.ln = a.ln AND r.bbdc = a.maxbbdc
WHERE     (r.cl = 'f') AND (r.gni = '1')
ORDER BY r.ln

Open in new window

running32Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Is this what you are looking for.
SELECT     r.NPT_PRICEx, r.NDC, r.NPT_DATEC
FROM         dbo.RNP2 r INNER JOIN
                          (SELECT     MAX(npt_datec) AS maxbbdc, NDC
                            FROM          rnp2
                            GROUP BY NDC) a ON r.NDC = a.NDC AND r.NPT_DATEC = a.maxbbdc
INNER JOIN (SELECT     r.ndc, r.ln, r.bbdc
FROM         dbo.RNDC14 r INNER JOIN
                          (SELECT     ln, MAX(bbdc) maxbbdc
                            FROM          RNDC14
                            GROUP BY ln) a ON r.ln = a.ln AND r.bbdc = a.maxbbdc
WHERE     (r.cl = 'f') AND (r.gni = '1')) b ON b.NDC = a.NDC AND b.maxbbdc = a.maxbbdc
WHERE     (r.NPT_TYPE = '01')

Open in new window

0
 
running32Author Commented:
yes thanks
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.