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

0

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's gravatar image

phbraga
41●7●7●9
accept rate: 0%

edited 09 Sep '16, 11:32

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
One Answer:
active answersoldest answersnewest answerspopular answers
1

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
permanent link

answered 07 Sep '16, 06:13

Christian%20Hamers's gravatar image

Christian Ha...
647●13●16●30
accept rate: 42%

edited 12 Sep '16, 06:38

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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

learn more about Markdown

Question tags:

×67

question asked: 06 Sep '16, 15:32

question was seen: 1,544 times

last updated: 12 Sep '16, 15:51

Related questions

table description as result set

Is there a more elegant way to obtain the least of 3 dates?

Select command with 32 UNION ALLs crashs the Android application

What causes Data Loss when Selecting Data from Sql Anywhere Proxy Tables?

IN search condition - expression-list represented by a variable

select two different db from ODBC

Exclude fields that return null on all rows with select *?

Last request type is unknown

Select From Select

How to left join to a stored procedure