Skip site navigation (1) Skip section navigation (2)

numeric rounding

From: Gezeala 'Eyah' "Bacuo" II <gezeala25(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: numeric rounding
Date: 2003-09-29 07:47:05
Message-ID: 20030929074705.17918.qmail@web41405.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
hey guys..need your help on this..

i have a plpgsql function where in i compute numeric values for my php scripts..

my problem is my function just won't round some numbers properly..

what i want it to do is like this.

example:

721.875 = 721.88
721.865 = 721.87
721.765 = 721.77
721.775 = 721.78


here's my function which returns numeric(12,2): 

CREATE OR REPLACE FUNCTION fn_adjust_accum_dep(int4,int4,numeric(12,2)) RETURNS numeric(12,2) AS '

DECLARE

  fxamid ALIAS FOR $1;
  -- life is in months
  life ALIAS FOR $2;
  acqamt ALIAS FOR $3;
  depmonth int4;
  depyear int4;
  depdate date;
  lastdepdate date;
  dyear int4;
  dmon int4;
  manth int4;
  manthlife int4;
  depexpense numeric(12,2);
 salvagevalue float4;
BEGIN 
 
 SELECT 
  EXTRACT(month FROM fxam_acquisition_date), 
  EXTRACT(year FROM fxam_acquisition_date), 
  fxam_dep_date, 
  fxam_salvage_value/100 
 INTO 
  depmonth, 
  depyear, 
  lastdepdate, 
  salvagevalue 
 FROM fixed_asset_master WHERE fxam_id = fxamid;
  
 -- for Month of December 
 IF  (depmonth = 12) THEN
  --Next year
  depyear := depyear + 1;
  --January the following year
  depmonth := 1;
 
 ELSE
  depmonth := depmonth + 1;
 
 END IF;
 
 -- first depreciation date of property based on acquisition date
 depdate := depmonth || ''/1/'' || depyear;

 -- RAISE NOTICE ''depdate = %'', depdate;


 -- get number of month and years from first depreciation date to last depreciation date 
 SELECT 
  EXTRACT(month FROM AGE(lastdepdate,depdate::DATE)), 
  EXTRACT(year FROM AGE(lastdepdate,depdate::DATE)) 
 INTO dmon,dyear;
 
 -- RAISE NOTICE ''dmon=%,dyear=%,depdate=%'',dmon,dyear,depdate;
 
 -- Number of months to depreciate
 manth := (dyear * 12) + dmon;
 -- Number of months of estimated life
 manthlife := life;

 -- Number of months to depreciate is greater than number of months of estimated life
 -- Only happens when property is encoded late and life is already consumed during first depreciation.. 
 IF ( dyear >= 0 AND manth > manthlife ) THEN
  
  -- Monthly depreciation expense Multiplied by number of month since Acquisition date

  depexpense := (acqamt - (acqamt * salvagevalue)) +  0.00000001;

  RAISE NOTICE ''manth = % > manthlife = %, depexpense=%'',manth, manthlife,depexpense;
  
 ELSE
 
  -- Monthly depreciation expense Multiplied by number of month since Acquisition date
  
  -- depexpense := ((acqamt - (acqamt * salvagevalue)) / life) * manth;
  depexpense := (((acqamt - (acqamt * salvagevalue)) / life) * manth) +  0.00000001;

  -- sample data :
  -- depexpense := (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001 
  -- returns 721.87
  -- should return 721.88 
  

  -- RAISE NOTICE ''manth = % < manthlife = %, depexpense=%'',manth, manthlife,depexpense;

 END IF;

RETURN depexpense;                                         

END;
'LANGUAGE 'plpgsql';

 

This function is up and running but my boss is such a great debugger.. ;)

You can see I already added a value of 0.00000001 to the computation but I also get the same results.. :(

I know that numeric data type automatically rounds off values but how come it's not returning the right values???

    



Marie Gezeala M. Bacuo II 
IS Department
Muramoto Audio-Visual Phils., Inc.
MEPZ1, Lapu-Lapu City, Cebu, Philippines 6015


The person with the ultimate cachinnation possesses, thereby, the optimal cachinnation.




---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

Responses

pgsql-general by date

Next:From: Andreas PflugDate: 2003-09-29 09:04:38
Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)
Previous:From: Shridhar DaithankarDate: 2003-09-29 07:42:00
Subject: Re: Result set granularity..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group