Getting Min & Max Values

[samiam07]

Hi
Using XiR2, SQL-Server2005, XP-SP2

This may have been discussed before, however, nothing showed in my search. If it has, please direct me the appropriate thread.


We have a table that has ItemID, Change_Date, Old_Value and New_Value.
Old and New Values are attributes that can change from time to time.

The report is grouped on ItemID and in the group footer I need to show the earliest change date (Min), the Old_Value at the time of the earliest date, the most recent date (Max) and the New_Value at the time of the most recent date.

Getting the Min and Max of the date is a snap. I have a formula with a couple of variables to get the first old value and the last new value. (Final report may not be sorted by date, so taking the min/max of the value fields won’t work)

Is there a better (and faster) way to get the data for the min date row and the max date row into the group footer?

Thanks

 

[IanWaterman]

Create formula

@minOldVal
If changedate = minimum(changedate, itemId) then Old_value else 0

Change the else to string date or whatever depending on datatype of Old_value

Then repeat for
@maxOldVal
If changedate = maximum(changedate, itemId) then Old_value else 0

Place these in details and then do max and min summaries in ItemId footer.

Ian

 

[samiam07]

The two formulas you have summaries in them and summaries cannot be summarized.
i.e. you can't take the max of a formaula that uses max.

 

[IanWaterman]

Sorry you are right, you can use variables

@minOldVal
whileprintingrecords;

global numbervar oldvalmin;
If changedate = minimum(changedate, itemId) then oldvalmin
:=Old_value


Then repeat for
@maxOldVal
whileprintingrecords;

global numbervar oldvalmax;

If changedate = maximum(changedate, itemId) then oldvalmax
:=Old_value

In footer display vars
@Display maxOldVal
whileprintingrecords;

global numbervar oldvalmax;

Ian