Queries using row positions

[Guest_imported]

I have a table with some 20 rows.
Each row has a date and an amount.
A date may appear in more than 1 row.
The table is sorted in descending date.
EG 20010418 100
20010418 50
20010418 75
20010417 100
etc etc

I need to write a procedure to handle row 1
differently from each subsequent row.
Row 1 will have a variable added to it's amount(50 say).
Rows 2, 3 ,..... will then subtract the sum of the
rows above from this starting position.
eg
Return Display
Row 1 100 (+ 50) 150
Row 2 50 50
Row 3 75 00
Row 4 100 -75
etc etc
I will probably need a union, with the 1st select returning
the 1st row, & the 2nd select returning the rest, but I am not clever enough to construct the coding.
Please help

 

[MalcolmW]

What database? Malcolm

 

[carp]

Also, could you clarify your algorithm? Maybe it's too close to Monday for me, but your example doesn't seem to match your description.

You state:
Row 1 will have a variable added to it's amount(50 say).
Rows 2, 3 ,..... will then subtract the sum of the
rows above from this starting position.
eg
Return Display
Row 1 100 (+ 50) 150
Row 2 50 50
Row 3 75 00
Row 4 100 -75
etc etc

The example doesn't appear to follow any pattern, and based on your description I would expect to see something like:

Return Display
Row 1 100 (+ 50) 150
Row 2 50 -(100) 50
Row 3 75 -(100+50) -75
Row 4 100-(100+50+75) -125
etc etc

Help me out here!

 

[Guest_imported]

For Malcolm W The DB is Sybase
For Carp In Row 2, Subtract Row 1's 100 from 150
giving 50.
In Row 3, Subtract Row 1's 100 + Row 2's
50 from 150 giving 00.
In Row 4, Subtract 100 + 50 + 75
from 150 giving - 75.
etc etc
Many thanks guys.
Sorry if I wasn't clear.

 

[MalcolmW]

To aid my understanding, I have created a couple of psuedo formulas.
col2 in Row 1 - sum of current row plus a variable
col2 in Row x - prior row(col2 - col1)

In other words, you have two different formulas for calculating the value, depending on the position of the row. Furthermore, your second formula (based on the prior row) is also "positional".

As SQL is a set based language, and theoretically, row order is irrelevant, this makes your problem difficult to solve in SQL. To get around this problem, you need to use a process based language, or use cursors, or delegate the problem to someone else.

Malcolm

 

[Guest_imported]

For MalcolmW
If I can update my table by "timestamping" a spare field in each row, then row 1 would have the lowest value, & each subsequent row would have a progressively higher value.
My Union would then look something like :-
select ..... from table where date = (select min(date) from)
union
select .... etc & exclude the above record.
But how do I update table
set date = ????,
picking up the current date (YYMMDDHHMMSS) as I proceed through each row ?
This will be run from within a Stored Procedure.