Click here to Skip to main content
15,880,796 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
XML
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
SQL
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
Posted
Updated 29-Oct-14 21:01pm
v5
Comments
KaushalJB 30-Oct-14 3:24am    
Remove below from your inner query:

group by BP,mi.patient_id
ORDER BY patient_id
Jörgen Andersson 30-Oct-14 4:10am    
Please state what database and version you are using, this could be solved with analytic function "LEAD", but only if your DB supports it
Maciej Los 30-Oct-14 12:12pm    
Why BP is a varchar data type field?

check this

SQL
select a.patient_id,a.bp_type,a.encounter_id,a.BP,DIFF=iSNULL(SUM(B.BP-A.BP),0) from 
#bp_patient65 a
lEFT Join
#bp_patient65 b On a.patient_id=b.patient_id AND B.ID
Group by a.patient_id,a.bp_type,a.encounter_id,a.BP
 
Share this answer
 
v2
SQL
SELECT patient_id, bp_type,encounter_id,BP,
       COALESCE((SELECT TOP 1 BP FROM #bp_patient65 mi
       WHERE mi.patient_id = m.patient_id ORDER BY  patient_id ), 0) - BP AS diffrence
FROM  #bp_patient65 m group by patient_id,bp_type,encounter_id,BP ORDER BY patient_id
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900