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

[former_SQLA_member1694941]

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...

[VolkerBarth]

Please show us the details of your two tables (i.e. the CREATE TABLE statements and the data (at least some sample data) and the queries you have tried so far...

What SQL Anywhere version do you use ("select @@version")?

[former_SQLA_member1694941]

I tried to exemplify and simplify above, my version is 12.0.1.3152

[VolkerBarth]

Hm, I have not yet understood your real requirements. What result would you expect based on your sample data?

In case you have to somehow "split" the invoices/purchases over a series of stocks (i.e. you have to check whether the fifth invoice belongs to that or the next stock, the following FAQ may give an idea - it's a different real world situation but possibly a similar usage of "the one-dimensional "First Fit" heuristic algorithm, according to Nick:

http://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase

[Chris26]

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