CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Nikhil
Nikhil

Reputation: 151

PL/SQL function to calculate average monthwise for a given date range

Suppose 

1st jan count is 20   
2nd jan count is 12   
3rd jan count is 0   
.................   
..................  
31st jan count is 17

I need a function which can find the average monthwise .In this case it should be 20+12+17/3 because I dont want to use the day which has count 0

I tried this:-

create function(p_date IN Date)
return number is

cursor c_num is
 select  count(distinct attribute8) from mtl_material_transactions  where trunc(transaction_date)=p_date;

l_num number:=0

begin
open c_num;
fetch c_num into l_num;
close c_num;

return sum(l_num);

end;

But it is not working as per my requirement.Please help me.I need to find monthwise average for a given date range say 01-April-2013 to 31st Mar-2014.

The Test Data is:-

create table kk1 ( ATTRIBUTE8 varchar2(30), TRANSACTION_DATE date );  

insert into kk1 values ( 'LP001', to_date('08.01.2015 15:21:59', 'dd.mm.yyyy hh24:mi:ss'));   
insert into kk1 values ( 'LP002', to_date('08.01.2015 15:24:33', 'dd.mm.yyyy hh24:mi:ss'));   
insert into kk1 values ( 'LP004', to_date('08.01.2015 15:41:51', 'dd.mm.yyyy hh24:mi:ss'));   
insert into kk1 values ( 'LP005', to_date('08.01.2015 15:43:38', 'dd.mm.yyyy hh24:mi:ss'));   
insert into kk1 values ( 'LP001', to_date('14.01.2015 11:36:13', 'dd.mm.yyyy hh24:mi:ss'));   
insert into kk1 values ( 'LP002', to_date('14.01.2015 11:38:24', 'dd.mm.yyyy hh24:mi:ss'));   
insert into kk1 values ( 'LP001', to_date('14.01.2015 11:40:09', 'dd.mm.yyyy hh24:mi:ss'));

Upvotes: 0

Views: 2130

Answers (2)

Nikhil
Nikhil

Reputation: 151

The answer to my question is:-

FUNCTION Get_attr_avg (p_from_date IN DATE,
                       p_to_date   IN DATE)
RETURN NUMBER
IS
  CURSOR c_attr_avg IS
    SELECT Avg(attr_per_day)
    FROM   (SELECT Count(DISTINCT attribute8) attr_per_day,
                   Trunc(transaction_date)    day
            FROM   mtl_material_transactions
            WHERE  transaction_date BETWEEN p_from_date AND p_to_date
                   AND attribute8 IS NOT NULL
            GROUP  BY Trunc(transaction_date))
    GROUP  BY To_char(day, 'MON-YYYY');
  l_attr_avg NUMBER := 0;
BEGIN
    OPEN c_attr_avg;
    FETCH c_attr_avg INTO l_attr_avg;
    CLOSE c_attr_avg;

    RETURN l_attr_avg;
EXCEPTION
  WHEN OTHERS THEN
             RETURN 0;
END get_attr_avg;

Upvotes: 0

Codo
Codo

Reputation: 78815

I'm not sure I understand your requirements. If you want to calculate the average for each month, I'd expect your function to take a date range (and not just single date) and to return more than a single number, namely a number for each month in the date range.

Does the following query calculate what your looking for?

SELECT AVG(attribute8) average, TRUNC(transaction_date, 'MONTH') mon
FROM mtl_material_transactions
WHERE transaction_date BETWEEN TO_DATE('01-Apr-2013', 'DD-MON-YYYY') AND TO_DATE('31-Mar-2014', 'DD-MON-YYYY')
  AND attribute8 <> 0
GROUP BY TRUNC(transaction_date, 'MONTH');

Upvotes: 1

Related Questions