Welcome to Sign in | Help

Re: Modalitate de generare a "Jurnalului de cumparari"...

  •  06-07-2007, 8:14 AM

    Re: Modalitate de generare a "Jurnalului de cumparari"...

    Erai destul de aproape:

    SELECT CONT, RULAJ_INITIAL_DEBITOR, RULAJ_INITIAL_CREDITOR,
        RULAJ_CURENT_DEBITOR, RULAJ_CURENT_CREDITOR,
        RULAJ_TOTAL_DEBITOR, RULAJ_TOTAL_CREDITOR,
        CASE WHEN RULAJ_TOTAL_DEBITOR>RULAJ_TOTAL_CREDITOR
            THEN RULAJ_TOTAL_DEBITOR-RULAJ_TOTAL_CREDITOR
            ELSE 0
        END AS SOLD_DEBITOR,
        CASE WHEN RULAJ_TOTAL_CREDITOR>RULAJ_TOTAL_DEBITOR
            THEN RULAJ_TOTAL_CREDITOR-RULAJ_TOTAL_DEBITOR
            ELSE 0
        END AS SOLD_CREDITOR

    FROM (
        SELECT COALESCE(S.CONT, D.CONT, C.CONT, A.CONT, B.CONT) AS CONT,
            ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_INITIAL_DEBITOR,0) AS RULAJ_INITIAL_DEBITOR,
            ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_INITIAL_CREDITOR,0) AS RULAJ_INITIAL_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_INITIAL_DEBITOR,0)
                +ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS RULAJ_TOTAL_DEBITOR,
            ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_INITIAL_CREDITOR,0)
                +ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS RULAJ_TOTAL_CREDITOR
        FROM SOLDURI_INITIALE S

        FULL OUTER JOIN (
            SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_INITIAL_DEBITOR
            FROM NOTE_CONTABILE 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_INITIAL_CREDITOR
            FROM NOTE_CONTABILE 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_CONTABILE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTDB
        ) A ON A.CONT=COALESCE(S.CONT,D.CONT,C.CONT)

        FULL OUTER JOIN (
            SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CURENT_CREDITOR
            FROM NOTE_CONTABILE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTCR
        ) B ON B.CONT=COALESCE(S.CONT,D.CONT,C.CONT,A.CONT)
    ) X
    ORDER BY CONT

    Răzvan 

View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems