Den 07.03.2016 11:16, skrev Luigi Siciliano [email protected]
[firebird-support]:
> Hallo,
> I need to write a query with a computed field that contain a
> progressive sum like this table:
>
> DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO SALDO
> 01/01 A 1 A
> 1 0 1
> 02/01 A 2 A
> 3 0 4
> 02/01 V 33 B
> 0 2 2
> 02/01 V 35 C
> 0 1 1
>
> I need to modify this query by adding SALDO field to get progressive sum
> of CARICO - SCARICO:
> select
> DT.DATA,
> DT.DOCUMENTO_ID,
> DT.NUMERO,
> DT.SERIE,
> DC.CARICO,
> DC.SCARICO
> from
> DOC_TESTA DT
> JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
> WHERE
> DC.ARTICOLO_ID = :ID
> ORDER BY
> DATA,
> DOCUMENTO_ID,
> NUMERO,
> SERIE
>
>
> How I do?
>
> Thanks
Maybe this can be solved like this:
select
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
(SELECT SUM(DC2.CARICO-DC2.SCARICO)
FROM DOC_TESTA DT2
JOIN DOC_CORPO DC2 on DT2.ID = DC2.DOC_TESTA_ID
WHERE DC.ARTICOLO_ID = DC2.ARTICOLO_ID
AND (DT.DATA > DT2.DATA
OR (D2.DATA = DT2.DATA
AND (DT.DOCUMENTO_ID > DT2.DOCUMENTO_ID
OR (DT.DOCUMENTO_ID = DT2.DOCUMENTO_ID
AND (DT.NUMERO > DT2.NUMERO
OR (DT.NUMERO = DT2.NUMERO
AND DT.SERIE >= DT2.SERIE))))))) SALDO
from
DOC_TESTA DT
JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
WHERE
DC.ARTICOLO_ID = :ID
ORDER BY
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE
This will not work properly if more than one row of DC are joined to the
same DT (but then your ORDER BY isn't 100% deterministic). If you change
the ordering of your query, you also have to change the subselect (e.g.
if you add DESC you have to change from > to <).
If this doesn't fit, is too slow or too difficult to understand, I'd
recommend EXECUTE BLOCK (as Sean already wrote).
HTH,
Set
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
