The forum will be down for maintenance at some point from Friday, August 24 at 19:00 EDT until Sunday, August 26 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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
26457
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...
29181019
accept rate: 50%

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:

×53

question asked: 06 Sep '16, 15:32

question was seen: 589 times

last updated: 12 Sep '16, 15:51