First of all, if this is answered somewhere, I apologize.

I'm trying to wrap my head around the following problem. I have a query that returns the following values from the inventory regarding one product:

This part is easy but the result should look like this:

and from that the final value is 800 pcs on stock:

In CurrentStock column is calculated value based on Entry, Sale and StockTaking columns. When we have stock-taking at the start is simple (for this sample let's say that all products start with stock-taking), in RowId = 1 we have CurrentStock identical to StockTaking value.

In RowId = 2 we have, entry of 1200 pcs, so CurrentStock is now 2880 1200 = 4080 pcs....

Now the problem arises in RowID = 5, CurrentStock before StockTaking was 2480 pcs, but we found only 2000 pcs

So we are taking StockTaking value as CurrentStock value so we can calculate from that value forward, and at the same time to level the columns we are making an exit for 480 pcs which are missing at that time.

I managed to handle this over some temp table and then iterating trough the records and updating the CurrentStock value but with larger dataset, it takes some time.

Any suggestions how to handle this with CTEs to avoid writing to temp tables is greatly appreciated.

Thanks

CodePudding user response：

A bit quick n dirty solution:

select  *
,   SUM(StockTaking   CASE WHEN StockTaking <= 0 THEN Entry - [Exit] ELSE 0 END) OVER(PARTITION BY StockGroup ORDER BY RowId) CurrentStock
from (
    select  COUNT(CASE WHEN StockTaking > 0 THEN 1 END) OVER(ORDER BY RowID) AS StockGroup
    ,   *
    from 
    (
        VALUES  (1, 0, 0, 2880)
        ,   (2, 1200, 0, 0)
        ,   (3, 0, 800, 0)
        ,   (4, 0, 800, 0)
        ,   (5, 0, 480, 2000)
        ,   (6, 600, 0, 0)
        ,   (7, 0, 800, 0)
        ,   (8, 0, 1000, 0)
    ) t (RowId,Entry,[Exit],StockTaking)
) x

First you create groups of rows by counting each inventory row as start of a group. Then you summarize changes to the stock sorted by rowID.

Important that for rows where stocktaking is taken, we don't add Entry / Exit values. So if you first do inventory and then take stock on the same "row", it will display wrong results. Probably you have info that can solve this problem, but it's not in your example.