cancel
Showing results for 
Search instead for 
Did you mean: 

How to show records until sum(som evalue) into the select reach a number, ordered by date desc

0 Kudos
2,171

Hi fellows,
I'll try to simplificate
Well, I think, the result I need would be something like this..
limit = 10000 (this value I already have, from the first table I mentioned... )
list (date, inv number, quantity, value) while quantity <= limit ordered by date descending (newest first)

+------------------------------------------------------------
| DATE | NUMBER | QUANT | $ PRICE | Q Total |
+------------------------------------------------------------
| 2016-09-09 | 12345 | 2400 | $ 102.12 | (2400)
+------------------------------------------------------------
| 2016-09-09 | 12344 | 2200 | $ 100.32 | (4600)
+------------------------------------------------------------
| 2016-09-08 | 12343 | 2700 | $ 105.62 | (7300)
+------------------------------------------------------------
| 2016-09-07 | 12342 | 1200 | $ 78.82 | (8500)
+------------------------------------------------------------
| 2016-09-06 | 12341 | 1600 | $ 92.02 |(10100)
+------------------------------------------------------------
Consider this above as a table
The main purpose is, I have an actual stock, in liters,
I need to know witch invoices represent this stock,
this will give me an average of my cost...

Accepted Solutions (0)

Answers (1)

Answers (1)

Chris26
Explorer

I think that the WINDOW clause can help you with this. Probably something like this:

SELECT *
  FROM (SELECT date, 
               number,
               quant,
               SUM(quant) OVER (ORDER BY date DESC 
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS q_total
          FROM invoices) AS t
 WHERE t.q_total < 10000