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

 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... asked 06 Sep '16, 15:32 phbraga 26●4●4●6 accept rate: 0% 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")? (07 Sep '16, 00:59) Volker Barth I tried to exemplify and simplify above, my version is 12.0.1.3152 (09 Sep '16, 11:36) phbraga 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 (12 Sep '16, 15:51) Volker Barth

 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`````` answered 07 Sep '16, 06:13 Christian Ha... 236●8●9●18 accept rate: 0%
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×52

question asked: 06 Sep '16, 15:32

question was seen: 418 times

last updated: 12 Sep '16, 15:51