Differences of grade!

Asked

Viewed 39 times

0

I have a situation where I need to validate the records that are in the books and the records that are in financial. I know there’s 51 bills in accounting and 52 in finance. I need to assemble the query in a way that only brings me this excess note in financial, which is considered difference. Could someone help me?

Follow the query below:

SELECT * FROM 
(SELECT 
    TIPO,
    CODPARC,
    NUNICO,
    SUM(VLR) AS VLR,
    CODPROJ,
    CODNAT,
    CODTIPOPER 
FROM 
    (SELECT 
            'ESTÁ NA CONTABILIDADE' AS TIPO,
            NULL AS CODPARC,
        BINT.NUNICO AS NUNICO,
        SUM(LAN.VLRLANC) AS VLR,
        NULL AS CODPROJ,
        NULL AS CODNAT,
        NULL AS CODTIPOPER
        
    FROM 
        TCBLAN LAN JOIN TCBINT BINT ON LAN.NUMLANC=BINT.NUMLANC AND LAN.NUMLOTE=BINT.NUMLOTE AND LAN.REFERENCIA=BINT.REFERENCIA AND LAN.TIPLANC = BINT.TIPLANC AND LAN.CODEMP=BINT.CODEMP
    WHERE 
        LAN.DTMOV >= :DATA.INI
        AND LAN.DTMOV <=  :DATA.FIN
        AND LAN.CODCTACTB = (SELECT CODCTACTB FROM AD_CONFIGCART WHERE CARTEIRA = :CARTEIRA)
        AND LAN.CODEMP IN (2,29,30,33,35)
        AND LAN.TIPLANC = 'R'       
    GROUP BY BINT.NUNICO

UNION

    SELECT
        'ESTÁ NA CONTABILIDADE' AS TIPO,
            NULL AS CODPARC,
        BINT.NUNICO AS NUNICO,
        SUM(LAN.VLRLANC) AS VLR,
        NULL AS CODPROJ,
        NULL AS CODNAT,
        NULL AS CODTIPOPER
    FROM 
        TCBLAN LAN JOIN TCBINT BINT ON LAN.NUMLANC=BINT.NUMLANC AND LAN.NUMLOTE=BINT.NUMLOTE AND LAN.REFERENCIA=BINT.REFERENCIA AND LAN.TIPLANC = BINT.TIPLANC AND LAN.CODEMP=BINT.CODEMP
    WHERE 
        LAN.DTMOV >= :DATA.INI
        AND LAN.DTMOV <=  :DATA.FIN
        AND LAN.CODCTACTB = (SELECT CODCTACTB FROM AD_CONFIGCART WHERE CARTEIRA = :CARTEIRA)
        AND LAN.CODEMP IN (2,29,30,33,35)
        AND LAN.TIPLANC = 'D'   
    GROUP BY BINT.NUNICO)A
GROUP BY 
    TIPO,
    CODPARC,
    NUNICO,
    CODPROJ,
    CODNAT,
    CODTIPOPER)B
JOIN 
(SELECT 
        TIPO,
    CODPARC
        NUNICO,
        VLR,
    CODPROJ,
    CODNAT,
    CODTIPOPER
FROM 
    (SELECT 
    'ESTÁ NO FINANCEIRO' AS TIPO,
    FIN.CODPARC, 
    FIN.NUFIN AS NUNICO,
    (FIN.VLRDESDOB) AS VLR,
    FIN.CODPROJ,
    FIN.CODNAT,
    FIN.CODTIPOPER
    FROM 
        TGFFIN FIN
    WHERE
        FIN.DHBAIXA BETWEEN :DATA.INI AND :DATA.FIN
        AND FIN.ORIGEM = 'F'
        AND FIN.PROVISAO = 'N'
        AND ( FIN.CODNAT IN 
            (SELECT CODNAT FROM AD_NATCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_NATCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( FIN.CODNAT NOT IN 
            (SELECT CODNAT FROM AD_NATCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_NATCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL)
        
        AND ( FIN.CODPROJ IN 
            (SELECT CODPROJ FROM AD_PROJCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA =:CARTEIRA ) 
            OR 
            (SELECT DISTINCT 1 FROM AD_PROJCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( FIN.CODPROJ NOT IN 
            (SELECT CODPROJ FROM AD_PROJCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_PROJCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL)
        AND ( FIN.CODTIPOPER IN 
            (SELECT CODTIPOPER FROM AD_TOPCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_TOPCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( FIN.CODTIPOPER NOT IN 
            (SELECT CODTIPOPER FROM AD_TOPCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_TOPCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL)
        AND ( FIN.CODTIPTIT IN 
            (SELECT CODTIPTIT FROM AD_TITCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_TITCART WHERE INCEXC = 1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( FIN.CODTIPTIT NOT IN 
            (SELECT CODTIPTIT FROM AD_TITCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_TITCART WHERE INCEXC = -1 AND (CABFIN=2 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL)
        
UNION

    SELECT  
    'ESTÁ NO FINANCEIRO' AS TIPO,
    CAB.CODPARC, 
        CAB.NUNOTA,
        (CAB.VLRNOTA) AS VLR,
    CAB.CODPROJ,
    CAB.CODNAT,
    CAB.CODTIPOPER
    FROM 
        TGFCAB CAB
    WHERE
        CAB.DTNEG BETWEEN :DATA.INI AND :DATA.FIN
        AND CAB.CODEMP IN (2,29,30,33,35)AND ( CAB.CODNAT IN 
            (SELECT CODNAT FROM AD_NATCART WHERE INCEXC = 1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_NATCART WHERE INCEXC = 1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( CAB.CODNAT NOT IN 
            (SELECT CODNAT FROM AD_NATCART WHERE INCEXC = -1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_NATCART WHERE INCEXC = -1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL)
        
        AND ( CAB.CODPROJ IN 
            (SELECT CODPROJ FROM AD_PROJCART WHERE INCEXC = 1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA =:CARTEIRA ) 
            OR 
            (SELECT DISTINCT 1 FROM AD_PROJCART WHERE INCEXC = 1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( CAB.CODPROJ NOT IN 
            (SELECT CODPROJ FROM AD_PROJCART WHERE INCEXC = -1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_PROJCART WHERE INCEXC = -1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL)
        AND ( CAB.CODTIPOPER IN 
            (SELECT CODTIPOPER FROM AD_TOPCART WHERE INCEXC = 1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_TOPCART WHERE INCEXC = 1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL) 
        AND ( CAB.CODTIPOPER NOT IN 
            (SELECT CODTIPOPER FROM AD_TOPCART WHERE INCEXC = -1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) 
            OR 
            (SELECT DISTINCT 1 FROM AD_TOPCART WHERE INCEXC = -1 AND (CABFIN=1 OR CABFIN=0) AND CARTEIRA = :CARTEIRA) IS NULL))C)D ON D.NUNICO=B.NUNICO

WHERE 
    D.NUNICO<>B.NUNICO
sql sql-server

  • just look at the query becomes difficult to help (especially a large query like yours), need to put the structure of tables and data examples

  • Roughly a "Outer Join" testing the missing key (null) note?! , or a "not exists", in Oracle we do "Minus" it seems that EXCEPT makes this papal in sql-server https://www.sqlshack.com/understanding-the-sql-except-statement-with-examples/

  • Any kind of help becomes difficult without further details. Your problem seems to be specific to your business. As a suggestion you could assemble a generic template with tables, some data and what you have tried and failed to do.

No answers

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.