68213

Most recent date with multiple providers

Question:

Below is my data:

MedicalRecordNumber edcdate wk lastappt facility provider ---------------------- ---------- ----- ---------------------- --------------- ------------------------ 255599 10/9/2014 37 3/8/2014 0:00 Women's Health Prenatal Registration 255599 10/9/2014 37 6/12/2014 0:00 Women's Health Tarcia 255599 10/9/2014 37 9/4/2014 0:00 Women's Health Beaven

This is the SQL I'm using:

Select a.ownerid, p.patientprofileid, p.searchname, max(a.EmrApptStart) as LastAppt, f.listname as facility, d.ListName as Provider from Appointments a left join patientprofile p on p.PatientProfileId= a.OwnerId inner join DoctorFacility f on f.DoctorFacilityId = a.FacilityId inner join DoctorFacility d on d.DoctorFacilityId = a.ResourceId where a.FacilityId in ('127','64') and p.MedicalRecordNumber = '00255599' and a.Status in ('Completed','Arrived') group by a.OwnerId,p.patientprofileid, p.searchname,f.listname, d.listname

I am trying to get the most recent date and the provider they saw, when I don't include the Provider in the group by, I get the results I need, see below:

Select a.ownerid, p.patientprofileid as MRN, p.searchname, max(a.EmrApptStart) as LastAppt, f.listname as facility from Appointments a left join patientprofile p on p.PatientProfileId= a.OwnerId inner join DoctorFacility f on f.DoctorFacilityId = a.FacilityId inner join DoctorFacility d on d.DoctorFacilityId = a.ResourceId where a.FacilityId in ('127','64') and p.MedicalRecordNumber = '00255599' and a.Status in ('Completed','Arrived') group by a.OwnerId,p.patientprofileid, p.searchname,f.listname MRN LastAppt facility ---------- -------------- -------------------- 255599 9/4/2014 0:00 Women's Health

If I include the Provider in my select statement, I get an error if I don't include it in the Group By clause. Any ideas?

Answer1:

Seems like you are interested on top 1 record with Provider. In that case, you could add Provider to the group by clause, select top 1 .... and use Order by a.EmrApptStart desc

Answer2:

SELECT a.PatientProfileId, a.MedicalRecordNumber, a.searchname, a.edcdate, a.wk , b.lastappt, c.NextAppt, c.NextProvider FROM (SELECT PatientProfile.PID, PatientProfile.PatientProfileID, PatientProfile.MedicalRecordNumber, PatientProfile.searchname, EDCDATE=case when isdate(max(RPTOBS.obsvalue))=1 then max(RPTOBS.obsvalue) end, WK=case when isdate(max(RPTOBS.obsvalue))=1 then 40-DATEDIFF(d,getdate(),max(RPTOBS.obsvalue))/7 end FROM OBSHEAD OBSHEAD INNER JOIN ((PatientProfile PatientProfile INNER JOIN DOCUMENT DOCUMENT ON PatientProfile.PId=DOCUMENT.PID) INNER JOIN RPTOBS RPTOBS ON (DOCUMENT.PID=RPTOBS.pid) AND (DOCUMENT.SDID=RPTOBS.sdid)) ON OBSHEAD.HDID=RPTOBS.hdid WHERE RPTOBs.hdid='8086' --and PatientProfile.PatientProfileID='255795' GROUP BY PatientProfile.PID,PatientProfile.PatientProfileID, PatientProfile.MedicalRecordNumber, PatientProfile.searchname)a INNER JOIN (SELECT DOCUMENT.PID, p.patientprofileid, LASTAPPT=DATEADD(day,max((DOCUMENT.CLINICALDATE)/1000000/3600/24),'1960-01-01') FROM DOCUMENT Inner join patientprofile p on document.pid=p.pid WHERE document.summary like '%Prenatal%' and DOCUMENT.DOCTYPE = '1' --and p.patientProfileID='255795' GROUP BY DOCUMENT.PID, p.patientprofileid) b ON a.pid = b.pid INNER JOIN (Select --TOP 1 a.ownerid,min(a.ApptStart) as NextAppt, d.listname as NextProvider FROM Appointments a INNER JOIN DoctorFacility d on d.DoctorFacilityId = a.ResourceId WHERE a.FacilityId in ('127','64') and a.Status = 'Scheduled' and a.ApptTypeId in ('15','10','181','25','24','263','235','89','90') --and a.ownerid = '255795' GROUP BY a.OwnerId, d.listname, a.ApptStart -- ORDER BY a.ApptStart ASC ) c ON a.PatientProfileID=c.ownerid and b.patientprofileid = c.ownerid WHERE EDCDATE>=cast(GETDATE() as DATE) GROUP BY a.PatientProfileId, a.MedicalRecordNumber, a.searchname,a.edcdate, a.wk, b.lastappt, c.NextAppt, c.NextProvider

Recommend

  • Open multiple excel files with user form
  • Pivoting table only returns 1 row
  • how to extract points from the string “(1,3),(4,6),(3,6)” using java
  • How do I create a call-by-need list with increasing size in Standard ML?
  • Can't ssh to AWS EC2: Identity file not accessible
  • how to get a list with words that are next to a specific word in a string in python
  • Writing a macro that contains a match body
  • How can I update a .yml file, ignoring preexisting Jinja syntax, using Python?
  • Using javascript regex to retrieve year from date string
  • Mysql stored procedure error 1064
  • How to update apollo cache after mutation (query with filter)
  • Removing back pointers with the use of explicit instantiation declarations causes std::bad_weak_ptr
  • Set runat=“server” from JavaScript
  • Given a substitution S and list Xs, how to apply S to Xs
  • Unable to Replicate “R for Beginners” Example
  • How to Eatchar in a multi-line Vim abbreviation
  • Cassandra CQL paging for Composite key
  • Override a private method in the lib folder of a gem
  • Passing command line arguments
  • WPF - MVVM Screen Management
  • Can i detect if a user canceled navigation from Google Maps App
  • How to capture enclosing scope in Runnable
  • How to replace TouchesBegan with UIGestureRecognizer
  • Asynchronous Sockets - Handling false socket.AcceptAsync values
  • Find longest path less than or equal to given value of an acyclic, directed graph in Python
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Row Count Is Returning the incorrect number using RaptureXML
  • Illegal mix of collations for operation for date/time comparison
  • Read text file and split every line in MSBuild
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Release, debug version and Authorization Google?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Buffer size for converting unsigned long to string
  • Hits per day in Google Big Query
  • embed rChart in Markdown
  • How to get Windows thread pool to call class member function?