Hi Sergey,
Allmost <g>, this select does add a running total but it totals everything. So this will give met the following result instead of the one I want and is mentioned below.
2002-01-22 00:00:00.000 Ru1 -2.00000 -2.0000
2002-01-28 00:00:00.000 Ru1 -1.00000 -3.0000
2002-04-06 00:00:00.000 Ru3 2.00000 -1.0000
2002-04-24 00:00:00.000 Ru1 -2.00000 -3.0000
2002-05-15 00:00:00.000 Ru1 1.00000 -2.0000
2002-05-16 00:00:00.000 Ru1 -1.00000 -3.0000
2002-08-06 00:00:00.000 Ru3 4.00000 1.0000
There should be a second grouping somewhere, but I can't get that to work.
>Try
Select Date, Code, Sum(Amount) as Total,
> (SELECT Sum(ord2.Amount) From Orders ord2
> Where fk_Customer = 1
> And Date Between '20020101' and '20021231'
> And CONVERT(CHAR(8), ord2.date(), 112) + ord2.code <=
> CONVERT(CHAR(8), orders.date(), 112) + orders.code) AS RunTotal
> From Orders
> Where fk_Customer = 1 And Date Between '20020101' and '20021231'
> Group by Date, Code
> Order by Date, Code
>
>>I have the following select:
>>
>>Select Date, Code, Sum(Amount) as Total From Orders
>> Where fk_Customer = 1 And Date Between '20020101' and '20021231'
>> Group by Date, Code
>>
>>This results in the following set:
>>
>>Date Code Total
>>2002-01-22 00:00:00.000 Ru1 -2.00000
>>2002-01-28 00:00:00.000 Ru1 -1.00000
>>2002-04-06 00:00:00.000 Ru3 2.00000
>>2002-04-24 00:00:00.000 Ru1 -2.00000
>>2002-05-15 00:00:00.000 Ru1 1.00000
>>2002-05-16 00:00:00.000 Ru1 -1.00000
>>2002-08-06 00:00:00.000 Ru3 4.00000
>>
>>
>>Now I want a extra column with the cumulative Total up until that Date for the code in that record. So the result set would look like this:
>>
>>Date Code Total CumTotal
>>2002-01-22 00:00:00.000 Ru1 -2.00000 -2.0000
>>2002-01-28 00:00:00.000 Ru1 -1.00000 -3.0000
>>2002-04-06 00:00:00.000 Ru3 2.00000 2.0000
>>2002-04-24 00:00:00.000 Ru1 -2.00000 -5.0000
>>2002-05-15 00:00:00.000 Ru1 1.00000 -4.0000
>>2002-05-16 00:00:00.000 Ru1 -1.00000 -6.0000
>>2002-08-06 00:00:00.000 Ru3 4.00000 6.0000
>>
>>How can this be done in a set-oriented way?