Notifications
Clear all
Topic starter
| (3) Generate lists of patients by specific conditions to use for quality improvement, reduction of disparities, research, or outreach. |
Hello I have been working on writing a SQL query for this meaningful use report and I am running into problems of getting 100s and sometimes 1000s of duplicates for a given patient. I was curious if anyone else was working on this report or has been running into any problems below is the beginning of the sql query and it is returning 30+ millions of rows. Any help that someone might able to give would be grateful.
Select
pp.first
,
pp.last
,
df.listname
,
pvd.ICD9Code
fromPatientProfile
pp
LeftJoinDoctorFacilitydfonpp.doctorid=df.
DoctorFacilityId
LeftJoinPatientVisitpvondf.DoctorFacilityId=pv.
DoctorId
LeftJoinPatientVisitDiagspvdonpvd.PatientVisitDiagsId=pv.
PatientVisitId
Wherepp.pstatus=
'A'
GroupBypvd.ICD9Code,df.ListName,pp.first,pp.
last
Posted : June 21, 2012 6:42 am
You were joining pp to df on doctorid, and pv to df on doctorId as well — that''s not going to work. Try this way:
Select pp.first, pp.last, df.listname, pvd.ICD9Code from PatientProfile pp Join DoctorFacility df on pp.doctorid = df.DoctorFacilityId Join PatientVisit pv on pp.PatientProfileId = pv.PatientProfileId Join PatientVisitDiags pvd on pvd.PatientVisitId = pv.PatientVisitId Where pp.pstatus = 'A' Group By pvd.ICD9Code, df.ListName, pp.first, pp.last
Posted : June 21, 2012 7:31 am
Topic starter
Oh thanks I see that now, if you can I am also running into a similar problem on this query, I have been looking at the Joins and they seem to be okay. Any help would be greatly appreciated. Thanks.
Select
pp.last + ' ' + isnull(pp.middle, ' ') + pp.first as [Full Name],
df.listname as [Facility Name],
pp.AlertNotes,
app.ApptStart [Appointment Date]
From PatientProfile pp
Join DoctorFacility df on pp.DoctorId = df.DoctorFacilityId
Join PatientVisit pv on pp.PatientProfileId = pv.PatientProfileId
Join Appointments app on pp.FacilityId = app.FacilityId
where df.listname not like '%ROCT%' and pp.alertnotes like '%DOI%'
Order By pp.last, pp.first
Posted : June 21, 2012 8:14 am
That query is multiplying the number of appointments by the number of visits, without any limitation on either, like date range. What are you trying to find out there? This might not actually be incorrect, but there's probably a more efficient way to do it.
Posted : June 22, 2012 12:01 am
Topic starter
What I need to do is search all appointments for patients for a given day that are not at any location with ROCT in it and that have the phrase DOI in the alert notes and then list them. I was going to include the date filter once I get into Centricity, the problem I am having now is that the query returns something like 30 Million rows in SQL Server Management, and is not nearly done chugging.
Posted : June 22, 2012 12:16 am
Thomase said:
What I need to do is search all appointments for patients for a given day that are not at any location with ROCT in it and that have the phrase DOI in the alert notes and then list them. I was going to include the date filter once I get into Centricity, the problem I am having now is that the query returns something like 30 Million rows in SQL Server Management, and is not nearly done chugging.
Ok, then it sounds like you don't need to join PatientVisit into the mix at the moment — how's it look if you drop that?
ETA: Oops, there are worse problems — back in a second.
Ok. The big problem is that you're joining the PatientProfile table to the Appointments table on FacilityId -- that's why you're getting so many records. Try this.
Select pp.last + ' ' + isnull(pp.middle, ' ') + pp.first as [Full Name]
, pp.AlertNotes
, df.listname as [Facility Name]
, app.ApptStart as [Appointment Date]
From PatientProfile pp
Join Appointments app on pp.PatientProfileId = app.OwnerId
Join DoctorFacility dff on app.FacilityId = dff.DoctorFacilityId
where dff.listname not like '%ROCT%'
and pp.alertnotes like '%DOI%'
Order By pp.last, pp.first
Posted : June 22, 2012 12:20 am
Topic starter
The query still runs but I am still getting many duplicates in the query, and I am trying to figure out why there are so many duplicates in this query.
Posted : June 22, 2012 12:37 am
Thomase said:
The query still runs but I am still getting many duplicates in the query, and I am trying to figure out why there are so many duplicates in this query.
You mean, the query I sent in #6 produces lots of duplicates?
Posted : June 22, 2012 12:39 am
Topic starter
Oh no I didnt see that new update, there are not any duplicates now so that is good :). But now it seems like I am missing many rows of data, only have 45046 rows of data which should be pulling data from the beggining of when we first started using Centricity so 45k is not nearly enough rows of data.
Posted : June 22, 2012 12:50 am
If you do
select count(distinct PatientProfileID)
from PatientProfile pp
where pp.AlertNotes like '%DOI%'
how many records come back?
Posted : June 22, 2012 1:02 am
Topic starter
4181
Posted : June 22, 2012 1:15 am
Topic starter
I guess that makes more sense then, I dont know why I didn't think of that, so that means only 4181 patients alerts notes have the phrase DOI in them. I guess that makes more sense now.
Posted : June 22, 2012 1:15 am
So that would average out to 11 appointments each. Do appointments get cleaned up in your system after a certain amount of time? That might explain why there aren't enough. What if you query against the PatientVisit table instead?
select trim(pp.First + ' ' + isnull(pp.Middle, '')) + ' ' + pp.Last as [Full Name]
, pp.AlertNotes
, df.Listname as [Facility Name]
, pv.Visit as [Appointment Date]
from PatientProfile pp
join PatientVisits pv on pp.PatientProfileId = pv.PatientProfileId
join DoctorFacility dff on pv.FacilityId = dff.DoctorFacilityId
where dff.Listname not like '%ROCT%'
and pp.AlertNotes like '%DOI%'
order by pp.Last, pp.First, pv.Visit
Posted : June 22, 2012 1:20 am
Topic starter
That gives me less rows, so the other way I had more rows, but the number of rows makes a little more sense since there are only 4181 alertnotes total that have the phrase DOI.
Posted : June 22, 2012 1:36 am
Topic starter
So in the end I think that will work, you said you saw other problems with the query, what did you see wrong?
Posted : June 22, 2012 1:44 am
Page 1 / 2
Next
