Help with a derived table in an existing query

Hi Experts,

Need help with how to create a derived table within the query below.

The current query lists all of a hospital's primary diagnoses in ICD-9 code (e.g. V20.2, etc.), as well as counts and sums of claims for a designated time period.

What I'd like to add in is a description, ICD_9_Desc, so that when someone sees V20.2, it will pull from the ICD-9 table and see the code as well as that it's a "Well Child Visit".

The ICD_9 and ICD_9_DESC are in the ICD_9_CODES table.

Cannot figure out where I can reference this from within the query.

Thank you


SELECT FHC.PRIMARY_DIAGNOSIS
--insert ICD-9 description here
, COUNT(DISTINCT FHC.[DOCUMENT]) AS TOTAL_CLAIMS MASTER_CLAIM#DETAIL
, SUM(FHC.BILL_AMT) AS TOTAL_BILLED
, SUM(FHC.TOTAL_PAY_AMT) AS TOTAL_PAID
from #temp_eligibles D
LEFT JOIN FH_MASTER_CLAIM_VALID FHC ON D.MEM_NO = FHC.MEMBER
LEFT JOIN dbo.PHYSICIAN P ON FHC.PROVIDER = P.PROVIDER_ID
WHERE
(FHC.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)
GROUP BY FHC.PRIMARY_DIAGNOSIS
ORDER BY COUNT(DISTINCT FHC.[DOCUMENT]) DESC
britpopfan74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
Where is the ICD_9 column other than the ICD_9_CODES?

Basically what you will do is to LEFT JOIN the dbo.ICD_9_CODES table on the table that has the ICD_9 column in it and then pull out the ICD_9_DESC column from the ICD_9_CODES table and put it in the place that you specified, and then add the ICD_9_DESC to the GROUP BY clause as well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
britpopfan74Author Commented:
Thank you - join worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.