Solved

oracle function to get percentge of shelf life remaining

Posted on 2016-09-15
17
45 Views
Last Modified: 2016-09-19
 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
Comment
Question by:anumoses
17 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 41799774
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
 
LVL 6

Author Comment

by:anumoses
ID: 41799789
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
 
LVL 34

Expert Comment

by:johnsone
ID: 41799802
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
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 41799833
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
 
LVL 6

Author Comment

by:anumoses
ID: 41799837
I understand. Now my other request was to calculate percentage shelf life remaining
0
 
LVL 6

Author Comment

by:anumoses
ID: 41799844
what were your test values used in the function? if you dont mind?
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 41799848
For percentage of remaining shelf life use johnsone's days_remaining divided by (add_months(trunc(sysdate),60) - trunc(sysdate))
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41799856
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
ID: 41799857
I think the other function johnsone created gets the percentage if I have understood correctly.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41799862
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
 
LVL 6

Author Comment

by:anumoses
ID: 41799872
shelf life data
Included data as a screen shot for shelf life
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41799886
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
 
LVL 6

Author Comment

by:anumoses
ID: 41800171
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
 
LVL 6

Author Comment

by:anumoses
ID: 41800185
select fun_percent_remaining(sysdate+3, null, null, 1) from dual;

This worked fine

0.0967741935483870967741935483870967741935
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41800297
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
 
LVL 6

Author Comment

by:anumoses
ID: 41804553
Thanks
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41804554
Thanks for all the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now