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

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...
2367918
accept rate: 0%

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

Question tags:

×47

question asked: 06 Sep '16, 15:32

question was seen: 165 times

last updated: 12 Sep '16, 15:51