I have what should be a simple select statement which I can't get my
head around.

I have a situation where we sell a product line and these details are
held in one table (sell), the table holds the product_key,
selling_date and selling_amount. In another table (payments) we hold
the payment transactions for the sales item, data includes product
key, payment_value and payment date. A number of payments can be made
to fully settle the original selling amount in table sell.

I need to be able to select the transaction and payment date from
table payment which made the sum of payment_value >= selling_amount.

I need to sort the payment table by payment_date, compare the first
payment_value to the selling_value, if selling_value higher then I
need to add the first payment_value with the second, compare and so on
until I find the final transaction.

Current I use a list(payment_amount), list(payment_date) and go
through a very long winded process. Is there a simpler way where I
can loop through each transaction? Or using the TOP x command?

Sorry for the long winded description of my problem.

Roger

Hello Roger.

It looks for me that you could use a window for your select statment
Help File SQL Anywhere® Server - SQL Usage > OLAP Support > Window
functions > Defining a window

SELECT SUM(payment_value) OVER ( PARTITION BY SalesItem
ORDER BY PaymentDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW ) AS Cumulative_Payment
FROM payments join sell on .....
where sell.sellingamount <= Cumulative_Payment

This is out of my fingers. But perhaps you can find your way with this
hint.

HTH
Thomas

Use a window function (Sum) to add the cumulative payments together by
date, and then select the row(s) where the payments exceed selling
amount. Here's a sketch:

SELECT ....
FROM ( SELECT ...
SUM( payment_value ) over (partition by product_key order by
payment_date ) rows between unbounded preceding and current row as
cum_payments
from payments ) DT
JOIN sell on sell.product_key = DT.product_key
WHERE sell.selling_mount < DT.cum_payments

If you need to find the FIRST overpayment, you can embed the above in a
derived table and use another window query (with RANK()) to order the
payments by date, and then select only those rows with a rank of 1.

You may find the following whitepaper useful:

http://www.sybase.com/detail?id=1037447

Glenn
Roger Peffers wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

On Sep 30, 6:45*pm, "Glenn Paulley [Sybase iAnywhere]"
<paul... (AT) ianywhere (DOT) com> wrote:
Thanks Glenn for the help and your reference to your white paper. I
have managed to get close with setting up the query with the proper
table names and I can retrieve the records that = or exceed the
original sales value but I am having trouble following the next part
of your suggestion with the rank() command to extract the first
payment that balanced or exceed the sales value.

As soon as I try to rank from my derived table I no longer get a
result returned where as I am expecting to get the same rows but with
my two additional ranking columns. I assume once this is working I
can then use a where clause to retrieve only rank_date = 1? Any
pointers would be greater appreciated. Current script below:

SELECT
*,
RANK() OVER pay_date AS Rank_Date,
ROW_NUMBER() OVER pay_date AS date_order
FROM
( SELECT
v.no,
dt.trans_date,
dt.VALUE,
dt.cum_payments,
v.sales_value
FROM
( SELECT
stock_no,
trans_date,
VALUE,
SUM(VALUE) OVER( PARTITION BY stock_no ORDER
BY
trans_date ROWS BETWEEN unbounded preceding
AND CURRENT
row ) AS cum_payments
FROM
vhdebtor
WHERE
tran_type NOT IN ('REV')
AND VALUE < 0 ) DT
JOIN vhstock v
ON v.no = DT.stock_no
WHERE
v.SALES_VALUE + DT.cum_payments <= 0
AND v.NO = 200800 ) new_dt
Window pay_date AS (ORDER BY trans_date ASC)
ORDER BY
date_order;

In your case either RANK() or ROW_NUMBER() will be sufficient - you
don't need both. You also need to rank the overpayments for each
product, not for all of them (the entire input) - so the window for your
RANK function must also contain PARTITION BY.

Glenn

Roger Peffers wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport