CODEWITHSUNDEEP
oracle-databasevariablesdivision
cmomah
cmomah

Reputation: 315

Oracle SQL to display results from dividing one variable with another

I need to calculate the YTD weekly average for the GROSS_AMOUNT

In the below script, the first part of the code gets the number of weeks. The second part of the code gets the YTD sum for that same time period

How do I write the SQL so I can retrieve the actual value of YtdTotal/TotalWeeks?

SELECT YtdTotal/TotalWeeks FROM DUAL; does not work

-- STORES NUMBER OF WEEKS SINCE BEGINING OF YEAR INTO THE VARIABLE, TotalWeeks
DECLARE
    TotalWeeks NUMBER;
BEGIN
    SELECT to_number(to_char(sysdate, 'WW')) - to_number(to_char(trunc(sysdate, 'year'),'WW'))
    INTO TotalWeeks
    FROM DUAL
    -- (retrieves number of weeks since beginning of year)
END

-- STORES THE SUM OF GROSS)AMOUNT FOR THE WEEKS CALCULATED ABOVE - INTO THE 
DECLARE
    VARIABLE, YtdTotal
BEGIN
    SELECT SUM(GROSS_AMOUNT) 
    INTO YtdTotal
    FROM PARENTS
    WHERE process_date BETWEEN
    (next_day(TRUNC(sysdate, 'year'),'SUN'))
    AND
    (next_day(TRUNC(sysdate),'SAT')-7);
END;

Upvotes: 0

Views: 130

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Don't use two separate anonymous PL/SQL blocks - combine them into one!

If you want to return the result, then - instead of an anonymous PL/SQL block - create a function.

CREATE OR REPLACE FUNCTION f_test
  return NUMBER
IS
   totalweeks  NUMBER;
   ytdtotal    NUMBER;
   result      NUMBER;
BEGIN
   -- STORES NUMBER OF WEEKS SINCE BEGINING OF YEAR INTO THE VARIABLE, TotalWeeks
   SELECT   TO_NUMBER (TO_CHAR (SYSDATE, 'WW'))
          - TO_NUMBER (TO_CHAR (TRUNC (SYSDATE, 'year'), 'WW'))
     INTO totalweeks
     FROM DUAL;

   -- (retrieves number of weeks since beginning of year)

   -- STORES THE SUM OF GROSS)AMOUNT FOR THE WEEKS CALCULATED ABOVE - INTO THE
   SELECT SUM (gross_amount)
     INTO ytdtotal
     FROM parents
    WHERE process_date BETWEEN (NEXT_DAY (TRUNC (SYSDATE, 'year'), 'SUN'))
                           AND (NEXT_DAY (TRUNC (SYSDATE), 'SAT') - 7);

   -- the final result
   result := ytdtogal / totalweeks;
   RETURN result;
END;
/

Use it as

select f_test from dual;

Upvotes: 1

Related Questions