hazz


Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm

The result of the query should only return 3 rows/records,not 5. Thank you. Greg

Code Snippet

select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age

,pv.WeightPounds, pv.HeightInches

from Patients as p

inner join PatientVisits as pv

ON p.ID = PV.PatientID

order by pv.VisitDate desc

INSERT INTO Patients (ID, FirstName,LastName,DOB)

select '1234-12', 'Joe','Smith','3/1/1960'

union

select '5432-30','Bob','Jones','3/1/1960'

union

select '3232-22','Paul','White','5/12/1982'

INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)

select '1234-12', '10/11/2001','180','68.5'

union

select '1234-12', '2/1/2003','185','68.7'

union

select '5432-30','11/6/2000','155','63.0'

union

select '5432-30','5/12/2001','165','63.0'

union

select '5432-30','4/5/2000','164','63.5'

union

select '3232-22','1/17/2002','220','75.0'

CREATE TABLE [dbo].[Patients](

[PID] [int] IDENTITY(1,1) NOT NULL,

[ID] [varchar](50) NULL,

[FirstName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

[DOB] [datetime] NULL,

CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[PatientVisits](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PatientID] [nvarchar](50) NULL,

[VisitDate] [datetime] NULL,

[WeightPounds] [numeric](18, 0) NULL,

[HeightInches] [decimal](18, 0) NULL

) ON [PRIMARY]




Re: return only the most recent record/row in a table

Vidhura


Code Snippet

select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age

,pv.WeightPounds, pv.HeightInches

from Patients as p

left join PatientVisits as pv

ON p.ID = PV.PatientID

Where PV.ID in (select max(ID) from PatientVisits

group by PatientID)

order by pv.VisitDate desc






Re: return only the most recent record/row in a table

Sankar Reddy

Your input data needs some modification and you need to test with data that is feasible to be in production. How can a patient visit 3 times in a day with different weights and height. You need more than a simple select to do this and I like to hear from other people to do this more elegantly.

Code Snippet

declare @t table ( PatientID varchar(50),

VisitDate datetime,

WeightPounds decimal(18, 0),

HeightInches decimal (18, 0))

insert @t

select PatientID, max(visitdate) latest_visitdate, WeightPounds, HeightInches

from PatientVisits

group by patientID, WeightPounds, HeightInches

DELETE t

FROM PatientVisits PV

INNER JOIN @t t

ON PV.PatientID = t.PatientID

AND PV.VisitDate > t.VisitDate

select * from @t






Re: return only the most recent record/row in a table

hazz

Thank you Vidhura! Yes, a group by... and selecting the max ID from that group of ordered visits by date.

Yes, a left join would return all Patients, even if there were no visits, correct

It would show the Patient but no vital statistics.





Re: return only the most recent record/row in a table

hazz

Oops! I am sorry for the data I provided with 3 dates the same for one patient and different weights/heights. I corrected that above.

Thank you for the working example Sankar using the temp table!





Re: return only the most recent record/row in a table

Vidhura

No,it wn't return Patient if there were no visits for that patient

because we mentioned as

"

ON p.ID = PV.PatientID

Where PV.ID in (select max(ID) from PatientVisits

group by PatientID)

"




Re: return only the most recent record/row in a table

hazz

You are right. I was thinking about the use of the left outer join rather than an inner join which was my initial choice.

How would I change the following tsql to get all records from PatientVisits for one specific patient rather than the list of patients that contained only the most recent one that the following solved

Should I create a new thread for this question

Thank you!

select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age

,pv.WeightPounds, pv.HeightInches

from Patients as p

left join PatientVisits as pv

ON p.ID = PV.PatientID

Where PV.ID in (select max(ID) from PatientVisits

group by PatientID)

order by pv.VisitDate desc

The following DID NOT work;

SELECT *

FROM Patients.Patients p

WHERE EXISTS

(SELECT *

FROM Patients.PatientVisits pv

WHERE p.PID = pv.ID





Re: return only the most recent record/row in a table

Vidhura

Are you passing the Username/ID from UI

Sample Code:

Code Snippet

select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age

,pv.WeightPounds, pv.HeightInches

from Patients as p

inner join PatientVisits as pv

ON p.ID = PV.PatientID

Where p.ID = '1234-12'

order by pv.VisitDate desc





Re: return only the most recent record/row in a table

hazz

Beautful, simple and it works! Thank you..!

Yes Vidhura, the ID will be passed in from the UI.

These will become stored procedures.

Appreciatively,

Greg