Hello!
Durumdara <durumd...@gmail.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
