Re: Function to calculate time between date

From: Stefan Rudolph-Klindtwort <StefanRudolph-Klindtwort_at_t-online.de>
Date: Thu, 9 Nov 2000 01:42:14 +0100
Message-ID: <8ucrv0$l1d$02$1_at_news.t-online.com>

Yep Yep,

assuming the warranty-cols are filled with months, I'm using the following test table :

Name                            Null?    Typ
------------------------------- -------- ----
CONTRACT_ID                     NUMBER(6) UNIQUE
PURCHASE_DATE                DATE
WARRANTY                          NUMBER(2)

EXTENDED_WARRANTY NUMBER(2) The following function is fast coded so optimize it :

create or replace
function f_get_expiration ( f_contract_id in number) return varchar2 is

cursor c_contract is
select trunc(purchase_date) , -- exact calculation without time  nvl(warranty,0) , -- regular warranty in month's  nvl(extended_warranty,0) -- extended warranty in month's from contract
where contract_id = f_contract_id;

l_purchase_date  date;
l_warranty  number(2);
l_ext_warranty  number(2);
l_total_warranty number(3);
l_date_expired  date;
l_result  varchar2(30) := null;
l_years   number(2);
l_months  number(2);
l_days   number(2);

begin

open c_contract;
fetch c_contract into l_purchase_date,

        l_warranty     ,
        l_ext_warranty ;

if

   c_contract%NOTFOUND
then

   close c_contract;
   return ( 'Contract ID not valid !' ); else

   close c_contract;
end if;

l_total_warranty := l_warranty + l_ext_warranty;

l_date_expired := add_months ( l_purchase_date, l_total_warranty );

if

   l_date_expired < trunc(sysdate) -- Assuming at last day warranty is still valid (<=)
then

   return ( 'Expired' );
else

   l_warranty := trunc(months_between ( l_date_expired, trunc(sysdate) ) );

   if

      l_warranty >= 12 -- We've at least 1 year    then

      l_years := trunc(l_warranty / 12 ); -- Number of years ...    end if;

   if

      l_warranty > 0 -- We have at least 1 month    then

      l_months := mod(l_warranty, 12 ); -- Rest in months    end if;

• Now we add the rest in Month's (l_warranty) to sysdate
• Using l_purchase_date because it's not nesessary anymore

   l_purchase_date := add_months ( trunc(sysdate), l_warranty );

   l_days := l_date_expired - l_purchase_date; -- Rest is days

• Decode the Years, 0 Years will not displayed

   if

      l_years > 1
   then

      l_result := to_char( l_years ) || ' Years';    elsif

      l_years = 1
   then

      l_result := '1 Year';
   end if;

• Same with Months and ... days

   if

      l_months > 1
   then

      l_result := l_result || ' ' || to_char( l_months ) || ' Months';    elsif

      l_months = 1
   then

      l_result := l_result || ' 1 Month';    end if;

   if

      l_days > 1
   then

      l_result := l_result || ' ' || to_char( l_days ) || ' Days';    elsif

      l_days = 1
   then

      l_result := l_result || ' 1 Day';
   end if;

• l_result := to_char( l_years ) || ' ' || to_char( l_months ) || ' ' || to_char( l_days );

   return (ltrim( l_result) ); -- ltrim is for leading bland i.e. no years but months or days ...

end if;

end f_get_expiration;
/

Try the following rows :

insert into contract values ( 10010, sysdate, 36, 0 ); --> Results in "3 Years"
insert into contract values ( 10020, sysdate + 1, 24, 12); --> Results in "3 Years 1 Day"

select contract_id , f_get_expiration ( contract_id ) Expiration from contract;

I wouldn't update the "End of warranty" column in your table, use the function instead.

Hope that helps

Greetinx

"C M" <cmarcoux_at_webnet.qc.ca> schrieb im Newsbeitrag news:OYgO5.239$Wd4.27296047_at_news1.mtl.metronet.ca...
> Hi.

>

> I Need to create a function that will calculate
> the time left for a warranty.
> help... Dont know how...

>

> Big thanks in avanced...
>
> Received on Thu Nov 09 2000 - 01:42:14 CET