• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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
0
britpopfan74
Asked:
britpopfan74
1 Solution
 
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
 
britpopfan74Author Commented:
Thank you - join worked.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now