*************************************************************************** * Description.......: GetMedianValue - returns median value from a specified table * Calling Samples...: GetMedianValue('TranMstr','LstSlPrice','Price between 100000 and 130000') * Parameter List....: pcTableName, pcField, pcWhere * Created by........: Daniel Rouleau - original idea, Andrew Coates - Median definition * Modified by.......: Nadya Nosonovsky 10/30/2000 01:06:58 PM ***************************************************************************** * Returns median value or .f. for unsuccessful cases lparameters pcTableName, pcField, pcWhere * pcTableName - name of the table or already opened cursor, which should be processed * pcFiled - name of the field, which used in calculation, price, for example * pcWhere - where expression suitable for macro * All parameters are optional, if they are not specified, current working alias is used and price field ** Check parameters first if empty(pcTableName) or vartype(pcTableName)<>'C' pcTableName=alias() && Current open alias if empty(pcTableName) && No current table return .f. endif endif if empty(pcField) or vartype(pcField)<>'C' pcField=upper('price') else pcField=upper(pcField) endif if empty(pcWhere) or vartype(pcWhere)<>'C' pcWhere='' else pcWhere='where '+pcWhere endif local lnMiddleRecord, lcCursor, lnOldSelect, lnMedianValue lnOldSelect=select() && Save current area lcCursor='cur'+sys(2015) && Unique name if used(lcCursor) && this should never happen use in (lcCursor) endif select &pcField from (pcTableName) ; &pcWhere ; order by 1 ; into cursor (lcCursor) nofilter if _tally>3 lnMiddleRecord=(_tally/2) && Find middle record else =messagebox('Number of records is less than 3. Can not calculate median...',48) if used(lcCursor) use in (lcCursor) endif select (lnOldSelect) && Return to the original area return .f. endif do case case int(lnMiddleRecord)==lnMiddleRecord && Even number of records local lnLowMedValue, lnHighMedValue go lnMiddleRecord lnLowMedValue = evaluate(lcCursor + '.' + pcField) skip && Go to the next record in sequence lnHighMedValue = evaluate(lcCursor + '.' + pcField) lnMedianValue = round((lnLowMedValue+lnHighMedValue)/2,0) && Average of the two middle numbers otherwise && Odd number of records go int(lnMiddleRecord)+1 lnMedianValue=evaluate(lcCursor + '.' + pcField) endcase use in (lcCursor) && Close cursor select (lnOldSelect) && Return to the original area return lnMedianValueThis is another version of this program. It works much faster, if we have a necessary index.
*************************************************************************** * Description.......: GetMedianValue - returns median value from a specified table * Calling Samples...: GetMedianValue('TranMstr','LstSlPrice','Price between 100000 and 130000') * Parameter List....: pcTableName, pcField, pcWhere * Created by........: Daniel Rouleau - original idea, Andrew Coates - Median definition * Modified by.......: Nadya Nosonovsky 10/30/2000 01:06:58 PM ***************************************************************************** * Returns median value or .f. for unsuccessful cases lparameters pcTableName, pcField, pcWhere * pcTableName - name of the table or already opened cursor, which should be processed * pcFiled - name of the field, which used in calculation, price, for example * pcWhere - where expression suitable for macro * All parameters are optional, if they are not specified, current working alias is used and price field ** Check parameters first if empty(pcTableName) or vartype(pcTableName)<>'C' pcTableName=alias() && Current open alias if empty(pcTableName) && No current table return .f. endif endif if empty(pcField) or vartype(pcField)<>'C' pcField=upper('price') else pcField=upper(pcField) endif if empty(pcWhere) or vartype(pcWhere)<>'C' pcWhere='' else pcWhere='where '+pcWhere endif local lnMiddleRecord, lcCursor, lnOldSelect, lnMedianValue lnOldSelect=select() && Save current area lcCursor='cur'+sys(2015) && Unique name if used(lcCursor) && this should never happen use in (lcCursor) endif select &pcField from (pcTableName) ; &pcWhere ; order by 1 ; into cursor (lcCursor) nofilter if _tally>3 lnMiddleRecord=(_tally/2) && Find middle record else =messagebox('Number of records is less than 3. Can not calculate median...',48) if used(lcCursor) use in (lcCursor) endif select (lnOldSelect) && Return to the original area return .f. endif do case case int(lnMiddleRecord)==lnMiddleRecord && Even number of records local lnLowMedValue, lnHighMedValue go lnMiddleRecord lnLowMedValue = evaluate(lcCursor + '.' + pcField) skip && Go to the next record in sequence lnHighMedValue = evaluate(lcCursor + '.' + pcField) lnMedianValue = round((lnLowMedValue+lnHighMedValue)/2,0) && Average of the two middle numbers otherwise && Odd number of records go int(lnMiddleRecord)+1 lnMedianValue=evaluate(lcCursor + '.' + pcField) endcase use in (lcCursor) && Close cursor select (lnOldSelect) && Return to the original area return lnMedianValue>Nadja,