Determine monthly and Yearly expenditures 1

[barnard90]

I have a Expenses table. It maintains the daily expenses in US Dollars of various units of the company.

The structure of the table is

Dept_Id Date Month Year Expense

The data looks like

Dept_id Date Month Year Expense
--------------------------------------------------

10 01/01/2006 1 2006 1000
10 01/02/2006 1 2006 2000
10 02/02/2006 2 2006 6000
10 01/05/2007 1 2007 5000

20 01/01/2006 1 2006 6000
20 01/02/2006 1 2006 8000
20 02/02/2006 2 2006 3000
20 01/05/2007 1 2007 4000




I need the monthly expense and Cumulative monthly expense of each Department . How do I achieve it . The output should look like

Dept_id Year Month Month Exp Cum Monthly Expense
---------------------------------------------------------
10 2006 1 3000 3000
10 2006 2 6000 9000
10 2007 1 5000 14000

20 2006 1 14000 14000
20 2006 2 3000 17000
20 2007 1 4000 21000

The monthly expense column has the sum of all daily expenses for a particular department_id in a particular month and year . The Cumulative Monthly Expense is the cumulative sum of all monthly expenses for a particular department_id .

How do I achieve the output as needed above
Please suggest

 

[SantaMufasa]

Barnard,

Your inquiry is a "poster child" for using Oracle "Analytic Functions". (Click on this link for an excellent discussion and great examples about Analytic Functions.)

Another piece of the solution is my use of in-line views (which allow grouped values to become detail values to the Analytic Functions).

So, using your data, here is a solution to your question:

col a heading "Month Exp" format 999,999
col b heading "Cum|Monthly|Expenses" format 999,999
break on dept_id skip 1
select dept_id
      ,year
      ,month
      ,sum_exp a
      ,sum(sum_exp)
       over (partition by dept_id order by year,month
             rows between unbounded preceding and current row) b
  from (select dept_id
              ,year
              ,month
              ,sum(expense) sum_exp
          from expenses
         group by dept_id,year,month)
/

                                                Cum
                                            Monthly
   DEPT_ID       YEAR      MONTH Month Exp Expenses
---------- ---------- ---------- --------- --------
        10       2006          1     3,000    3,000
                 2006          2     6,000    9,000
                 2007          1     5,000   14,000

        20       2006          1    14,000   14,000
                 2006          2     3,000   17,000
                 2007          1     4,000   21,000

Let us know if you other questions or wish to follow up.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

Santa,

Would you be kind enough to explain me the

[i] rows between unbounded preceding and current row[/i]

Thanks
Engi