Formulae for calculating from one group to a next 1

[FB1]

A big thank you for everyones help

Have data that is recorded several time each month.
need to be able to pull out month difference

I know I can put them into a detail(as there can be 10-15 different entries each month), then group on date field monthly
but I need to be able to show the weight loss or gain from the month before
only need to look at pre entries

Ralph

Required
"Pre Date","Weigth","Change/Month",BMI
06/02/2012,"100.0 ","-0.2 loss",122
11/01/2012,"100.2 ","0.0 gain",123
16/12/2011,"100.2 ","0.2 gain",122
21/11/2011,"100.0 ","-0.2 loss",124
26/10/2011,"100.2 ","-0.2 loss",125
26/09/2011,"100.4 ","1.6 gain",132

the fields are called

oid
fk_oid (patients id)
didat (Date)
Prepost ( weather a pre rec or a post record)
diwt (weight)
BMI

 

[hilfy]

A couple of questions:

- Can there be multiple "Pre" entries in a month or just one?
- Do you need to show just the Pre entries on the report or do you need to show all records?

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.

 

[FB1]

Hi Dell

thank you for your reply

Will only need PRE dates
last one per PRE month

last 6 months

there will be multiple pre dates in each month.

Thank you for your help
Ralph

code at the moment

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;

 

[hilfy]

From your code, I'm going to assume that you're using a command for the report instead of just a table in Crystal. Here's some potential changes I would do to the command so that it will pull just the data you're looking for:

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 as pa1
where prepost = 'PRE'
and DIDAT = (
select max(pa2.DIDAT)
from p_anthropometry as pa2
where prepost = 'PRE'
and pa2.fk_oid = pa1.fk_oid
and trunc(pa2.DIDAT, 'MM') = trunc(pa1.DIDAT, 'MM')
and trunc(pa2.DIDAT, 'MM') >= add_months(trunc(sysdate, 'MM'), -7))
order by fk_oid, didat desc

NOTE: You didn't say what type of database you're using. I assumed Oracle. If it's not Oracle, you'll need to change the "trunc" syntax to whatever is appropriate for your database to convert the dates to the first day of the month for whatever month DIDAT is in and the "sysdate" to whatever gets the current date for your database.

After you make these changes, you'll something like the following in your report:

1. Group by patient
2. Sort by date descending (even though your data is already sorted - this makes sure that Crystal sees it that way...)
3. Create something like the following formula which I'll call {@WeightChange}:

if OnLastRecord then 0
else next({command.WEIGHT}) - {command.WEIGHT}

4. Put your data in the details section.
5. Go to the Section Expert and click on the Suppress formula button (do NOT check the Suppress checkbox!) Enter the following:

OnLastRecord

The reason for step 5 is that your query needs to return 7 months worth of data in order to get the weight change for the earliest month in the report. This will suppress the data for the month that is 7 months ago.

This should get you at least close to what you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[FB1]

Hi Dell
Thank you ever so much it gave me just what I wanted, having changing it to MYSQL.

if any one need the above query in MYSQL it is as follows
Ralph

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 as pa1
where prepost = '200100' and pa1.fk_oid = {?ThisPat}
and DIDAT = (
select max(pa2.DIDAT)
from p_anthropometry as pa2
where prepost = '200100' and pa2.fk_oid ={?ThisPat}
and pa2.fk_oid = pa1.fk_oid
and DATE_SUB(pa2.didat,INTERVAL (DAY(pa2.didat)-1) DAY) =
DATE_SUB(pa1.didat,INTERVAL (DAY(pa1.didat)-1) DAY)
and DATE_SUB(pa2.didat,INTERVAL (DAY(pa2.didat)-1) DAY) >=

Again thank you ever so much Dell
adddate(DATE_SUB(didat,INTERVAL (DAY(curdate())-1) DAY) , -7))
order by fk_oid, didat desc limit 7