Displaying the most recent data record by patient

Question

Displaying the most recent data record by patient

Mr or Mrs. 500

Points: 542

May 29, 2019 at 12:48 pm

Apologies for the newbie question. I've got two tables, one with patients and one with observations collected on them. I want to be able to query the two tables to extract one patient per row with their most recent observations. Example tables can be created with the following code:

CREATE TABLE [dbo].[Patient](
[ID] [nchar](10) NOT NULL,
[Surname] [varchar](100) NULL,
[Bed] [nchar] (10) NULL,
[OpDate] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.Patient 
VALUES('1','Smith','10','2019-21-01 12:00:00'),
('2','Jones','11','2019-21-01 12:00:00'),
('3','Thomas','12','2019-21-01 12:00:00'),
('4','Johnson','13','2019-20-01 12:00:00');

CREATE TABLE [dbo].[PatData](
[ID] [nchar](10) NOT NULL,
[BP] [nchar](10) NULL,
[Temp] [nchar] (10) NULL,
[SampleDate] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.PatData 
VALUES('1','70','36','2019-21-01 12:00:00'),
('1','75','37','2019-21-01 12:00:30'),
('1','71','36','2019-21-01 12:01:00'),
('1','78','37','2019-21-01 12:01:30'),
('2','89','34','2019-21-01 12:00:00'),
('2','87','34','2019-21-01 12:00:30'),
('2','85','35','2019-21-01 12:01:00'),
('2','80','35','2019-21-01 12:01:30'),
('3','95','37','2019-21-01 13:00:00'),
('3','91','37','2019-21-01 13:00:30'),
('3','94','37','2019-21-01 13:01:00'),
('3','89','37','2019-21-01 13:01:30'),
('4','65','33','2019-20-01 14:00:00'),
('4','68','33','2019-20-01 14:00:30'),
('4','69','33','2019-20-01 14:01:00'),
('4','72','34','2019-20-01 14:01:30');

My expected output would be one row for each patient where the OpDate is TODAY (21 Jan 2019 in this example) displaying the following fields:

ID, Surname, Bed, OpDate, BP, Temp

I want to be able to run my query and see the most current state of my patients at the time the query runs. I have tried using "TOP 1" from the observations but I can't seem to then pull out each patient, on the specified date, and just the most current row from the observations in table PatData.

I'd appreciate any help, Ian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

In order to get the most recent record of each patient from Patdata you can use something like below

;WITH cte_p AS 
(
SELECT p.id , surname , bed , pd.Temp  , bp ,  sampledate   ,  opdate , ROW_NUMBER() OVER (PARTITION  BY p.id ORDER BY pd.SampleDate DESC) AS rn  FROM #PatData AS pd 
INNER JOIN #Patient AS p
ON p.ID = pd.ID ) 
SELECT * FROM cte_p WHERE rn = 1

pietlinden SSC Guru Points: 63506 More actions · Answer 2

Or use CROSS APPLY... but the above is probably easier.

SELECT p.Id
  , p.Surname
  , p.Bed
  , p.OpDate
  , patientData.SampleDate
  , patientData.BP
  , patientData.Temp
FROM Patient p
CROSS APPLY (SELECT TOP 1 ID, BP, Temp, SampleDate
   FROM PatData pd
   WHERE pd.ID = p.ID
   ORDER BY SampleDate) patientdata;

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 3

A huge thank you @Taps and pietlinden, both have done just what I needed and shown me two different ways to solve the problem. Thanks again.

autoexcrement SSCertifiable Points: 5956 More actions · Answer 4

pietlinden wrote:

Or use CROSS APPLY... but the above is probably easier.

SELECT p.Id
  , p.Surname
  , p.Bed
  , p.OpDate
  , patientData.SampleDate
  , patientData.BP
  , patientData.Temp
FROM Patient p
CROSS APPLY (SELECT TOP 1 ID, BP, Temp, SampleDate
   FROM PatData pd
   WHERE pd.ID = p.ID
   ORDER BY SampleDate) patientdata;

I think it would be ORDER BY SampleDate DESC

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura