Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

SQL script for top 25 dx and top 25 procedures in a given time frame

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

5 Posts

4 Users

0 Reactions

76 Views

RSS

sharansunkara

(@sharansunkara)

Posts: 83

Estimable Member

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

rfisher

(@rfisher)

Posts: 126

Estimable Member

From Chart or Billing module?

Posted : July 31, 2020 3:11 am

DavidShower

(@davidshower)

Posts: 1255

Noble Member

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

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

Share:

24 Forums
9,940 Topics
39.9 K Posts
1 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Powered by wpForo Powered by wpForo version 2.4.0