Da, este bestial! Intre timp, am reusit sa generez toate cele 8 coloane ale balantei, dar mai 'babeste'! Evident, cind am vazut simplitatea si rapiditatea solutiei date de tine am hotarit sa fac asa! Problema e ca am facut asa:
SELECT COALESCE(S.CONT, D.CONT, A.CONT, B.CONT) AS CONT,
ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_DEBITOR,0) AS RULAJ_TOTAL_DEBITOR,
ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_CREDITOR,0) AS RULAJ_TOTAL_CREDITOR,
ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS RULAJ_CURENT_DEBITOR,
ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS RULAJ_CURENT_CREDITOR,
ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_DEBITOR,0)+ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS TOTAL_DEBITOR,
ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_CREDITOR,0)+ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS TOTAL_CREDITOR
FROM SOLDURI_INITIALE S
FULL OUTER JOIN (
SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_DEBITOR
FROM NOTE WHERE DATADOC BETWEEN '20070101' AND '20070131' GROUP BY CONTDB
) D ON S.CONT=D.CONT
FULL OUTER JOIN (
SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CREDITOR
FROM NOTE WHERE DATADOC BETWEEN '20070101' AND '20070131' GROUP BY CONTCR
) C ON C.CONT=ISNULL(S.CONT,D.CONT)
FULL OUTER JOIN (
SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_CURENT_DEBITOR
FROM NOTE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTDB
) A ON A.CONT=ISNULL(S.CONT,D.CONT)
FULL OUTER JOIN (
SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CURENT_CREDITOR
FROM NOTE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTCR
) B ON B.CONT=ISNULL(S.CONT,C.CONT)
ORDER BY CONT
dar imi dubleaza unele inregistrari, adica imi apar de 2 ori conturile care au rulaj curent si pe debit si pe credit...cele care au numai debit sau numai credit ies bine... O alta nelamurire ar fi: cum pot sa atribui un alias cirnatului de operatii (cum este de ex TOTAL_CREDITOR) pt a putea sa fac mai usor operatiile de verificare si scadere necesare generarii coloanelor de sold final debitor/creditor?
Multam pt timpul pierdut cu mine!