Help please returning value from row below into top row

[FB1]

Hi everyone, newbie to this as you can see from my last correspondence.

have a table which show rows 'patient id', 'date' , 'weight', 'post or pre'

need to do a query that show last date entry, pre weight at the time of that entry, also the post weight of the entry before, then I can calculate weight +/-.

I will need to show the last 12 entries for that patient. the paient gets weighed 2 in a day, pre before treatment and post after treatment. therefore it will look at 25 entries to do teh calculation.

Your help is most appreciated

Ralph

 

[feherke]

Hi

You mean, the date field should have the same value in both the pre and post records ?

Given that you wrote "25 entries", I suppose you also want the last pre measurement which has no post pair yet. In such circumstances I prefer to use 2 sub-[tt]select[/tt]s :

[b]select[/b]
date[teal],[/teal]pre[teal].[/teal]weight[teal],[/teal]post[teal].[/teal]weight[teal],[/teal]pre[teal].[/teal]weight[teal]-[/teal]post[teal].[/teal]weight

[b]from[/b] [teal]([/teal]
  [b]select[/b]
  [teal]*[/teal]
  [b]from[/b] f1

  [b]where[/b] post_or_pre[teal]=[/teal][green][i]'pre'[/i][/green]
  [b]and[/b] patient_id[teal]=[/teal][purple]1[/purple]

  [b]order[/b] [b]by[/b] date [b]desc[/b]

  [b]limit[/b] [purple]12[/purple]
[teal])[/teal] pre
[b]left[/b] [b]join[/b] [teal]([/teal]
  [b]select[/b]
  [teal]*[/teal]

  [b]from[/b] f1

  [b]where[/b] post_or_pre[teal]=[/teal][green][i]'post'[/i][/green]
  [b]and[/b] patient_id[teal]=[/teal][purple]1[/purple]
[teal])[/teal] post [b]using[/b] [teal]([/teal]patient_id[teal],[/teal]date[teal])[/teal]

[b]order[/b] [b]by[/b] date

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[FB1]

Hi Feherke

This is my Query for pulling information out
select
oid as recid,
fk_oid as patientid,
DIDAT as date,
prepost,
DIWT as weight,
DIBPS, DIBPD, DIGAIN, DIIW, DIHEIGHT, sp_bmi(oid) as BMI


from p_anthropometry
where fk_oid = '3'

order by fk_oid, didat desc
this gives me
recid ,fk_oid, date , Prepost , weight
'188856', '3', '1982-02-26 00:00:00', '200100', '71.8', '150', '60', NULL, NULL, NULL, NULL
'188855', '3', '1982-02-26 00:00:00', '200101', '69.3', '120', '60', NULL, NULL, NULL, NULL
'188858', '3', '1982-02-24 00:00:00', '200100', '72.3', '145', '65', NULL, NULL, NULL, NULL
'188857', '3', '1982-02-24 00:00:00', '200101', '70.5', '120', '60', NULL, NULL, NULL, NULL
'188859', '3', '1982-02-22 00:00:00', '200101', '71.1', '120', '60', NULL, NULL, NULL, NULL
'188860', '3', '1982-02-22 00:00:00', '200100', '73.3', '145', '60', NULL, NULL, NULL, NULL
'188862', '3', '1982-02-19 00:00:00', '200100', '73.4', '150', '60', NULL, NULL, NULL, NULL
'188861', '3', '1982-02-19 00:00:00', '200101', '71.3', '120', '60', NULL, NULL, NULL, NULL
'188864', '3', '1982-02-17 00:00:00', '200100', '73.5', '160', '60', NULL, NULL, NULL, NULL
'188863', '3', '1982-02-17 00:00:00', '200101', '72.1', '125', '60', NULL, NULL, NULL, NULL
'188866', '3', '1982-02-15 00:00:00', '200100', '74', '150', '60', NULL, NULL, NULL, NULL
'188865', '3', '1982-02-15 00:00:00', '200101', '71.8', '125', '60', NULL, NULL, NULL, NULL
'188868', '3', '1982-02-12 00:00:00', '200100', '74', '165', '60', NULL, NULL, NULL, NULL
'188867', '3', '1982-02-12 00:00:00', '200101', '71.8', '125', '60', NULL, NULL, NULL, NULL
'188870', '3', '1982-02-10 00:00:00', '200100', '74.2', '150', '60', NULL, NULL, NULL, NULL
'188869', '3', '1982-02-10 00:00:00', '200101', '72.3', '120', '60', NULL, NULL, NULL, NULL
'188872', '3', '1982-02-08 00:00:00', '200100', '75', '155', '60', NULL, NULL, NULL, NULL
'188871', '3', '1982-02-08 00:00:00', '200101', '73', '125', '60', NULL, NULL, NULL, NULL
'188874', '3', '1982-02-05 00:00:00', '200100', '75', '155', '60', NULL, NULL, NULL, NULL
'188873', '3', '1982-02-05 00:00:00', '200101', '73.5', '130', '60', NULL, NULL, NULL, NULL
'188876', '3', '1981-12-16 00:00:00', '200100', '74', '165', '60', NULL, NULL, NULL, NULL
'188875', '3', '1981-12-16 00:00:00', '200101', '72.8', '130', '60', NULL, NULL, NULL, NULL


200100 = 'Pre'
200101 = 'Post'

I need to show the following

'Date' 'Preweight' ' Last Post weight before preweight i.e before preday' 'difference between preweight and last post weight' 'Interval between Prewieght and last post weight dates'



so it looks like this

Date PreWt lastpostwt IDFG Interval
08/02/2012 101.6 100.0 1.6 2 days
06/02/2012 100.3 100.3 1.2 3 days
01/02/2012 101.4 99.8 1.6 2 days
to show last 6

 

[feherke]

Hi

[b]select[/b]
pre[teal].[/teal]fk_oid[teal],[/teal]
pre[teal].[/teal]didat pre_date[teal],[/teal]
pre[teal].[/teal]diwt pre_weight[teal],[/teal]
post[teal].[/teal]didat post_date[teal],[/teal]
post[teal].[/teal]diwt post_weight[teal],[/teal]
datediff[teal]([/teal]pre[teal].[/teal]didat[teal],[/teal]post[teal].[/teal]didat[teal])[/teal] date_difference[teal],[/teal]
pre[teal].[/teal]diwt[teal]-[/teal]post[teal].[/teal]diwt weight_difference

[b]from[/b] [teal]([/teal]
  [b]select[/b]
  [teal]*,[/teal]
  [teal]([/teal]
    [b]select[/b]
    didat

    [b]from[/b] p_anthropometry

    [b]where[/b] prepost[teal]=[/teal][green][i]'200101'[/i][/green]
    [b]and[/b] didat[teal]<[/teal]pre[teal].[/teal]didat
    [b]and[/b] fk_oid[teal]=[/teal]pre[teal].[/teal]fk_oid

    [b]order[/b] [b]by[/b] didat [b]desc[/b]

    [b]limit[/b] [purple]1[/purple]
  [teal])[/teal] post_date

  [b]from[/b] p_anthropometry pre

  [b]where[/b] prepost[teal]=[/teal][green][i]'200100'[/i][/green]

  [b]order[/b] [b]by[/b] didat [b]desc[/b]

  [b]limit[/b] [purple]6[/purple]
[teal])[/teal] pre
[b]left[/b] [b]join[/b] p_anthropometry post [b]on[/b] post[teal].[/teal]didat[teal]=[/teal]pre[teal].[/teal]post_date
[b]and[/b] post[teal].[/teal]fk_oid[teal]=[/teal]pre[teal].[/teal]fk_oid
[b]and[/b] post[teal].[/teal]prepost[teal]=[/teal][green][i]'200101'[/i][/green]

[b]order[/b] [b]by[/b] pre[teal].[/teal]didat

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[FB1]

That is brilliant thank you ever so much feherke.

I need to create this as a view, because there are several sub queries, I am trying to break them down.
This is your query, change it a little to show newest date. I need to run this query against each patient, ( at the moment just going against date)
I will then join the view's and add it as cr command as a sub report
Could I please ask for you to give me a bit more help.

select pre.fk_oid,
pre.didat pre_date,
pre.diwt pre_weight,
post.didat post_date,
pre.didat,
post.diwt post_weight,
datediff(pre.didat,post.didat) date_difference,
pre.diwt-post.diwt weight_difference
from ( select *,
( select didat from p_anthropometry
where prepost='200100'
and didat<pre.didat
and fk_oid=pre.fk_oid
order by didat desc limit 1 )
post_date
from p_anthropometry pre
where prepost='200100' order by didat desc limit 12) pre
left join p_anthropometry post on post.didat=pre.post_date
and post.fk_oid=pre.fk_oid
and post.prepost='200101'
-- where pre.fk_oid = '12'
order by pre.didat desc


//I have created a view for Pre_date_hdObs
select pre.fk_oid, pre.prepost,
pre.didat pre_date,
pre.diwt pre_weight
from p_anthropometry as pre
where prepost='200100'
-- and didat<pre.didat
and fk_oid=pre.fk_oid
order by pre.didat desc

//also view for Post_date_HdObs
Select post.fk_oid, post.didat post_date,
post.diwt post_weight, prepost
from p_anthropometry as post
where post.prepost='200101'
--and post.fk_oid = '12'
order by post.didat desc

But now I am lost, I tried to do the following
SELECT pre_date_hdobs.*,
post_date_hdobs.post_date,
post_date_hdobs.post_weight,
datediff(pre_date_hdobs.Pre_date,post_date_hdobs.Post_date) as date_difference,
pre_date_hdobs.Pre_weight - post_date_hdobs.Post_weight as weight_difference
FROM pre_date_hdobs
join post_date_hdobs on post_date_hdobs.fk_oid=pre_date_hdobs.fk_oid;

Again thank you for all your help, would most appreciated a little bit more

Ralph

 

[FB1]

Hi Ferherke

Manage to get it working on single patient, thank you very much for all your help.

If I wanted to group in each last Pre entry in a month and show the weight difference for each month.
what do I need to change
Date Weigth Change/Month
06/02/2012 100.0 -0.2 loss
11/01/2012 100.2 0.0 gain
16/12/2011 100.2 0.2 gain
21/11/2011 100.0 -0.2 loss
26/10/2011 100.2 -0.2 loss
26/09/2011 100.4 1.6 gain

Your help is most appreciated
Thank you once again
Ralph

your code:
select pre.fk_oid,
pre.didat pre_date,
pre.diwt pre_weight,

post.didat post_date,
pre.didat,
post.diwt post_weight,
datediff(pre.didat,post.didat) date_difference,
pre.diwt-post.diwt weight_difference
from ( select *,
( select didat from p_anthropometry
where prepost='200100'
and didat<pre.didat
and fk_oid=pre.fk_oid
order by didat desc limit 1 )
post_date
from p_anthropometry pre
where prepost='200100' and pre.fk_oid = '12'
order by didat desc limit 12) pre
left join p_anthropometry post on post.didat=pre.post_date
and post.fk_oid=pre.fk_oid
and post.prepost='200101'
and pre.fk_oid = '12'
and post.fk_oid = '12'
order by pre.didat desc