Running total

[FB1]

Hi all

Crystal 11
using a sql view and crystal command
I have a report that show dates and weights

need to be able to show the difference in weight loss or gained.
(IDEALLY Would like it to take the pre date first and then show + or - from Post weight before the date of the pre weight from the next row.)

But do not not mind if it takes it from the row below.

I have the dates in desc order therefore "Post" date is on top and limit to 12 rows.
Would this need to be done via a running total and Please how
Thank you for all your time this would help me enormously
ralph

"Date" "Pre or Post" "Weight" "Weigt Diff"
09/05/2012, "", 85.00, I would like this to show +5,
04/01/2011, "Post", 80.00, -2,
04/01/2011, "Pre", 82.00, -7,
02/01/2011, "", , ,
10/11/2010, "Pre", 89.00, +5 etc,
10/11/2010 "Post" 85.00
08/11/2010 ""
13/10/2010 "" 86.00
30/09/2010 "" 81.90
07/09/2010 "" 110.00
06/09/2010 "" 120.00
10/08/2010 "" 88.15


this is my sql view called patwtht

select p_anthropometry . oid AS recid ,
p_anthropometry . fk_oid AS patientid ,
p_anthropometry . DIDAT AS date ,
p_anthropometry . PREPOST AS prepost , p_anthropometry . DIWT AS weight ,
p_anthropometry . DIBPS AS DIBPS , p_anthropometry . DIBPD AS DIBPD ,
p_anthropometry . DIGAIN AS DIGAIN , p_anthropometry . DIIW AS DIIW ,
p_anthropometry . DIHEIGHT AS DIHEIGHT ,
sp_bmi ( p_anthropometry . oid ) AS BMI from p_anthropometry
order by p_anthropometry . fk_oid , p_anthropometry . DIDAT desc

and in Crystal command

SELECT * FROM patwtht
where Patientid={?ThisPat}
limit 12;

 

[IanWaterman]

Instead of using a command and view just use this command
Puts pre and post in same row making your weight difference calculation much easier

select p_anthropometry . oid AS recid , 
p_anthropometry . fk_oid AS patientid , 
MAXIMUM(p_anthropometry . DIDAT) AS date , 
Maximum(Case when p_anthropometry . PREPOST = 'Pre' then p_anthropometry . DIWT AS weight else 0 end) as PreWeight,
Maximum(Case when p_anthropometry . PREPOST = 'Post' then p_anthropometry . DIWT AS weight else 0 end) as PreWeight,
p_anthropometry . DIBPS AS DIBPS , p_anthropometry . DIBPD AS DIBPD , 
p_anthropometry . DIGAIN AS DIGAIN , p_anthropometry . DIIW AS DIIW , 
p_anthropometry . DIHEIGHT AS DIHEIGHT , 
sp_bmi ( p_anthropometry . oid ) AS BMI 
from p_anthropometry 
where Patientid={?ThisPat}
group by p_anthropometry . oid, 
p_anthropometry . fk_oid, 
p_anthropometry . DIBPS, p_anthropometry . DIBPD , 
p_anthropometry . DIGAIN, p_anthropometry . DIIW, 
p_anthropometry . DIHEIGHT, 
sp_bmi ( p_anthropometry . oid )

Ian

 

[FB1]

Change the query around for Mysql
It nealy works except pre and post date will be in different rows with different oid.

the main problem there is no time field been populated, there it needs to see
the lastest pre date then look at the post date before the pre date

Pre = 200100
Post = 200101
ie

fk_oid, date Prepost weight weight difference days difference

12 12/12/2012 200100 69.2 0.8 2 days
12 10/12/2012 200101 70.0
12 10/12/2012 200100 70.0 6.9 7 days
12 03/12/2012 200101 76.9
12 03/12/2012 200100 76.8

How to I get it to check the next row or date for the calculation

Please find query adated to mysql

select p_anthropometry . oid AS recid , p_anthropometry . fk_oid AS patientid ,
MAX(p_anthropometry . DIDAT) AS date ,
Max(Case when p_anthropometry . PREPOST = '200100'
then p_anthropometry . DIWT -- AS weight
else 0 end) as PreWeight,
Max(Case when p_anthropometry . PREPOST = '200101'
then p_anthropometry . DIWT -- AS weight
else 0 end) as PostWeight,
p_anthropometry . DIBPS AS DIBPS , p_anthropometry . DIBPD AS DIBPD ,
p_anthropometry . DIGAIN AS DIGAIN , p_anthropometry . DIIW AS DIIW ,
p_anthropometry . DIHEIGHT AS DIHEIGHT , sp_bmi ( p_anthropometry . oid ) AS BMI
from p_anthropometry where p_anthropometry . fk_oid = '12'
group by p_anthropometry . fk_oid,
p_anthropometry . DIBPS, p_anthropometry . DIBPD ,
p_anthropometry . DIGAIN, p_anthropometry . DIIW,
p_anthropometry . DIHEIGHT, sp_bmi ( p_anthropometry . oid )
order by max(p_anthropometry . DIDAT) desc


it didn't seem to like the 'as weight' in the query
Max(Case when p_anthropometry . PREPOST = '200100'
then p_anthropometry . DIWT -- AS weight
else 0 end) as PreWeight,


Thank you once again for all your help, any ideas

Ralph

 

[IanWaterman]

Sorry that was careless of me did not spot the As Weight in Case, also saw that you spotted duplicate COL names, less haste more speed.

You can do something similar for the dates, I do not know syntax for MYSQL, so replace my To_date() with appropriate

Maximum(Case when p_anthropometry . PREPOST = 'Pre' then p_anthropometry . DIDAT else to_date('01/01/1900', 'DD/MM/YYYY') end) as PreDate

Would that help

Ian