Comparing detail records

[Dross]

I know I have done this before but can't remember how.

I have multiple records coming back. The values are simple: Client, yesterday's value, today's value. Problem is they are coming on seperate detail lines so it looks like this

John Doe YESTERDAY VALUE
John Doe TODAY VALUE

I want it to look like
John Doe YESTERDAY VALUE TODAY VALUE

So I can then compare the values. How can I get values on the same detail line when returning as seperate records in SQL?

 

[IanWaterman]

Group data on Client.

Add Max summaries of yesterday value and today value to group footer.

To compare thos values use a formual

@variance

maximum(YESTERDAY VALUE, client) - maximum(ToDAY VALUE, client)

Ian

 

[Dross]

Thanks, I'll try that.

 

[Dross]

Unfortunately that won't work for me. this is what the actual data would look like:

NAME VALUE DATE LOOKUP VALUE
John Doe 650 6/9/2011 200
John Doe 650 6/9/2011 201
John Doe 670 6/8/2011 200
John Doe 490 6/8/2011 201
John Doe 375 6/8/2011 200
John Doe 700 6/8/2011 201
John Doe 710 6/8/2011 200
John Doe 644 6/8/2011 201

I need to compare the 2nd and 3rd records only. I know it sounds strange but that is how the data is. Basically the 200 is what the next value for the client should be and the 201 is the last value given to the client. So I need to do the 2nd and 3rd because the 1st is what should be given the next time the client is due based on the 201 record. I have to compare the 2nd and 3rd so we can see if the recommended value is the same as the last value given or not and adjust.

 

[lbass]

There is nothing in your sample that indicates a sequence. So is your datefield really a datetime? Or is there some other sequence field that would indicate which are the appropriate values to compare? What field is ordering the data? It might help to know conceptually what 200 and 201 mean to you.

-LB

 

[Dross]

200 values are the reccomended dose records of medication and the 201 records are the actual last dose given.

The first record in my example will be the next dose due but would have to be ignored.

it's kind of confusing but the 1st and 2nd record will always have the same datetime so I need to ignore the 1st.

 

[fisheromacse]

Dross

is this correct?
if you group by datetime and sort descending, you will have 2 records per group for all but one group. one record for the rec. dose (200) and one record for the actual dose (201). the exception to the 2 records per group would be the most recent datetime (the 1st group), as it will only have 1 record in it's group, the next rec. dose.
You want to compare the actual dose record (201) from the first group with 2 records (which should be the 2nd group overall) to the rec. dose record (200) from the second group with 2 records (which should be the 3rd group overall)

have a confused it terribly?

 

[Dross]

I could have 2 records or 2000 records depending on how long this person has been on these meds.

No comparison by groups. Grouped by clients and then sorted by datetime.

So the latest record will be what the recommended dose should be when it is time to give the medication. Those are the 200 records.

The 2nd record would be the last dose actually given. Since we can't compare what was given to what should be given next time I have to go to the next pairing which will be what was actually given the last time to what was recommended the time before that.

This is why I need to actually compare the 2nd record against the 3rd record to see what was given VS what was recommended.

 

[lbass]

You should have identified the field as a datetime to begin with and stated that they are sorted in descending order, so that we could understand the sequence. Create these formulas:

//{@reset} for the client group header:
whileprintingrecords;
numbervar curr;
numbervar prev;
numbervar cnt;
if not inrepeatedgroupheader then (
curr := 0;
prev := 0;
cnt := 0
);

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar curr;
numbervar prev;
numbervar cnt := cnt + 1;
if cnt = 2 then
curr := {table.value};
if cnt = 3 then
prev := {table.value};

//{@result} to be placed in the client group footer:
whileprintingrecords;
numbervar curr;
numbervar prev;
"Yesterday's Value: "+totext(prev,0,"")+
"Today's Value: "+totext(curr,0,"")

This assumes that the records are always ordered by 200, 201, 200, etc., and that the records you want are ALWAYS in the second and third records.

You can then suppress the detail section and drag the groupname into the client group footer.

-LB