1

Currently I am working on fixed asset register report wand and I need to add Projection columns in main query.

          Depreciation  Remaining Life of
Assed_No        Amount    asset in months
--------  ------------  -----------------
       1           400                  6
       2           200                  3
       3           100                  4
       4           600                  1

Now, I want modification in SQL that according remaining life of asset in months column should generate. For 1st asset remaining life of asset in months is 6, so total 6 projection columns should come with value as 400. If remaining life is less than maximum no. i.e 6 then it should give 0 for rest of columns.

I want final solution like below,

          Depreciation  Remaining Life of
Assed_No        Amount    asset in months  Projection 1  Projection 2  Projection 3  Projection 4  Projection 5  Projection 6
--------  ------------  -----------------  ------------  ------------  ------------  ------------  ------------  ------------
       1           400                  6           400           400           400           400           400           400
       2           200                  3           200           200             0             0             0             0
       3           100                  4           100           100           100           100             0             0
       4           600                  1           600             0             0             0             0             0
Improve this question
1
  • Hi All, Thanks for your suggestions. There is one more parameter added in current scenario which is period name. If it current period i.e 'JUL-18' then it should display projection months as, 'JUL-18', 'AUG-18' instead of Proj_1 , proj_2 etc. At current year end it should also calculate sum of all projection i.e 'JUL-18'+'AUG18'+...='Total 2018 Projection' then it should start with new columns 'JAN-19', 'FEB-19' at last again total 'Total 2019 Projection'. Commented Jul 23, 2018 at 12:30

3 Answers 3

Reset to default
2

You can use a simple case expression for each projection:

-- CTE for sample data
with your_table (asset_no, amount, remaining_months) as (
            select 1, 400, 6 from dual
  union all select 2, 200, 3 from dual
  union all select 3, 100, 4 from dual
  union all select 4, 600, 1 from dual
)
-- query using my CTE column names
select asset_no, amount, remaining_months,
  case when remaining_months >= 1 then amount else 0 end as proj_1,
  case when remaining_months >= 2 then amount else 0 end as proj_2,
  case when remaining_months >= 3 then amount else 0 end as proj_3,
  case when remaining_months >= 4 then amount else 0 end as proj_4,
  case when remaining_months >= 5 then amount else 0 end as proj_5,
  case when remaining_months >= 6 then amount else 0 end as proj_6
from your_table;

  ASSET_NO     AMOUNT REMAINING_MONTHS     PROJ_1     PROJ_2     PROJ_3     PROJ_4     PROJ_5     PROJ_6
---------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ----------
         1        400                6        400        400        400        400        400        400
         2        200                3        200        200        200          0          0          0
         3        100                4        100        100        100        100          0          0
         4        600                1        600          0          0          0          0          0
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi Alex, Thanks for reply. Here actual requirement is different because we dont know about what will be remaining month for each asset. If it 'n' no. of months then it should generate 'n' number of columns. Here I missed one thing that I need summation of all projection row-wise.
@PrashantKulkarni - if you want to pick that n value at run time then you need a dynamic picot, as KaushikNayak linked to. That isn't what you described in your question though, aside from using 'dynamic' in the title - it implies the maximum number is known and is 6. If you're using this for a report then the pivot might be something your reporting tool should be doing, instead of trying to do it in the database.
1

This is basically the dynamic version of Alex's query ( as a bonus !)

Note the use of refcursor Bind variables. This will work when you run it in SQl* Plus or as script (F5) in SQL developer or Toad.

You may also use DBMS_SQL.RETURN_RESULT in Oracle 12c and above to do the same.

VARIABLE x REFCURSOR;
DECLARE
v_case_expr VARCHAR2(1000);
BEGIN
SELECT
    listagg('CASE WHEN remaining_months > = '
              || level
              || ' 
                         then amount else 0 end as proj_'
              || level,',') WITHIN GROUP ( ORDER BY level)
INTO v_case_expr
FROM
    dual
CONNECT BY
    level <= (
        SELECT
            MAX(remaining_months)
        FROM
            assets
    );            

OPEN :x FOR 'select asset_no, amount, remaining_months, '
           || v_case_expr
           || ' FROM assets';END;
/

PRINT x;

PL/SQL procedure successfully completed.



  ASSET_NO     AMOUNT REMAINING_MONTHS     PROJ_1     PROJ_2     PROJ_3     PROJ_4     PROJ_5     PROJ_6
---------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ----------
         1        400                6        400        400        400        400        400        400
         2        200                3        200        200        200          0          0          0
         3        100                4        100        100        100        100          0          0
         4        600                1        600          0          0          0          0          0
Improve this answer

2 Comments

That won't work if all the remaining months are 0. Also, I wonder if it would be more performant to find the max value first and then use PL/SQL to generate the list of columns (e.g. for i in 1..v_max_remaining_months loop v_case_expr := .... end loop) rather than the connect by and listagg.
Hi Kaushik, can we write Function to to get Pivot table dynamically?
0

When the amount of calculations increase sometimes pivot could be better. Here I use a cartesian product before, to create the data: 

  with dat (asset_no, dep_amount, r_life)as 
  ( select 1, 400, 6 from dual union all 
    select 2, 200, 3 from dual union all
    select 3, 100, 4 from dual union all 
    select 4, 600, 1 from dual )
  , mon as (select level lv from dual connect by level <= 6)
  , bas as (
  select asset_no, dep_amount, r_life, lv, case when r_life >= lv then dep_amount else 0 end proj_m from dat, mon)
  select * 
  from bas
  pivot(sum(proj_m) for lv in (1 as proj_1,2 as proj_2,3 as proj_3,4 as proj_4,5 as proj_5,6 as proj_6)) 
  order by asset_no

Think the solution with case expressions is better here

Improve this answer

2 Comments

Hi Thomas, Can we do it for 'n' number of assets?
Assets aren't the issue. Months have to be hardcoded if you don't want to use xml-pivot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.