Onfly Query - cumulative sum the stock change values by articles

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Onfly Query - cumulative sum the stock change values by articles
Date: 2021-01-07 09:17:42
Message-ID: CAEcMXhm8oakWjW_KOZAbhvp=Hryx=PmJcz6JxQ242afund7maQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Thank you for the answer!

Best regards

ddd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Northcott 2021-01-07 09:22:25 RE: Keep needing to run manual analyze
Previous Message Laurenz Albe 2021-01-07 02:12:16 Re: SQL to query running transactions with subtransactions that exceeds 64