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

Crystal Report - CPS - Appts that have office visits or transcripts created

Trading Post

Last Post by Anonymous 1 second ago

6 Posts

2 Users

0 Reactions

106 Views

RSS

jennfranco

(@jennfranco)

Posts: 24

Eminent Member

Topic starter

I am trying to create a crystal report that looks at the appt list to make sure all patients all the schedule have a document in the chart (I am looking for a matching type of transcription but any type would work). Does anyone have anything similar to this that they would be willing to share or the SQL query for it?

Thanks in advance,

Jenn Franco

Computer Operations Manager

Sports Medicine North

[email protected]

Posted : June 21, 2012 10:46 pm

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

We don't use integrated CPS here, so I'm not sure how these tables go together, but would this work?

select pp.first, pp.last, pp.birthdate
        , ap.ApptStart, ap.ApptStop
    from Appointments ap
        join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
        left join DOCUMENT doc on ap.AppointmentsId = doc.AppointmentsId
            and doc.dtId = 21
    where doc.AppointmentsId is null
        and ap.ApptKind = 1
        and ap.ApptStart >= @DateStart
        and ap.ApptStop < @DateEnd + 1

Posted : June 22, 2012 12:18 am

jennfranco

(@jennfranco)

Posts: 24

Eminent Member

Topic starter

I apologize - I want a report that shows patients that do not have a transcription report for that date. How would I change the below query to do that?

SarekOfVulcan said:

We don't use integrated CPS here, so I'm not sure how these tables go together, but would this work?

select pp.first, pp.last, pp.birthdate
        , ap.ApptStart, ap.ApptStop
    from Appointments ap
        join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
        left join DOCUMENT doc on ap.AppointmentsId = doc.AppointmentsId
            and doc.dtId = 21
    where doc.AppointmentsId is null
        and ap.ApptKind = 1
        and ap.ApptStart >= @DateStart
        and ap.ApptStop < @DateEnd + 1

Posted : June 22, 2012 12:49 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Again with the warning that I can't actually test this here...

select pp.first, pp.last, pp.birthdate
        , ap.ApptStart, ap.ApptStop
    from Appointments ap
        join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
        left join DOCUMENT doc on pp.PId = doc.PId
            and convert_id_to_date(doc.clinicalDate) >= ap.ApptStart
                and convert_id_to_date(doc.clinicalDate) < dateadd(d, 1, ap.ApptStart)
            and doc.dtId = 21
    where doc.AppointmentsId is null
        and ap.ApptKind = 1
        and ap.ApptStart >= @DateStart
        and ap.ApptStop < @DateEnd + 1

You could tweak the date comparison so it properly did on-the-same-date, instead of within-24-hours-of-the-appointment - I just didn't want to put too much effort into it without having data to test against.

Posted : June 22, 2012 12:58 am

jennfranco

(@jennfranco)

Posts: 24

Eminent Member

Topic starter

I got this SQL to work - and it pulls up everyone who has a transcription and an appt but I want to see those who have an appt and no transcription. The patients have other document types for that date of service so making it doc.doctype <> 21 is not working.

select pp.first, pp.last, pp.birthdate, ap.ApptStart, ap.ApptStop
from Appointments ap
join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
left join DOCUMENT doc on pp.PId = doc.PId
and (CONVERT(datetime, '1/1/1960') + doc.CLINICALDATE / 1000000 / 86400) >= ap.ApptStart
and (CONVERT(datetime, '1/1/1960') + doc.CLINICALDATE / 1000000 / 86400) < dateadd(d, 1, ap.ApptStart)
and doc.DOCTYPE = 21
where doc.AppointmentsId is null
and ap.ApptKind = 1
and ap.ApptStart >= @DateStart
and ap.ApptStop < @Dateend

Any ideas how I can change this query to get my exception list?

Posted : June 22, 2012 1:41 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Ah, guess doc.AppointmentsId must be null in all cases. How about if you use

where doc.sdId is null

instead?

Posted : June 22, 2012 2:06 am

Share:

24 Forums
9,940 Topics
39.9 K Posts
0 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