[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

re: adjustments (function)

Posted on 2011-05-11
18
Medium Priority
?
243 Views
Last Modified: 2012-05-11
hi experts

i have a function which returns the accumulated adjustments until the given date
what if i want adjustment across each year given passed sate is as of date

project      employee      year      adjustment
d12345-00      c1234      2008      1500
d12345-00      c1234      2009      987
d12345-00      c1234      2010      12345
d12345-00      c1234      2011      2356

above is the data , if i give qualifier as to_char(sysdate,'yyyy')<= year its accumulating the adjustment , but in another scenario if the date is passed i need adjustments yearly till that year

mean suppose passed date is 05/05/2010 then output must be three rows

hi experts

i have a function which returns the accumulated adjustments until the given date
what if i want adjustment across each year given passed sate is as of date

project      employee      year      adjustment
d12345-00      c1234      2008      1500
d12345-00      c1234      2009      987
d12345-00      c1234      2010      12345
0
Comment
Question by:iamtechnical
  • 9
  • 5
  • 2
  • +1
18 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35740944
Not following but is it as simple as changing "<= year" to "< year"
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35740962
SELECT project, employee, year, adjustment, SUM(adjustment) OVER () total_adjustments
  FROM (SELECT project, employee, year, adjustment FROM yourtable)
 WHERE year <= 2010


you can replace the inner query with whatever query are you currently using that generates the original data
0
 

Author Comment

by:iamtechnical
ID: 35740970
actually i dint mention correctly ill pass project, employee and year to a function where the function has to give me the adjustments values, i am not using this adjustment table in the query for joining,
0
Technology Partners: 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!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35740982
Do you already have the function or are you wanting help writing the function?

I'm not sure what you are actually asking.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35740990
you mean something like this?

SELECT project, employee, year, adjustment, SUM(adjustment) OVER () total_adjustments
  FROM (SELECT project, employee, year, get_adjustment(project,employee,year) adjustment FROM yourtable
 WHERE year <= 2010)

if not,  please post sample data, as you would really use it, and expected results
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741009
i added the "as you would really use it" part
because it sounds like your original "table" is the output of some other table and function result

and then you want to modify that output to become the second output but with some other call?

so it's confusing.  instead.  Show where you are starting from and where you want to end up.  
0
 

Author Comment

by:iamtechnical
ID: 35741043
 

in this code if the passed date ia xx/xx/2010 it will calculate sum of adjustment till 2010 and return the value, but there is another scenario for me where i need those adjustments yearly,

the reason i am not using table at query level is particular project and consulatant may or may not have the adjustment, so if i use at query level if there is no adjustment for that project entire row will not be populated
create or replace function adjustment(project_number in varchar2, employee in varchar2, end_date in date)
return number
is

v_project_number varchar2(25);
v_employee      varchar2(15);
v_adjustment    number;
v_end_date     number;


begin

v_project_number :=project_number;
v_employee := employee;
v_end_date := to_char(end_date, 'yyyy');

       select adjustment
       into v_adjustment
       from
      xx_revenueadjustments 
       where 1=1
       and project = v_project_number
       and employee = v_employee
       and fiscalyear <= v_end_date;
       
       return v_adjustment;
       
       exception 
       when no_data_found then
       v_adjustment := 0;
       return v_adjustment;
       end ;
       
       exception
       when others then
       dbms_output.put_line('error is :'||sqlerrm);
       end adjustment;
       /

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741073
just do an outer join with analytic as shown above, much more efficient
0
 

Author Comment

by:iamtechnical
ID: 35741106
what shd i pass in the brackets for over(), because when i executed it summed all the adjustments across all the projects
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741118
still guessing as to what your input is but assuming it's a table of project/employee/years   try this...


SELECT project,
       employee,
       year,
       adjustment,
       SUM(
           adjustment)
       OVER (PARTITION BY project, employee
             ORDER BY year
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) total_adjustment
  FROM     yourtable t
       LEFT OUTER JOIN
           xx_revenueadjustments a
       ON     a.project = t.project
          AND a.employee = t.employee
          AND a.fiscalyear <= TO_DATE('05/05/2010', 'mm/dd/yyyy')
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35741131
here it is, create a sp, put \output param as cursor and use it in your app
create or replace procedure adjustment(project_number in varchar2, employee in varchar2, end_date in date, output out ref cursor)
return ref cursor
is
  v_project_number varchar2(25);
  v_employee      varchar2(15);
  v_adjustment    number;
  v_end_date     number;
begin

  v_project_number :=project_number;
  v_employee := employee;
  v_end_date := to_char(end_date, 'yyyy');

  open output for
    select fiscalyear, adjustment
      from xx_revenueadjustments 
     where 1=1
           and project = v_project_number
           and employee = v_employee
           and fiscalyear <= v_end_date;
     group by fiscalyear;

exception
when others then
dbms_output.put_line('error is :'||sqlerrm);
end adjustment;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741138
oops, I might have left off a join condition there,

again, though, I'm sort of guessing as to what the starting data and expected results are supposed to be
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35741142
no return part, fixing the code above
/* Formatted on 5/11/2011 3:31:40 PM (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE adjustment (project_number   IN     VARCHAR2,
                                        employee         IN     VARCHAR2,
                                        end_date         IN     DATE,
                                        output              OUT REF CURSOR)
IS
   v_project_number   VARCHAR2 (25);
   v_employee         VARCHAR2 (15);
   v_adjustment       NUMBER;
   v_end_date         NUMBER;
BEGIN
   v_project_number := project_number;
   v_employee := employee;
   v_end_date := TO_CHAR (end_date, 'yyyy');

   OPEN output FOR
        SELECT   fiscalyear, adjustment
          FROM   xx_revenueadjustments
         WHERE       1 = 1
                 AND project = v_project_number
                 AND employee = v_employee
                 AND fiscalyear <= v_end_date
      GROUP BY   fiscalyear;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('error is :' || SQLERRM);
END adjustment;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741155
creating a new function doesn't solve the cumulative total problem

you'd need 2 functions for that,  one to get the yearly and one to get the cumulative total

and it's not necessary since analytics will do it for you, more efficiently too

but... that's assuming I'm guessing correctly about what this question is really asking


0
 

Author Comment

by:iamtechnical
ID: 35741157
select h.org_id, p.project, e.employee, p.start_date, adjustment(p.project, e.employee,:end_date)
from projects p,
employeetable e,
organization h
where p.org_id = h.org_id
and p.person_id = h.person_id
and p.project = :project

so in the above query works for adjustments to be summed, in another layout i want

select h.org_id, p.project, e.employee, p.start_date, to_char(:end_date,'yyyy'), adjustment(p.project, e.employee,:end_date)
from projects p,
employeetable e,
organization h
where p.org_id = h.org_id
and p.person_id = h.person_id

across each year
and p.project = :project
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 35741185
if I am guessing correctly about the start and end points as well as what the data within the unnamed source table looks like and what the adjustment year data looks like then  it could also do be done with a scalar subquery instead of outer join


that might look like this...
SELECT project,
       employee,
       year,
       adjustment,
       SUM(
           adjustment)
       OVER (PARTITION BY project, employee
             ORDER BY year
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
           total_adjustment
  FROM (SELECT project,
               employee,
               year,
               (SELECT adjustment
                  FROM xx_revenueadjustments a
                 WHERE a.project = t.project AND a.employee = t.employee AND a.fiscalyear <= t.year)
                   adjustment
          FROM yourtable t
         WHERE year <= TO_DATE('05/05/2010', 'mm/dd/yyyy'))

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741206
ah, sorry, was posting followup while you were posting


try this...
SELECT project,
       employee,
       year,
       adjustment,
       SUM(
           adjustment)
       OVER (PARTITION BY project, employee
             ORDER BY year
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
           total_adjustment
  FROM (SELECT h.org_id,
               p.project,
               e.employee,
               TRUNC(p.start_date, 'yyyy') year,
               (SELECT adjustment
                  FROM xx_revenueadjustments a
                 WHERE     a.project = t.project
                       AND a.employee = t.employee
                       AND TRUNC(a.fiscalyear, 'yyyy') = TRUNC(p.start_date, 'yyyy'))
                   adjustment
          FROM projects p, employeetable e, organization h
         WHERE p.org_id = h.org_id AND p.person_id = h.person_id AND p.project = :project)

Open in new window

0
 

Author Closing Comment

by:iamtechnical
ID: 35741373
thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 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