Notifications
Clear all
Topic starter
Does anyone have a crystal report built that will list the top diagnosis codes used by a provider?
Posted : June 7, 2016 7:19 am
I do not have a Crystal Report but I just wrote a quick SQL script for you. If you really need it in a report it would probably only take about 10-15 minutes to create so let me know. The script uses the "MOSTUSEDPROBLEM" table to determine usage. I only include providers by checking to see if there is an NPI number and I rank each problem in order and only include the top 10 problems. (you could change that number to fit your needs on the second to last line)
SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY MU.PVID ORDER BY MU.USAGECOUNT DESC) TOP,
U.SEARCHNAME, MU.DESCRIPTION, MD.CODE, MU.USAGECOUNT FROM MOSTUSEDPROBLEM MU
INNER JOIN USRINFO U ON MU.PVID = U.PVID
INNER JOIN MASTERDIAGNOSIS MD ON MU.ICD10MASTERDIAGNOSISID = MD.MASTERDIAGNOSISID
WHERE U.NPI IS NOT NULL
)
WHERE TOP <= 10
ORDER BY SEARCHNAME, TOP
Posted : June 8, 2016 4:51 am
Topic starter
Thank you Ryan for the SQL but if it isn't too much trouble, a crystal report would be great since this is something that I would eventually like the users to have and run. We have a Residency program starting up and one of the reports they will need to run periodically is this report by provider and with a date range.
This will be a big help. Thank you Peg
Posted : June 8, 2016 7:09 am
I just turned that SQL into a report and then realized that you asked for it with a date range. The MOSTUSEDPROBLEM table does not have a date as it is a summary table giving the provider, description, icd code and number of times used.
Posted : June 8, 2016 7:48 am
I'll send you a copy of the report via email.
The SQL code to do it by date is:
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PROVIDER ORDER BY CNT DESC) RANK, T.* FROM
(
SELECT DISTINCT COUNT(P.DESCRIPTION) OVER (PARTITION BY USRID, P.DESCRIPTION, MD.CODE) CNT,
P.DESCRIPTION, MD.CODE, U.SEARCHNAME PROVIDER FROM ML.PROBLEM P
INNER JOIN ML.USRINFO U ON P.USRID = U.PVID
INNER JOIN ML.MASTERDIAGNOSIS MD ON P.ICD10MASTERDIAGNOSISID = MD.MASTERDIAGNOSISID
WHERE PUBTIME > ML.CONVERT_DATE_TO_ID(TO_DATE('01012015','MMDDYYYY'))
AND PUBTIME < ML.CONVERT_DATE_TO_ID(TO_DATE('01012016','MMDDYYYY'))
AND U.NPI IS NOT NULL
) T
) WHERE RANK <= 10
ORDER BY PROVIDER, CNT DESC
Posted : June 8, 2016 8:28 am
