I am pulling one code (ICD9) which is in table AbsDrgDiagnosis.Diagnosis. I need to pull another code (SNOMED_CT) which is in another table DMisNomenclatureMapCodes.CodeID when DMisNomenclatureMapCodes.CodeSetID = 'SNOMED_CT'
Then only way to link these two tables is by AbsDrgDiagnosis.Diagnosis = DMisNomenclatureMapCodes.CodeID when DMisNomenclatureMapCodes.CodeSetID = 'ICD9'
So for example I have to report 3942005 from DMisNomenclatureMapCodes.CodeID when CodeSetID = 'SNOMED_CT'.
EXAMPLE DATA:
Diagnosis
AbsDrgDiagnosis 290.23
CodeID CodeSetID MisNomenclatureMapID
DMisNomenclatureMapCodes 290.23 ICD9 IMO-PROC-99959
3942005 SNOMED_CT IMO-PROC-99959
53899 CPT IMO-PROC-99959
So far I can only compare AbsDrgDiagnosis.Diagnosis on DMisNomenclatureMapCodes.CodeID, which both equal 290.23, but after that I am lost as to how I get the SNOMED code. I assume I have to do another subquery on DMisNomenclatureMapCodes.MisNomenclatureMapID to get the grouping and then find the CodeSetID of SNOMED_CT to get the final value of CodeID 3942005. how do I attempt this? Any help would be greatly appreciated, I tried a couple of subqueries but they errored out. I am new to SQL so if I didnt say something correctly or write it in expert fashion please be kind.
CODE:
DECLARE
SET @StartDate = '10/28/2013 00:00:000'
SET @EndDate = '12/28/2013 23:59:000'
SELECT
,Diagnosis AS Code_3
,DMisNomenclatureMapCodes.CodeID
,DMisNomenclatureMapCodes.MisNomenclatureMapID
FROM AbsDrgDiagnoses
LEFT JOIN AbstractData
ON AbstractData.VisitID = AbsDrgDiagnoses.VisitID
LEFT JOIN DMisNomenclatureMapCodes
ON DMisNomenclatureMapCodes.CodeID = AbsDrgDiagnoses.Diagnosis
WHERE AbstractData.DischargeDateTime BETWEEN @StartDate and @EndDate
Thanks to anyone who replied to this. As it turns out I had to build a couple of sub-queries to achieve what I wanted. The bottom most sub-query returns the nomenclature map results when both ICD9 codes are present via
NMC.CodeID = AbsDrgDiagnoses.Diagnosis.
Now this was my problem, I didn't know that you could include an AND statement within the sub-query when it was returning a single result from another sub-query, learned something new! Thus I could only return the CodeSetID of 'SNOMED_CT' and not the actual CodeID of 5510009. By putting in
WHERE NMC.CodeSetID = 'SNOMED_CT' AND NMC.MisNomenclatureMapID IN
I was then able to return the value needed.
CODE:
,COALESCE((SELECT NMC.CodeID
FROM DMisNomenclatureMapCodes NMC
WHERE NMC.CodeSetID = 'SNOMED_CT' AND NMC.MisNomenclatureMapID IN
(SELECT NMC.MisNomenclatureMapID
FROM DMisNomenclatureMapCodes NMC
WHERE (NMC.CodeID = AbsDrgDiagnoses.Diagnosis)
)
),'') AS SNOSet_TEST