re: adjustments (function)

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
iamtechnicalAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Not following but is it as simple as changing "<= year" to "< year"
0
 
sdstuberCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
iamtechnicalAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Do you already have the function or are you wanting help writing the function?

I'm not sure what you are actually asking.
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
iamtechnicalAuthor Commented:
 

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
 
sdstuberCommented:
just do an outer join with analytic as shown above, much more efficient
0
 
iamtechnicalAuthor Commented:
what shd i pass in the brackets for over(), because when i executed it summed all the adjustments across all the projects
0
 
sdstuberCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
sdstuberCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
sdstuberCommented:
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
 
iamtechnicalAuthor Commented:
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
 
sdstuberCommented:
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
 
iamtechnicalAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.