Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello!


Durumdara <durumdara@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux