Skip to Main Content
  • Questions

  • Weighted Average Inventory calculation.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tanveer.

Asked: March 06, 2019 - 4:34 pm UTC

Last updated: February 17, 2021 - 5:28 pm UTC

Version: 11g Release 11.2.0.2.0

Viewed 10K+ times! This question is

You Asked

Hello,
Hope you are at the best of you health.

I am working in Microsoft Windows environment and installed database is Oracle 11gR2.
I have following two table with sample data.

CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype VARCHAR2(15) );


and sample data is following.

INSERT INTO stock_master  VALUES (1, '25-JAN-19', 'PURCHASE’ );

INSERT INTO stock_master  VALUES (2, '26-JAN-19', 'PURCHASE’ );

INSERT INTO stock_master  VALUES (3, '26-JAN-19', 'SALE’ );

INSERT INTO stock_master  VALUES (4, '27-JAN-19', 'SALE’ );

INSERT INTO stock_master  VALUES (5, '28-JAN-19', 'PURCHASE’ );

INSERT INTO stock_master  VALUES (6, '28-JAN-19', 'SALE’ );

CREATE TABLE stock_detail ( vd_no INTEGER, vno INTEGER, item_code VARCHAR2(8), qty NUMBER, rate NUMBER, vtype VARCHAR2(15) );

INSERT INTO stock_detail  VALUES (1, 1, '001001’, 10, 100 );
INSERT INTO stock_detail  VALUES (2, 2, '001001’, 50, 150 );
INSERT INTO stock_detail  VALUES (3, 3, '001001’, 15, 160 );
INSERT INTO stock_detail  VALUES (4, 4, '001001’, 5, 160 );
INSERT INTO stock_detail  VALUES (5, 5, '001001’, 10, 165 );
INSERT INTO stock_detail  VALUES (6, 6, '001001’, 50, 170 );


My requirement is to create VIEW from above two tables that will show date wise inventory with weighted average cost of item_code as follows: -

DATE ITEM_CODE QTY RATE AMOUNT QTY_IN_HAND AVG_RATE AVG_AMOUT VTYPE
25-Jan-19 001001 10 100 1000 10 100 1000 PURCHASE
26-Jan-19 001001 50 150 7500 60 141.6667 7083.335 PURCHASE
26-Jan-19 001001 15 160 2400 45 141.6667 6375.0015 SALE
27-Jan-19 001001 5 160 800 40 141.6667 5666.668 SALE
28-Jan-19 001001 10 165 1650 50 146.3334 7316.67 PURCHASE
28-Jan-19 001001 50 70 3500 0 146.3334 0 SALE


AMOUNT will be calculated as follows:-
Amount = qty * rate

In case of first time purchase, calculation will be as follows:-
qty_in_hand = qty
avg_rate = amount / qty
avg_amount = qty_in_hand * avg_rate

Subsequently in case of PURCHASES, QTY_IN_HAND and AVG_AMOUNT will be calculated by adding newly purchased qty and amount in the running balances like follows:-
qty_in_hand = qty_in_hand + qty
avg_amount = avg_amount + amount
avg_rate = avg_amount / qty_in_hand

and in case of SALE, QTY_IN_HAND will be calculated by subtracting selling qty
qty_in_hand = qty_in_hand – qty

but AVERAGE_AMOUNT will be reduced by subtracting running avg_amount from (qty * avg_rate) as follows:-
avg_amount = avg_amount - (qty * avg_rate)

and Chris said...

Thanks for the formulas. But I don't understand how you're getting the shown avg rate/amount figures based on them; these values seem to have a circular definition.

Anyway, to calculate the quantity on hand, sum up:

Positive QTY for purchases
Negative QTY for sales

Which gives

select m.*, d.qty, d.rate,
       sum (
         case when vtype = 'PURCHASE'
           then qty
           else -qty
         end
       ) over ( 
         order by vdate, m.vno 
       ) qty_on_hand
from   stock_master m
join   stock_detail d
on     m.vno = d.vno;

VNO   VDATE                  VTYPE      QTY   RATE   QTY_ON_HAND   
    1 25-JAN-0019 00:00:00   PURCHASE      10    100            10 
    2 26-JAN-0019 00:00:00   PURCHASE      50    150            60 
    3 26-JAN-0019 00:00:00   SALE          15    160            45 
    4 27-JAN-0019 00:00:00   SALE           5    160            40 
    5 28-JAN-0019 00:00:00   PURCHASE      10    165            50 
    6 28-JAN-0019 00:00:00   SALE          50    170             0


If you clarify how the averages should work, we'll see how we can help on those.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Average Rate and Amount calculations

Tanveer, March 15, 2019 - 4:26 pm UTC

Sir, Thanks for your SQL for calculation of Qty_In_Hand but I am also interested in calculation of average rates and average amount through SQL.

Let me again explain formula for avg_rate and avg_amount calculations (now skipping qty_in_hand which is already mentioned in your answer)

In every row, amount will be calculated as follows:-
amount = qty * rate;

In case of first time purchase, calculation will be as follows:-
qty_in_hand = qty
avg_rate = rate
avg_amount = amount

Subsequently in case of PURCHASES, AVG_AMOUNT will be calculated by adding newly purchased qty and amount in the running balances like follows:-
avg_amount = avg_amount + amount (avg_amount from last row will be added with amount to form new avg_amount)
Now avg_rate will be calculated with new avg_amount
avg_rate = avg_amount / qty_in_hand

In case of SALES, AVG_RATE will not be recalculated and taken from previous row
but AVERAGE_AMOUNT will be reduced by subtracting last row average_amount from (qty * avg_rate) as follows:-
avg_amount = avg_amount - (qty * avg_rate) (avg_amount from last row will be substracted from product of qty * avg_rate)

Chris Saxon
March 18, 2019 - 11:34 am UTC

I can't make your expected avg_amounts match the formulas provided.

For the second row, AVG_AMOUNT = 7083.335

But:

prev avg_amount + ( qty * rate ) = 1000 + ( 50 * 150 ) = 8,500.

So where does 7083.335 come from?

Average Rate and Amount calculations

Tanveer, March 22, 2019 - 5:47 pm UTC

Please consider following table for calculation of average amount and average rate as per my already given formula

DATE ITEM_CODE QTY RATE QTY_IN_HAND AVG_RATE AVG_AMOUT MODE
25-Jan-19 001001 10 100 10 100 1000 PURCHASE
26-Jan-19 001001 50 150 60 141.6667 8500 PURCHASE
26-Jan-19 001001 15 160 45 141.6667 6375.0015 SALE
27-Jan-19 001001 5 160 40 141.6667 5666.668 SALE
28-Jan-19 001001 10 165 50 146.3334 7316.668 PURCHASE
28-Jan-19 001001 50 70 0 146.3334 0 SALE

Chris Saxon
April 03, 2019 - 10:02 am UTC

There's interdependence between the average rates and amounts. So I think you're going to have to go with recursive with for this.

First join the tables and add a row number according to the order you want to process the rows.

This makes it easy to find the base case ( rn = 1 ) and the recursive values ( child rn = parent rn + 1 ).

Then plug in the formulas as needed in the recursive clause:

with rws as (
  select d.*, m.vdate, m.vtype,
         row_number () over (
           order by m.vdate, m.vno 
         ) rn
  from   stock_master m
  join   stock_detail d
  on     m.vno = d.vno
), stock ( 
  vdate, item_code, qty, rate, amount, 
  qty_in_hand, avg_rate, avg_amount, vtype, rn
) as (
  select vdate, item_code, qty, rate, 
         ( qty * rate ) amount, 
         qty qty_in_hand, 
         ( qty * rate ) / qty avg_rate, 
         ( qty * rate ) avg_amount, 
         vtype, 
         rn
  from   rws
  where  rn = 1
  union  all
  select r.vdate, r.item_code, r.qty, 
         r.rate, 
         ( r.qty * r.rate ) amount, 
         case
           when r.vtype = 'PURCHASE' then
             s.qty_in_hand + r.qty
           else 
             s.qty_in_hand - r.qty
         end qty_in_hand, 
         case
           when r.vtype = 'PURCHASE' then
             ( s.avg_amount + ( r.qty * r.rate ) ) /
             ( s.qty_in_hand + r.qty )
           else 
             s.avg_rate
         end avg_rate, 
         case
           when r.vtype = 'PURCHASE' then
             s.avg_amount + ( r.qty * r.rate )
           else 
             s.avg_amount - ( r.qty * s.avg_rate )
         end avg_amount, 
         r.vtype,
         r.rn
  from   stock s
  join   rws r
  on     s.rn + 1 = r.rn
)
  select vdate, item_code, qty, rate
         amount, qty_in_hand, 
         round ( avg_rate, 4 ) avg_rate,
         round ( avg_amount, 4 ) avg_amount,
         vtype
  from   stock;

VDATE       ITEM_CODE   QTY   AMOUNT   QTY_IN_HAND   AVG_RATE   AVG_AMOUNT   VTYPE      
25-JAN-19   001001         10      100            10        100         1000 PURCHASE   
26-JAN-19   001001         50      150            60   141.6667         8500 PURCHASE   
26-JAN-19   001001         15      160            45   141.6667         6375 SALE       
27-JAN-19   001001          5      160            40   141.6667    5666.6667 SALE       
28-JAN-19   001001         10      165            50   146.3333    7316.6667 PURCHASE   
28-JAN-19   001001         50      170             0   146.3333            0 SALE

using model clause

Rajeshwaran Jeyabal, April 05, 2019 - 9:49 am UTC

since it involves inter-dependencies between avg_rate and avg_amount calculation and lot of inter row calculations across the VTYPE for "Purchase" and "sales" - here is an approach using MODEL clause (with few round off issues on avg_rate and avg_amount calculations)
demo@PDB1> select *
  2  from ( select m.vdate, d.item_code,d.vd_no,
  3          d.qty,d.rate, d.qty*d.rate as amount,
  4          m.vtype,
  5          sum(case when m.vtype ='SALE' then -1 else 1 end * qty) over(order by vd_no) qty_in_hand,
  6          row_number() over( order by d.vd_no) r,
  7          count(*) over() cnt
  8        from stock_master m,
  9             stock_detail d
 10        where m.vno = d.vno )
 11  model
 12     dimension by (r)
 13     measures( vdate,item_code,vd_no,qty,rate,amount,vtype,qty_in_hand ,
 14               0 avg_amount, 0 avg_rate,cnt )
 15     rules iterate(1000) until( cnt[1] <= iteration_number ) (
 16       avg_amount[r=1] = qty[cv()] * rate[cv()] ,
 17       avg_rate[r=1] = rate[cv()],
 18       avg_rate[r>1] order by r =
 19           case when vtype[cv()]='PURCHASE' then
 20               round( ( avg_amount[cv()-1] + ( qty[cv()] * rate[cv()] ) ) /
 21                 ( qty_in_hand[cv()-1] + qty[cv()] ) ,4 )
 22               else avg_rate[cv()-1] end ,
 23       avg_amount[r>1] order by r =
 24            case when  vtype[cv()]='PURCHASE' then
 25               round ( avg_amount[cv()-1] + ( qty[cv()] * rate[cv()] ),4 )
 26             else round ( avg_amount[cv()-1] - ( qty[cv()] * avg_rate[cv()-1] ),4 )
 27            end )
 28  order by r
 29  /

         R VDATE       ITEM_COD      VD_NO        QTY       RATE     AMOUNT VTYPE           QTY_IN_HAND AVG_AMOUNT   AVG_RATE        CNT
---------- ----------- -------- ---------- ---------- ---------- ---------- --------------- ----------- ---------- ---------- ----------
         1 25-JAN-2019 001001            1         10        100       1000 PURCHASE                 10       1000        100          6
         2 26-JAN-2019 001001            2         50        150       7500 PURCHASE                 60       8500   141.6667          6
         3 26-JAN-2019 001001            3         15        160       2400 SALE                     45  6374.9995   141.6667          6
         4 27-JAN-2019 001001            4          5        160        800 SALE                     40   5666.666   141.6667          6
         5 28-JAN-2019 001001            5         10        165       1650 PURCHASE                 50   7316.666   146.3333          6
         6 28-JAN-2019 001001            6         50        170       8500 SALE                      0       .001   146.3333          6

6 rows selected.

demo@PDB1>

Chris Saxon
April 08, 2019 - 7:11 am UTC

Yep, that's another way to solve this.

Tanveer Ahmad, April 09, 2019 - 4:39 pm UTC

Thank you

Question on Model Solution

Amit S, April 24, 2019 - 4:56 pm UTC

Hi Chris,

How this Model solution works with Iterate? Can you please explain it a little.

Thanks.
Chris Saxon
April 25, 2019 - 12:54 pm UTC

Iterate means run the rules N times. In this case, the lower of 1,000 and CNT (count of rows).

It's not necessary - Rajesh will have to weigh in to explain the reasoning behind this.

You can use automatic order to resolve the dependencies between the rules:

select vdate, item_code, vd_no, rate, amount, vtype, qty_in_hand,
       round ( avg_amount, 4 ) avg_amount,
       round ( avg_rate, 4 ) avg_rate
from ( 
  select m.vdate, d.item_code,d.vd_no,
        d.qty,d.rate, d.qty*d.rate as amount,
        m.vtype,
        sum(case when m.vtype ='SALE' then -1 else 1 end * qty) over(order by vd_no) qty_in_hand,
        row_number() over( order by d.vd_no ) r
  from  stock_master m,
        stock_detail d
  where m.vno = d.vno 
)
model
   dimension by (r)
   measures ( 
     vdate,item_code,vd_no,qty,rate,amount,vtype,qty_in_hand ,
     0 avg_amount, 0 avg_rate
   )
   rules automatic order (
     avg_amount[r=1] = qty[cv()] * rate[cv()] ,
     avg_rate[r=1] = rate[cv()] ,
     avg_amount[r>1] =
        case 
          when vtype[cv()]='PURCHASE' then
            avg_amount[cv()-1] + ( qty[cv()] * rate[cv()] )
          else 
            avg_amount[cv()-1] - ( qty[cv()] * avg_rate[cv()-1] )
        end ,
     avg_rate[r>1] =
       case 
         when vtype[cv()]='PURCHASE' then
           ( avg_amount[cv()-1] + ( qty[cv()] * rate[cv()] ) ) /
           ( qty_in_hand[cv()-1] + qty[cv()] )
         else 
           avg_rate[cv()-1] 
     end
     )
order by r;

VDATE                  ITEM_CODE   VD_NO   RATE   AMOUNT   VTYPE      QTY_IN_HAND   AVG_AMOUNT   AVG_RATE   
25-JAN-0019 00:00:00    001001              1     100      1000 PURCHASE                10          1000         100 
26-JAN-0019 00:00:00    001001              2     150      7500 PURCHASE                60          8500    141.6667 
26-JAN-0019 00:00:00    001001              3     160      2400 SALE                    45          6375    141.6667 
27-JAN-0019 00:00:00    001001              4     160       800 SALE                    40     5666.6667    141.6667 
28-JAN-0019 00:00:00    001001              5     165      1650 PURCHASE                50     7316.6667    146.3333 
28-JAN-0019 00:00:00    001001              6     170      8500 SALE                     0             0    146.3333


Also note you need to defer rounding to the final select. Or you end up extra decimal digits at the end...

what if we use Temporal Validity on table stock_detail

zaki, February 01, 2021 - 7:20 am UTC

how do we proceed if the table stock_detail use temporal validity and we need multiple version over time of the inventory 

DATE date_correction ITEM_CODE QTY 
25-Jan-19 25-Jan-19 1001 10 
26-Jan-19 26-Jan-19 1001 50 
26-Jan-19 26-Jan-19 1001 15 
27-Jan-19 27-Jan-19 1001 5 
<b>27-Jan-19 29-Jan-19 1001 4 </b>
28-Jan-19 28-Jan-19 1001 10 
28-Jan-19 28-Jan-19 1001 50

Chris Saxon
February 01, 2021 - 9:13 am UTC

What do you mean by we need multiple version over time of the inventory?

What output are you expecting given this input?

And please provide sample data in the form of:

create table
insert into

weighted average inventory on temporal table

zaki, February 01, 2021 - 1:35 pm UTC

let assume 

drop TABLE stock_master cascade constraints;
drop TABLE stock_detail cascade constraints;
CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype VARCHAR2(15) );
INSERT INTO stock_master  VALUES (1, '25-JAN-19', 'PURCHASE' );
INSERT INTO stock_master  VALUES (2, '26-JAN-19', 'PURCHASE' );
INSERT INTO stock_master  VALUES (3, '26-JAN-19', 'SALE' );
INSERT INTO stock_master  VALUES (4, '27-JAN-19', 'SALE' );
INSERT INTO stock_master  VALUES (5, '28-JAN-19', 'PURCHASE' );
INSERT INTO stock_master  VALUES (6, '28-JAN-19', 'SALE' );

CREATE TABLE stock_detail ( vd_no INTEGER, vno INTEGER, item_code VARCHAR2(8), qty NUMBER, rate NUMBER,  effectif_date_start date , effectif_date_end  date );

INSERT INTO stock_detail  VALUES (1, 1, '001001', 10, 100  ,null,null);
INSERT INTO stock_detail  VALUES (2, 2, '001001', 50, 150 ,null,null);
INSERT INTO stock_detail  VALUES (3, 3, '001001', 15, 160 ,null,null);
INSERT INTO stock_detail  VALUES (4, 4, '001001', 5, 160 ,null,null);
INSERT INTO stock_detail  VALUES (5, 5, '001001', 10, 165 ,null,null);
INSERT INTO stock_detail  VALUES (6, 6, '001001', 50, 170 ,null,null);

update stock_detail set effectif_date_end = '30-JAN-19' where vd_no= 4 and vno = 4 ;
INSERT INTO stock_detail  VALUES (5, 5, '001001',12, 165 ,'30-JAN-19', null);


the output is like 
vdate eff_date_start eff_date_end item_code qte rate qte_in_hand avg_rate avg_amount vtype
25-Jan-19   1001 10 100 10 100 1000 PURCHASE
26-Jan-19   1001 50 150 60 141.6667 8500 PURCHASE
26-Jan-19   1001 15 160 45 141.6667 6375 SALE
27-Jan-19   1001 5 160 40 141.6667 5666.6667 SALE
28-Jan-19  30-Jan-19 1001 10 165 50 146.3333 7316.6667 PURCHASE
28-Jan-19 30-Jan-19  1001 12 165 52 147.0513 7646.6667 PURCHASE
28-Jan-19  30-Jan-19 1001 50 170 0 146.3333 0 SALE
28-Jan-19 30-Jan-19  1001 50 170 2 147.0513 294.1026 SALE

Chris Saxon
February 01, 2021 - 2:57 pm UTC

Surely you want to update the row for vno 5, not 4? Assuming that's the case, you can get the output you want by changing the recursive with above:

- Changing row_number() => dense_rank () in the first subquery. This ensures rows for a particular VNO have the same rank, so the recursive search picks up both

- Adding start/end dates to the query. In the recursive branch, take the start/end value from the previous row if present

Example below.

The output will quickly get messy though and I'm not convinced it's correct in all cases. If you give us more background on what the business goal is here and why you're doing this we figure this out.

with rws as (
  select d.*, m.vdate, m.vtype, d.effectif_date_start st, d.effectif_date_end en,
         dense_rank () over (
           order by m.vdate, m.vno 
         ) rn
  from   stock_master m
  join   stock_detail d
  on     m.vno = d.vno
), stock ( 
  vdate, st, en, item_code, qty, rate, amount, 
  qty_in_hand, avg_rate, avg_amount, vtype, rn
) as (
  select vdate, st, en, item_code, qty, rate, 
         ( qty * rate ) amount, 
         qty qty_in_hand, 
         ( qty * rate ) / qty avg_rate, 
         ( qty * rate ) avg_amount, 
         vtype, 
         rn
  from   rws
  where  rn = 1
  union  all
  select r.vdate, nvl ( r.st, s.st ) st, nvl ( r.en, s.en ) en,
         r.item_code, r.qty, 
         r.rate, 
         ( r.qty * r.rate ) amount, 
         case
           when r.vtype = 'PURCHASE' then
             s.qty_in_hand + r.qty
           else 
             s.qty_in_hand - r.qty
         end qty_in_hand, 
         case
           when r.vtype = 'PURCHASE' then
             ( s.avg_amount + ( r.qty * r.rate ) ) /
             ( s.qty_in_hand + r.qty )
           else 
             s.avg_rate
         end avg_rate, 
         case
           when r.vtype = 'PURCHASE' then
             s.avg_amount + ( r.qty * r.rate )
           else 
             s.avg_amount - ( r.qty * s.avg_rate )
         end avg_amount, 
         r.vtype,
         r.rn
  from   stock s
  join   rws r
  on     s.rn + 1 = r.rn
)
  select vdate, st, en, item_code, qty, rate
         amount, qty_in_hand, 
         round ( avg_rate, 4 ) avg_rate,
         round ( avg_amount, 4 ) avg_amount,
         vtype
  from   stock;
  
VDATE                ST                   EN                    ITEM_CODE  QTY  AMOUNT  QTY_IN_HAND   AVG_RATE   AVG_AMOUNT VTYPE      
25-JAN-0019 00:00:00 <null>               <null>                001001      10     100           10        100         1000 PURCHASE    
26-JAN-0019 00:00:00 <null>               <null>                001001      50     150           60   141.6667         8500 PURCHASE    
26-JAN-0019 00:00:00 <null>               <null>                001001      15     160           45   141.6667         6375 SALE        
27-JAN-0019 00:00:00 <null>               <null>                001001       5     160           40   141.6667    5666.6667 SALE        
28-JAN-0019 00:00:00 <null>               30-JAN-0019 00:00:00  001001      10     165           50   146.3333    7316.6667 PURCHASE    
28-JAN-0019 00:00:00 30-JAN-0019 00:00:00 <null>                001001      12     165           52   147.0513    7646.6667 PURCHASE    
28-JAN-0019 00:00:00 <null>               30-JAN-0019 00:00:00  001001      50     170            0   146.3333            0 SALE        
28-JAN-0019 00:00:00 30-JAN-0019 00:00:00 <null>                001001      50     170            2   147.0513     294.1026 SALE

correction of the output including null values

zaki, February 01, 2021 - 1:38 pm UTC 

vdate eff_date_start eff_date_end item_code qte rate qte_in_hand avg_rate avg_amount vtype
25-Jan-19 null null 1001 10 100 10 100 1000 PURCHASE
26-Jan-19 null null 1001 50 150 60 141.6667 8500 PURCHASE
26-Jan-19 null null 1001 15 160 45 141.6667 6375 SALE
27-Jan-19 null null 1001 5 160 40 141.6667 5666.6667 SALE
28-Jan-19 null 30-Jan-19 1001 10 165 50 146.3333 7316.6667 PURCHASE
28-Jan-19 30-Jan-19 null 1001 12 165 52 147.0513 7646.6667 PURCHASE
28-Jan-19 null 30-Jan-19 1001 50 170 0 146.3333 0 SALE
28-Jan-19 30-Jan-19 null 1001 50 170 2 147.0513 294.1026 SALE

A reader, February 03, 2021 - 8:04 am UTC

thanks for all,
the business purpose is to provide the accounting system any corrections on the inventory management system, so any delayed update on purchase cost( rate or price) affect all following weighted average cost 
drop TABLE stock_master cascade constraints;
drop TABLE stock_detail cascade constraints;

CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype VARCHAR2(15) );
CREATE TABLE stock_detail ( vno INTEGER, item_code VARCHAR2(8), qty NUMBER, cost NUMBER,  eff_date_start date , eff_date_end  date );

INSERT INTO stock_master  VALUES (1, '25-JAN-19', 'PURCHASE' );
INSERT INTO stock_detail  VALUES (1, '1001', 10, 100  ,null,null);
INSERT INTO stock_detail  VALUES (1, '1002', 15, 120  ,null,null);

INSERT INTO stock_master  VALUES (2, '27-JAN-19', 'SALE' );
INSERT INTO stock_detail  VALUES (2, '1001',  7, null ,null,null);
INSERT INTO stock_detail  VALUES (2, '1002',  4, null ,null,null);

-- assuming some corrections occured in february

update stock_detail set eff_date_end = '01-FEB-19' where vno = 1 and item_code = '1001'; -- this update to validate temporal constraint  
INSERT INTO stock_detail  VALUES (1,  '1001',10, 160 ,'01-FEB-19', null);


the final requirement is 
yyyymm item_code purchase purchase_delta sale sale_delta avg_amount
201901 1001 1000 0 700 0 300
201901 1002 1800 0 480 0 1320
201902 1001 0 100 0 70 330
201902 1002 0 0 0 0 1320



Chris Saxon
February 17, 2021 - 5:28 pm UTC

I'm not sure what you're hoping to see here; the expected output doesn't include the correction?

I think what you're getting at is the business wants to see their stock details for a date in the past, based on the data they had on that date.

e.g. on 1 Jan we thought the amount were X; so reports run for dates as of 1 Jan should report this total.

But on 1 Feb someone realized there had been an error for 1 Jan's numbers, so put a correction in. Reports run "as of" 1 Feb onwards should report the corrected values.

If this is the case, filter this table like so:

select * from stock_details
where  eff_date_start <= :dt
and    ( eff_date_end is null or eff_date_end > :dt )


Or you may want to look into Flashback Data Archive. This allows you to update the values, but still get the data as it was on a date in the past with Flashback Query:

https://docs.oracle.com/en/database/oracle/oracle-database/21/adfns/flashback.html#GUID-06AA782A-3F79-4235-86D5-2D136485F093

correction

A reader, February 03, 2021 - 8:42 am UTC 


-- assuming some corrections occured in february

update stock_detail set eff_date_end = '01-FEB-19' where vno = 1 and item_code = '1001'; -- this update to validate temporal constraint  
INSERT INTO stock_detail  VALUES (1,  '1001',10, 110,'01-FEB-19', null);

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.