June 18, 2009 at 6:51 am
Not sure if my Post Subject is the greatest but here goes
My Problem is
I have a view set up (see end of this post). I need to get a Persons(PatID), First Visits Date (DOS) and most recent DOS, and the Last Dr(DRFname) they saw.
I have no issues getting these things except for the DR I do not know how to get only the last DR an Individual Saw. I thought i could possibly do max DOS with dr name but that just gave me duplicate patid's.
Thanks
Here's my query
Select PatientNames.Patid,PatientNames.LastName,
PatientNames.FirstName,Dos.Fdos,Dos.Ldos,lastdos.Dr
From
(SELECT distinct patid, FirstName,
LastName,substring(SSN,6,4) as SSNL4, ClinicName
FROM VwVisitDetails where clinicid='42' and dos between '5/1/2009' and '5/31/2009'
group by patid,firstname,lastname,clinicname,ssn) As PatientNames
Inner Join
(Select PatID,Min(DOS)As FDOS,Max(DOS)As LDOS
From VwVisitDetails where ClinicID='42'
Group By Patid) As DOS On Dos.PatID = PatientNames.PatID
Inner Join
(Select PatID,DrFName + ' ' + DrLname as DR
From VwVisitDetails where ClinicID='42'
Group By Patid,DrFname,DrLname) As LastDOS On LastDos.PatID = Dos.PatID
VWVisitDetails View
ALTER VIEW [dbo].[VwVisitDetails]
AS
SELECT PatientInfo.PatID,patientinfo.lastname as lname,patientinfo.firstname as fname, (CASE WHEN PatientInfo.Middle IS NULL
THEN PatientInfo.Firstname + ' ' + PatientInfo.Lastname ELSE PatientInfo.Firstname + ' ' + PatientInfo.Middle + ' ' + PatientInfo.Lastname END)
AS Name, PatientInfo.Suffix AS patSufx, PatientInfo.DOB, PatientInfo.SSN, PatientInfo.Address, PatientInfo.zipid,
(CASE WHEN dbo.Insurance.Company LIKE 'Unknown%' THEN 'Unknown' ELSE dbo.Insurance.Company END) AS InsCompany, Visits.VisitID,
Visits.DOS, Visits.LastUser, Visits.DateModified, Visits.DateEntered, Visits.AnnualVisit, Visits.Comments, PatientDetails.DOD, PatientDetails.Disenroll,
PatientDetails.DisEnrollDate, PatientDetails.FSD, PatientDetails.Deceased, PatientDetails.Medicare, Doctors.FirstName AS DrFname,
Doctors.LastName AS DrLname, Zips.city, Zips.state, Zips.zipcode, PatientDetails.Comments AS PatComments,
(CASE WHEN dbo.Visits.CLINICID IS NULL THEN dbo.PatientInfo.ClinicID ELSE dbo.Visits.CLINICID END) AS ClinicID, Clinics.ClinicName,
PatientInfo.Firstname, PatientInfo.Lastname, Clinics.ClinicStartDate, Clinics.ClinicEndDate
FROM PatientInfo LEFT OUTER JOIN
Visits ON PatientInfo.PatID = Visits.PatientID INNER JOIN
PatientDetails ON PatientInfo.PatID = PatientDetails.PatID INNER JOIN
Insurance ON PatientDetails.InsuranceID = Insurance.InsID LEFT OUTER JOIN
Doctors ON Visits.DRID = Doctors.DrID LEFT OUTER JOIN
Clinics ON Visits.CLINICID = Clinics.ClinicID INNER JOIN
Zips ON PatientInfo.zipid = Zips.ZipID
June 18, 2009 at 9:02 am
Try:
SELECT Q1.*, Q2.DrFName
FROM (SELECT Patid, LastName, FirstName, Min(dos) as MinDate, Max(dos) as MaxDate
FROM @VwVisitDetails
GROUP BY Patid, LastName, FirstName) AS Q1
INNER JOIN
(SELECT Patid, LastName, FirstName, Max(dos) as MaxDate, DrFName
FROM @VwVisitDetails
GROUP BY Patid, LastName, FirstName, DrFName) Q2 ON
Q1.MaxDate = Q2.MaxDate
June 19, 2009 at 10:29 am
Well that seemed to work, except that it took 5 minutes to execute! 🙁
Any suggestions? Here is what I did
Select PatientNames.Patid,PatientNames.LastName,PatientNames.FirstName,Dos.Fdos,Dos.Ldos,Q2.DR
From
(SELECT distinct patid, FirstName,--Middle,
LastName,--Suffix,
substring(SSN,6,4) as SSNL4, ClinicName
FROM VwVisitDetails where clinicid='42' and dos between '5/1/2009' and '5/31/2009'
group by patid,firstname,lastname,clinicname,ssn) As PatientNames
Inner Join
(Select PatID,Min(DOS)As FDOS,MAX(Dos) AS Ldos
From VwVisitDetails where ClinicID='42'
Group By Patid) As DOS On Dos.PatID = PatientNames.PatID
INNER JOIN
(SELECT Patid, Max(dos) as MaxDate, DrFName + ' ' + DRLname AS DR
FROM VwVisitDetails WHERE clinicid ='42'
GROUP BY Patid, DrFName,DRLname) Q2 ON
Dos.Ldos = Q2.MaxDate AND dos.patid = q2.patid
ORDER BY patientnames.LastName,PatientNames.FirstName
June 19, 2009 at 5:45 pm
You may want to index your view on the date of visit and patient id. Subqueries tend to run slower. Is the underlying table indexed - and if it is, how big is it - is it fragmented?
Just some things to check 🙂
June 19, 2009 at 7:56 pm
I can't index the view because it uses left joins.
Yes the underlying table has indexes, they are not fragmented.
Perhaps I'll try querying the underlying table directly and see what that does.
June 20, 2009 at 11:13 am
I got it to work, by changing my last query to select form the under lying table directly and not the view. I think the real issue is that in the view the visit table is a left join, I changed the query to an inner join for that specific table and that worked.
Thanks for the help
June 22, 2009 at 7:58 am
So glad you got it to work faster!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply