Re: aggregate function ?

--- Raimon Fernandez <coder(at)montx(dot)com> wrote:
>
> compte: 572 0000 01
> date init: 15/01/2007
> date end: 30/01/2007
>
> The initial sum, would be the compte field plus a date init of the
> year, in that case, 01/01/2007 to the date init, 15/01/2007. Here
> we'll get the SUM of the two values (deure-haver), and this would be
> the initial value of the sum_value to add, so the code would be:
>
> SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE
> assentaments.compte='572000001' and assentaments.data >='2007-01-15'
> and assentaments.data <='2007-01-30';

So if I understand you correctly:

SELECT oid, concepte, deure, haver,
delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver ) )
FROM Assentaments A3
WHERE A3.compte = '572000001'
AND A1.data BETWEEN '2007-01-15' AND '2007-01-30' ) AS starting_Sum
FROM( SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A2.deure, 0 ) -
COALESCE( A2.haver, 0 )) AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid >= A2.oid
AND A1.numero = A2.numero
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte, deure, haver, delta_sum );

Regards,
Richard Broersma Jr.