Notifications
Clear all
Topic starter
Does anyone have a report for the "Top 100 Diagnoses by Location and Date" report for ICD-9? I show we have a report in our EMR reports under MedicaLogic - Knowledgebank but it says the report is missing.
EMR Version 9.8.9
Thanks!
Posted : August 4, 2015 10:52 am
I have a SQL query for it, if that helps. It might get you closer than where you are now.
DECLARE @Start datetime, @End datetime; SELECT @Start = '7/1/2014', @End = '7/1/2015'; --change these as needed
;WITH cteDiags AS (
SELECT
f.ABBREVNAME AS Location,
d.ICD9Code,
COUNT(d.PatientVisitDiagsId) AS DiagCount
FROM PatientVisit v
INNER JOIN PatientVisitDiags d
ON v.PatientVisitId = d.PatientVisitId
INNER JOIN LOCREG f
ON v.FacilityId = f.FacilityId
WHERE v.Visit BETWEEN @Start AND @End
GROUP BY f.ABBREVNAME, d.ICD9Code
),
cte_Top AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY d.Location ORDER BY d.DiagCount DESC) AS RecNum,
d.Location,
d.ICD9Code,
c.Description,
d.DiagCount
FROM cteDiags d
INNER JOIN ICD9Codes c
ON d.ICD9Code = c.Code
)
SELECT Location, ICD9Code, Description, ICD10Code, DiagCount
FROM cte_Top
WHERE RecNum <= 100
ORDER BY Location, DiagCount DESC
Hope that helps!
(shameless blog plug - Adventures in Centricity)
Posted : August 6, 2015 6:37 am
Topic starter
This query is for a SQL database though correct? I should have clarified that we are using an Oracle DB for the EMR. We are however using a SQL DB for our CPS PM. We run both products at our practice.
Would I be able to run this against the CPS SQL DB to gather those top 100 codes?
Posted : August 10, 2015 6:32 am
Unfortunately I don't know how the database is split between those two platforms, so I can't say for sure. Worst case scenario, you'd get an error. Can't hurt to try.
Posted : August 10, 2015 6:34 am
Topic starter
That actually worked out perfectly. I just had to remove any reference to "Location" as we only have one location and it spit the report right out. Thanks for your assistance!
Posted : August 10, 2015 7:15 am
ronmoses any chance you can help me with that same code but pull the diagnosis from a specifially billed facitilty like the hospital instead of the office?
Posted : August 10, 2015 7:20 am
Sure! I actually made that modification this morning. We even get to simplify it a bit because we no longer need that second CTE.
You'll just need to know how that facility is identified in the LOCREG table. I'm going to assume you know the ABBREVNAME value, but you can modify this to look for LOCID or FacilityId, whatever you need. In this case I'm going with LOCREG.ABBREVNAME = 'Hospital'
;WITH cteDiags AS
(
SELECT
d.ICD9Code,
COUNT(d.PatientVisitDiagsId) AS DiagCount
FROM PatientVisit v
INNER JOIN PatientVisitDiags d
ON v.PatientVisitId = d.PatientVisitId
INNER JOIN LOCREG f
ON v.FacilityId = f.FacilityId
WHERE v.Visit BETWEEN @Start AND @End
AND f.ABBREVNAME = 'Hospital' --edit this to suit your needs
GROUP BY d.ICD9Code
)
SELECT TOP 100
d.ICD9Code,
c.Description,
md10.Code AS ICD10Code,
d.DiagCount
FROM cteDiags d
INNER JOIN ICD9Codes c
ON d.ICD9Code = c.Code
INNER JOIN MasterDiagnosis md9
ON c.Code = md9.Code
INNER JOIN ProblemCodeMap pcm
ON md9.MasterDiagnosisId = pcm.FromMasterDiagnosisID
INNER JOIN MasterDiagnosis md10
ON pcm.ToMasterDiagnosisID = md10.MasterDiagnosisId
WHERE pcm.MappingType = 1
ORDER BY DiagCount DESC;
Hope this helps!
Posted : August 10, 2015 7:36 am
For anyone interested, I've expanded on this topic considerably on my blog. Click here!
Posted : August 12, 2015 6:16 am
