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?
--sb--