Notifications
Clear all
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
Posted : June 21, 2012 10:46 pm
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
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
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
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
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
