Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

oracle function to get percentge of shelf life remaining

 select
1 -
( (shelf_exp_date-trunc(sysdate))
     / (add_months(trunc(sysdate),60) - trunc(sysdate)) )
 from built_items where bar_code = 33502238


select shelf_exp_date, trunc(sysdate), shelf_exp_date-trunc(sysdate)
from built_items where bar_code = 33502238;

 
 select add_months(trunc(sysdate),60) - trunc(sysdate)
from dual;

Open in new window


shelf life
I have included a screen print that shows the shelf life. It may be years, days, weeks etc. So I want one function that can take input of shelf life and convert to days and get the percentage shelf life remaining. Help appreciated.
0
anumoses
Asked:
anumoses
3 Solutions
 
johnsoneSenior Oracle DBACommented:
Given the information you have given us, this would be a function to return the number of days remaining:
CREATE OR replace FUNCTION Days_remaining (expire_date DATE) 
RETURN NUMBER 
AS 
BEGIN 
    RETURN ( expire_date - Trunc(SYSDATE) ); 
END; 

/ 

Open in new window

You need to figure out what you want to do with fractions of a day (unless the expire date is always midnight as a time).

If you want days remaining and percentage of life left, then you need to supply two of these three pieces of information; date put on the shelf, shelf life (need to know units too), date expires.  I only see one of those pieces of information, unless shelf life is always 5 years.

Also, a function can only return one value, so if you want 2 values, you would have to use either 2 functions or a function (or a procedure) that contains OUT parameters.
0
 
anumosesAuthor Commented:
For any given part in the inventory the shelf life of the part is always one. I mean either years, months, weeks or days. So for the above function, will this function return days for any expiration date I give? I mean I need to get expiry date for that part that is in the built items table.
0
 
johnsoneSenior Oracle DBACommented:
Given your sample queries, the expiration date of a part is stored in the SHELF_EXP_DATE, correct?  If you pass that field into the function it will return number of days remaining.  I took your query and literally put it in a function.  If your sample queries are incorrect, then I need more information.

The easiest way to find out if it works, it to try it.  Create the function and the run:

select shelf_exp_date, trunc(sysdate), shelf_exp_date-trunc(sysdate), days_remaining(shelf_exp_date)
from built_items where bar_code = 33502238;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johnsoneSenior Oracle DBACommented:
Since you stated that life can be days, weeks, months, or years, this function should be able to handle all those situations.  I tried to test it as much as I could.  It returns values that seem correct and errors when it needs to.
CREATE OR replace FUNCTION Pct_remaining (expire_date       DATE, 
                                          shelf_life_days   NUMBER DEFAULT NULL, 
                                          shelf_life_weeks  NUMBER DEFAULT NULL, 
                                          shelf_life_months NUMBER DEFAULT NULL, 
                                          shelf_life_years  NUMBER DEFAULT NULL) 
RETURN NUMBER 
AS 
  num_null     PLS_INTEGER := 0; 
  num_not_null PLS_INTEGER := 0; 
  sdays        PLS_INTEGER; 
BEGIN 
    IF ( shelf_life_days IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( shelf_life_weeks IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( shelf_life_months IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( shelf_life_years IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( num_null = 4 ) THEN 
      Raise_application_error(-20001, 'Must supply one shelf life'); 
    END IF; 

    IF ( shelf_life_days IS NOT NULL ) THEN 
      sdays := shelf_life_days; 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( shelf_life_weeks IS NOT NULL ) THEN 
      sdays := shelf_life_weeks * 7; 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( shelf_life_months IS NOT NULL ) THEN 
      sdays := expire_date - Add_months(expire_date, shelf_life_months * -1); 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( shelf_life_years IS NOT NULL ) THEN 
      sdays := expire_date - Add_months(expire_date, shelf_life_years * -12); 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( num_not_null != 1 ) THEN 
      Raise_application_error(-20002, 'Cannot supply more than one shelf life'); 
    END IF; 

    RETURN ( ( Trunc(expire_date) - Trunc(SYSDATE) ) / sdays ); 
END; 

/ 

Open in new window

0
 
anumosesAuthor Commented:
I understand. Now my other request was to calculate percentage shelf life remaining
0
 
anumosesAuthor Commented:
what were your test values used in the function? if you dont mind?
0
 
awking00Commented:
For percentage of remaining shelf life use johnsone's days_remaining divided by (add_months(trunc(sysdate),60) - trunc(sysdate))
0
 
Abhimanyu SuriCommented:
Hi Anumoses,

I have one question, what is the basis of total shell life

SHELL_EXP_LIFE --> Some date in future
Life Remaining --> Future date - current date

Total Life --> How to determine that, is it always going to be from a fixed date or there is SHELL_START_DATE defined as well ?

Here is an assumption of given scenario

Lets's say ,

 shelf life start date is 54 days in past from today  = (sysdate -  54)
 shelf life expiration date is 18 months from now i.e. shelf_exp_date = add_months(sysdate,18)
 
 
 Life remaining =  (add_months(sysdate,18) - sysdate )       -- 546 days
 Total life = (add_months(sysdate,18) - (sysdate -  54) )    -- 600 days
 
  % life remaining

SELECT   (add_months(sysdate,18) - sysdate ) * 100
       / (add_months(sysdate,18) - (sysdate -  54) )
  FROM DUAL
 
  91% Life Remaining  

Please do consider the points mentioned by Johnsone regarding midnight and function
0
 
anumosesAuthor Commented:
I think the other function johnsone created gets the percentage if I have understood correctly.
0
 
anumosesAuthor Commented:
Abhimanyu Suri

We have a built_items table that has barcodes and part numbers. Each part has a shelf_exp_date

Taking system date I have to get percentage shelf life left. we provide parts to all major airlines.
0
 
anumosesAuthor Commented:
shelf life data
Included data as a screen shot for shelf life
0
 
johnsoneSenior Oracle DBACommented:
This is what I used for tests.

select pct_remaining(trunc(sysdate)+4) from dual;
select pct_remaining(trunc(sysdate)+4, 4, 4) from dual;
select pct_remaining(trunc(sysdate)+4, 4) from dual;
select pct_remaining(trunc(sysdate)+3, 4) from dual;
select pct_remaining(trunc(sysdate)+2, 4) from dual;
select pct_remaining(trunc(sysdate)+1, 4) from dual;
select pct_remaining(sysdate, null, 7) from dual;
select pct_remaining(sysdate+3, null, 1) from dual;
select pct_remaining(sysdate+3, null, null, 1) from dual;
select pct_remaining(sysdate+3) from dual;
select pct_remaining(sysdate+3, 1, 1, 1, 1) from dual;
select pct_remaining(sysdate+3, 1, 1, 1) from dual;

That is by no way conclusive and I really just checked to see that it looked right.  It could be totally incorrect.  You should be the one testing and verifying the code.  If it needs to be changed, by all means change it.
0
 
anumosesAuthor Commented:
johnsone please let me know if I am testing correctly. I get errors

select fun_percent_remaining(trunc(sysdate)+4) from dual;

ORA-20001: Must supply one shelf life
ORA-06512: at "DSS.FUN_PERCENT_REMAINING", line 29
ORA-06512: at line 1

select fun_percent_remaining(trunc(sysdate)+4, 4, 4) from dual;

ORA-20002: Cannot supply more than one shelf life
ORA-06512: at "DSS.FUN_PERCENT_REMAINING", line 57
ORA-06512: at line 1

I just changed the function name according to naming convention but everything is as you gave me

create or replace
FUNCTION fun_percent_remaining (expire_date       DATE, 
                                          shelf_life_days   NUMBER DEFAULT NULL, 
                                          shelf_life_weeks  NUMBER DEFAULT NULL, 
                                          shelf_life_months NUMBER DEFAULT NULL, 
                                          shelf_life_years  NUMBER DEFAULT NULL) 
RETURN NUMBER 
AS 
  num_null     PLS_INTEGER := 0; 
  num_not_null PLS_INTEGER := 0; 
  sdays        PLS_INTEGER; 
BEGIN 
    IF ( shelf_life_days IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( shelf_life_weeks IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( shelf_life_months IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( shelf_life_years IS NULL ) THEN 
      num_null := num_null + 1; 
    END IF; 

    IF ( num_null = 4 ) THEN 
      Raise_application_error(-20001, 'Must supply one shelf life'); 
    END IF; 

    IF ( shelf_life_days IS NOT NULL ) THEN 
      sdays := shelf_life_days; 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( shelf_life_weeks IS NOT NULL ) THEN 
      sdays := shelf_life_weeks * 7; 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( shelf_life_months IS NOT NULL ) THEN 
      sdays := expire_date - Add_months(expire_date, shelf_life_months * -1); 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( shelf_life_years IS NOT NULL ) THEN 
      sdays := expire_date - Add_months(expire_date, shelf_life_years * -12); 

      num_not_null := num_not_null + 1; 
    END IF; 

    IF ( num_not_null != 1 ) THEN 
      Raise_application_error(-20002, 'Cannot supply more than one shelf life'); 
    END IF; 

    RETURN ( ( Trunc(expire_date) - Trunc(SYSDATE) ) / sdays ); 
END;

Open in new window

0
 
anumosesAuthor Commented:
select fun_percent_remaining(sysdate+3, null, null, 1) from dual;

This worked fine

0.0967741935483870967741935483870967741935
0
 
johnsoneSenior Oracle DBACommented:
As you stated, the shelf life has units of either days, weeks, months, or years.  Not a combination of them.  So, you can only specify one of them.  If it would be possible to have more than one specified, then change the function.
0
 
anumosesAuthor Commented:
Thanks
0
 
anumosesAuthor Commented:
Thanks for all the help
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now