Notifications
Clear all
Topic starter
Hi all!
We are still using 12.0.3 CPS for billing currently and we need a report to show the top 25 dx billed and top 25 procedures in a given time frame for one location. Any help with a SQL script on this would be appreciated. Thanks!
Posted : July 27, 2020 11:56 pm
From Chart or Billing module?
Posted : July 31, 2020 3:11 am
Here's some code to get you started on problems. Just replace the homelocation id and add in some start date delimiters:
select count(ICD10MasterDiagnosisId), ICD10MasterDiagnosisId, DESCRIPTION
from problem where pid in (select pid from person where homelocation = 1225648184001060 and ispatient = 'Y' and pstatus = 'A')
and (stopdate > getdate() or stopdate is null) and xid = 1000000000000000000
and upper(qualifier)= 'DX OF' group by ICD10MasterDiagnosisId, DESCRIPTION order by count(ICD10MasterDiagnosisId) desc
Posted : July 31, 2020 8:48 am
From the PM side...
top 25 cpt codes billed during a specified time range and specified location:
SELECT top 25 count(pvp.code) as Total,
pvp.Code, pvp.CPTCode, pvp.Description
FROM PatientVisitProcs pvp
JOIN PatientVisit pv ON pv.PatientVisitID=pvp.PatientVisitID
left join DoctorFacility as df on pv.FacilityId = df.DoctorFacilityId
left join Procedures as p on pvp.Code = p.Code
LEFT JOIN medlists as m on p.DepartmentMId = m.MedListsId and m.TableName = 'department'
WHERE Voided IS NULL AND ((pvp.CPTCode < '99000' OR pvp.CPTCode > '99999')
AND pvp.CPTCode IS NOT NULL
--date of service range
AND dateofservicefrom >= ISNULL('xx/xx/xxxx','1/1/1900') AND dateofservicefrom < dateadd(day,1,ISNULL('xx/xx/xxxx','1/1/3000')))
--to exclude code departments if applicable to your cptcode setup
and m.Description not in ('xxxxxxxx')
-- Specific Location
and df.ListName = 'xxxx'
group by pvp.code, pvp.cptcode, pvp.Description
order by Total desc
Posted : August 4, 2020 9:06 am
top 25 dx:
SELECT top 25 count(PatientVisitDiags.Code) as Total, PatientVisitDiags.Code AS [DX Code], PatientVisitDiags.Description AS Diagnosis
FROM PatientProfile
INNER JOIN PatientVisit ON PatientProfile.PatientProfileId = dbo.PatientVisit.PatientProfileId
INNER JOIN PatientVisitDiags ON PatientVisit.PatientVisitId = dbo.PatientVisitDiags.PatientVisitId
LEFT JOIN DoctorFacility dff ON PatientVisit.FacilityId = dff.DoctorFacilityId
LEFT JOIN Diagnosis ON PatientVisitDiags.DiagnosisId = Diagnosis.DiagnosisId
WHERE
--filter on location
(
(NULL IS NOT NULL AND PatientVisit.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND
--filter on date range
(PatientVisit.Visit >= ISNULL('01/01/2019','1/1/1900') AND PatientVisit.Visit < DATEADD(d, 1, ISNULL('12/31/2019','1/1/3000')))
GROUP BY dbo.PatientVisitDiags.Description, dbo.PatientVisitDiags.Code
Order by Total desc
Posted : August 4, 2020 9:28 am
