Search code examples
sqldb2ibm-midrange

Used data from the previous record to calculate data of the current one

What is the LAG and LEAD syntax for AS400? I am trying to do some calculation to the current record based on the previous record?

For example: I need to calculate the Work Center Quantity for each record based on the previous one.

enter image description here

UPDATE:

It is important, as I described in the example, that the calculation will be based on the previous row but not on a total of all the previous rows. I just gave a very optimized example of my needs but actually substracting the SUM of all the previous records is not helping me. Is there a way to subscribe only the previous record?

Solution

  • Perhaps the following, a very close likeness to my reply to DB2 recursive UDF Table but with this reply I made almost no review except of the results from a test with the given sample data [plus a column for visually expressing equivalence to expected output] to verify:

    Setup:

    set current schema <whatever>
create table wc
( work_center    for wc       char     
, total_quantity for tq       dec      
, scrap_quantity for sq       dec      
, vfy_quantity   for vq       dec
) 
;
insert into  wc       values     
  ( 'A' ,   1000,     10,   990 )
, ( 'B' ,   1000,      5,   985 )
, ( 'C' ,   1000,     12,   973 )
, ( 'D' ,   1000,      1,   972 )
;

    Query and result:

    with
  ordRows (rn, wc, tq, sq, vq) as
   ( select row_number() over(order by wc) as rn
          ,    wc, tq, sq, vq 
     from wc
   )    
, subRows (rn, wc, tq, sq, vq, cq /* calc qty */) as
   ( select rn, wc, tq, sq, vq
          , ( tq - sq )
     from ordRows
     where rn = 1
   union all
     select b.rn, b.wc, b.tq, b.sq, b.vq        
          , ( a.cq - b.sq )
     from subRows a
     join ordRows b
       on a.rn = ( b.rn - 1 )
   )
select dec(rn, 5) as rn, wc, tq, sq
     , vq, ' = ' as eq, cq
from subrows
; -- likeness of report from above query:
....+....1....+....2....+....3....+....4....+....5....+
    RN   WC      TQ       SQ       VQ   EQ          CQ 
     1   A    1,000       10      990    =         990 
     2   B    1,000        5      985    =         985 
     3   C    1,000       12      973    =         973 
     4   D    1,000        1      972    =         972 
********  End of data  ********