| Subject | Re: How write a query with a progressive sum field |
|---|---|
| Author | |
| Post date | 2016-03-08T09:05:23Z |
I'm looking at your statement and my first instinct was that it should be correct (also for CARICO = 0 and SCARICO = 0). But you need to make sure that they are really filled with 0 and not NULL.
---In firebird-support@yahoogroups.com, <luigisic@...> wrote :
Furthermore... You do a LEFT JOIN with DC2 on DOC_CORPO but there is no ORDER BY for the DC2. Doesn't that mean that DC2 is un-ordered (ordering can be anything). Using the SUM() in that case seems dangerous because now you assume the ordering of DC2 is the same as DC.
And last... you should use the same criteria for DC2 as you did for DC. So:
LEFT JOIN DOC_CORPO DC2 ON DT.ID = DC2.DOC_TESTA_ID
AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID) AND (DC2.DOC_TESTA_ID <= DC2.DOC_TESTA_ID)
But I think that's the same because of the DC.ARTICOLO_ID = :ID but I can't be sure because you didn't provide the complete DDL.
You could try the following:
select
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
(SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
(SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2
WHERE (DC2.DOC_TESTA_ID = DT.ID) AND (DC2.ARTICOLO_ID = DC.ARTICOLO_ID)
AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID) ) AS SALDO
from DOC_TESTA DT
JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID
WHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE
(I'm not sure if you should add DC.DOC_TESTA_ID to the order clause. If that works you should also do an ORDER BY in the sub-select)
---In firebird-support@yahoogroups.com, <luigisic@...> wrote :
select
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
SUM(dc2.carico - DC2.SCARICO) AS saldo
from DOC_TESTA DT
JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
left JOIN DOC_CORPO DC2 ON (DC.DOC_TESTA_ID >= DC2.DOC_TESTA_ID)
AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID)
WHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE
but I have an issue:
if I have a row with CARICO = 0 and SCARICO = 0, SALDO is added or
subtracted by the number present in last row with
CARICO > 0 or SCARICO > 0
if I add "(AND(DC.CARICO + DC.SCARICO) > 0) IN a LEFT JOIN I obtain
almost correct result but the rows that contain "CARICO = 0 and SCARICO
= 0" now contain SALDO = NULL, the others contains correct progressive sum.
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
SUM(dc2.carico - DC2.SCARICO) AS saldo
from DOC_TESTA DT
JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
left JOIN DOC_CORPO DC2 ON (DC.DOC_TESTA_ID >= DC2.DOC_TESTA_ID)
AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID)
WHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE
but I have an issue:
if I have a row with CARICO = 0 and SCARICO = 0, SALDO is added or
subtracted by the number present in last row with
CARICO > 0 or SCARICO > 0
if I add "(AND(DC.CARICO + DC.SCARICO) > 0) IN a LEFT JOIN I obtain
almost correct result but the rows that contain "CARICO = 0 and SCARICO
= 0" now contain SALDO = NULL, the others contains correct progressive sum.