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?
 
DBAduck - Ben MillerConnect With a Mentor Principal 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
 
britpopfan74Author Commented:
Thank you - join worked.
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.