Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help please returning value from row below into top row

Status
Not open for further replies.
FB1

FB1

Instructor
Joined
May 31, 2005
Messages
69
Location
GB
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

 
Sort by date Sort by votes
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 :
Code: 
[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/][/url]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Hi

Code: 
[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/][/url]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question Question
function argument value, type or count is invalid
Replies
3
Views
933
bharons
bharons
Loomis
  • Locked
  • Question Question
stepping through table rows
Replies
7
Views
812
rashmibala
R
waubain
  • Locked
  • Question Question
Show rows results on same row when Group BY fields
Replies
6
Views
853
SkipVought
S
jawadfrhad
  • Locked
  • Question Question
how make row to column
Replies
4
Views
523
jawadfrhad
jawadfrhad
waubain
  • Locked
  • Question Question
mysqli_multi_query not not returning any results
Replies
2
Views
754
waubain
waubain

Part and Inventory Search

Sponsor

Back
Top