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.
--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
(FHC.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)
GROUP BY FHC.PRIMARY_DIAGNOSIS
ORDER BY COUNT(DISTINCT FHC.[DOCUMENT]) DESC