Thank you for your reply, Lester. I came across CTE during my research, but wasn't sure it was the right thing for this case. Looked more detailed into it and came up with this: with TOTAL_STOCKS as ( select PRODUCTID, STORAGE_AREA_ID_SOURCE, sum(AMOUNT) as TOTAL_STOCK from STOCK_RECORDS where STOCK_RELEVANT = -1 group by PRODUCTID, STORAGE_AREA_ID_SOURCE ) select sr.ID, sr.STORAGE_AREA_ID_SOURCE, sr.INSERTDATE, sr.EDITDATE, sr.PRODUCTID, sr.RECORD_DATE, sr.DELIVERYPOSID as POSID, sr.INSTANCE_TYPE, sr.INSTANCE_ID, abs(sr.AMOUNT) as AMOUNT, sr.STORAGE_AREA_ID_SOURCE, ts.TOTAL_STOCK as RESULTING_AMOUNT from STOCK_RECORDS sr left join TOTAL_STOCKS ts on sr.STORAGE_AREA_ID_SOURCE = ts.STORAGE_AREA_ID_SOURCE and sr.PRODUCTID = ts.PRODUCTID where sr.INSTANCE_TYPE = 2 If that's correct, then there is still one thing I have trouble with: the sum(AMOUNT) aka RESULTING_AMOUNT has to be based on the field EDITDATE, i.e. I need an amount for each row, which was the TOTAL AMOUNT back then, i.e. prior to EDITDATE in the said row. There are actually some more criteria to exclude some rows, but if there is a solution for EDITDATE, that would be similar for the rest. If I do
with TOTAL_STOCKS as ( select PRODUCTID, STORAGE_AREA_ID_SOURCE, sum(AMOUNT) as TOTAL_STOCK from STOCK_RECORDS where STOCK_RELEVANT = -1 and EDITDATE <= :EDITDATE group by PRODUCTID, STORAGE_AREA_ID_SOURCE ) That would apply to all rows. If I pass PRODUCTID and STORAGE_AREA_ID_SOURCE as parameters as well, that wouldn't be a full view of data to work with. Where would I put the EDITDATE part, when using CTE?
