Re: Onfly Query - cumulative sum the stock change values by articles

Hello!

Durumdara <durumdara(at)gmail(dot)com> ezt írta (időpont: 2021. jan. 7., Cs,
10:17):

> Hello!
>
> I have a query where I can show the positive and negative future changes
> of the articles.
> For example:
>
> select art.id, art.name, art.actual_stock, art.min_stock,
> change.stock_change, change.date
> from change left join art on art.id = change.art_id
> order by art.id, change.id
>
> Ok, I have a list of the changes.
>
> I need to sum these changes to get the rows where the stock is negative or
> below the minimum.
>
> 1, bla, 100, 20, +20, 2021-02-01, [120]
> 1, bla, 100, 20, -10, 2021-02-01, [110]
> 1, bla, 100, 20, -100, 2021-02-01, [10] - below minimum
> 1, bla, 100, 20, -20, 2021-02-01, [-10] - below zero
> 2, bli, 20, 10, 10, 2021-02-01, [20]
> 2, bli, 20, 10, -15, 2021-02-01, [5] below minimum
>
> How do I use a window function (can I use it) to "reset" the stock in
> every first record of the articles - to cumulate the data?
>
> Or do I need to store data in a temporary table and use stored procedure
> to fill up the cumulated stock value?
>
>
>
Sorry... I solved it!

First I need to window sum "change.stock_change" (partition by art.id),
then I need to add the starting stock value to all.
In this case I don't need to check what is the first item of the article
(where I need to add the actual stock).

Best wishes
dd