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

Meaningful Use Menu Objectives 3, Generate lists of patients

Page 1 / 2 Next

Trading Post

Last Post by ljburnham 12 years ago

18 Posts

3 Users

0 Reactions

139 Views

RSS

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Topic starter

4181

Posted : June 22, 2012 1:15 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

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