Hello
I have a table
create table #bp_patient65(id int identity(1,1),patient_id int,bp_type varchar(50),encounter_id int,BP varchar(50))
insert into #bp_patient65 values(64874,'Systolic',146019,150),(64874,'Systolic',146020,130),
(64875,'Systolic',146022,150),(64876,'Systolic',146025,150),
(64876,'Systolic',146026,130),(64877,'Systolic',146028,150)
select * from #bp_patient65
drop table #bp_patient65
i want my output like that
patient_id---- bp_type------ encounter_id----- BP--------diff
64874---------- Systolic----- 146019-------- 150----- 0
64874---------- Systolic----- 146020-------- 130----- 20
64875---------- Systolic----- 146022-------- 150----- 0
64876---------- Systolic----- 146025-------- 150----- 0
64876---------- Systolic----- 146026-------- 130----- 20
64877---------- Systolic----- 146028-------- 150----- 0
I tried this but not getting right
SELECT patient_id, bp_type,encounter_id,BP,
COALESCE(
(
SELECT TOP 1 BP
FROM #bp_patient65 mi
WHERE mi.patient_id > m.patient_id
group by BP,mi.patient_id
ORDER BY patient_id
), 0) - BP AS diff
FROM #bp_patient65 m
group by patient_id,bp_type,encounter_id,BP
ORDER BY
patient_id