Calculating balance and separation by currency

Question

Calculating balance and separation by currency

Asked 5 years, 4 months ago

Viewed 73 times

-1

I am in need of help to do a function that I can know the balance of customers' invoices and that the value is separated by coins of invoices (for example 250 USD | 340 EUR) because there are invoices with different currencies. In addition there are requirements to be met for invoices to enter into the balance account.

I will leave a table print and show a select that I had already done that contemplates other fields that I will also use to build a dynamic list that I am doing.

The idea of the list is to show Customer per customer, without repetition, with all invoices competing with the requirements and your balance according to the currencies that exist in the invoices and would like help to do this because I do not know how to make this calculation.

        SELECT * FROM (
    Select      -- Campos fatura
         FATU.ID_CD_FATURA
        ,FATU.ID_CD_BOOKING
        ,FATU.ID_CD_QUEM_PAGA --> indica que o valor vai ser a ser somado para obter o saldo
        ,FATU.ID_CD_QUEM_RECEBE --> indica que o valor vai ser subtraído para obter o saldo
        ,FATU.ID_CD_FATURA_TIPO
        ,FATU.ID_CD_LOGIN
        ,FATU.IN_STATUS
        ,FATU.DH_CADASTRO
        ,FATU.DH_VENCIMENTO
        ,FATU.ID_CD_FATURA_REL

        ,NM_PAGREC              = (Select
                                        CASE WHEN CRMP.IN_TIPO = 'F' THEN
                                            CRMP.NM_PESSOA+' '+CRMP.NM_SOBRENOME +'<br>'+convert(varchar,CRMP.NR_CPF_CNPJ)
                                        ELSE
                                            CRMP.NM_APELIDO  +'<br>'+convert(varchar,CRMP.NR_CPF_CNPJ)
                                        END
                                    From 
                                        TB_CRM_PESSOAS_CRMP     CRMP
                                    Where 
                                        ID_CD_PESSOA            = FATU.ID_CD_QUEM_PAGA OR ID_CD_PESSOA = FATU.ID_CD_QUEM_RECEBE
                                    )
        ,ID_CD_PESSOA   = (Select
                                        CASE WHEN CRMP.IN_TIPO = 'F' THEN
                                            CRMP.ID_CD_PESSOA
                                        ELSE
                                            CRMP.ID_CD_PESSOA
                                        END
                                    From 
                                        TB_CRM_PESSOAS_CRMP     CRMP
                                    Where 
                                        ID_CD_PESSOA            = FATU.ID_CD_QUEM_PAGA OR ID_CD_PESSOA = FATU.ID_CD_QUEM_RECEBE
                                    ) -- Campo do ID do Cliente a ser utilizado para listar
        -- TIPO DE FATURA
        ,FATU.FL_CONV_MOEDA_BRL 
        ,FATP.DS_TITULO         AS DS_FATURA_TIPO
        ,FATP.DS_TITULO_EN      AS DS_FATURA_TIPO_EN

        -- BOOKING
        ,BOOK.FL_IMPOEXPO
        ,BOOK.DS_BOOK_NUMERO
        ,BOOK.DS_AMS_REFERENCE
        ,NM_AGENCIA_AUX             = (Select Case When IN_TIPO = 'J' Then NM_APELIDO Else NM_PESSOA + ' ' + NM_SOBRENOME End From TB_CRM_PESSOAS_CRMP CRMP6 Where CRMP6.ID_CD_PESSOA = BOOK.ID_CD_AGENCIA_AUX)
        ,NM_ARMADOR_AUX             = (Select Case When IN_TIPO = 'J' Then NM_APELIDO Else NM_PESSOA + ' ' + NM_SOBRENOME End From TB_CRM_PESSOAS_CRMP CRMP7 Where CRMP7.ID_CD_PESSOA = BOOK.ID_CD_ARMADOR_AUX)

        -- ADICIONAIS
        ,TEM_FECHAMENTO.FL_TEM_FECHAMENTO
        ,TEM_FECHAMENTO.ID_CD_FATURA_PRI

        -- FINANCEIRO
        ,TEM_LANCAMENTO.ID_CD_LANCAMENTO
        ,TEM_LANCAMENTO.FL_COMPENSADO 
        ,TEM_LANCAMENTO.DT_ULTIMO_PAGTO

        ,COALESCE(VL_FATURA_TOTAL,0)        AS VL_FATURA_TOTAL
        ,COALESCE(VL_FATURA_TOTAL_MOEDA,0)  AS VL_FATURA_TOTAL_MOEDA -> Valor para o calculo
        ,DS_MOEDA_PRINCIPAL  = (SELECT TOP(1) CASE WHEN FATU.ID_CD_QUEM_PAGA IS NOT NULL THEN FAIT.DS_MOEDA_VENDA ELSE FAIT.DS_MOEDA_CUSTO END DS_MOEDA_PRINCIPAL FROM TB_FATURA_ITENS_FAIT FAIT WHERE FAIT.ID_CD_FATURA = FATU.ID_CD_FATURA)
    From 
        TB_FATURA_FATU              FATU INNER JOIN
        TB_BOOKING_BOOK             BOOK ON BOOK.ID_CD_BOOKING      = FATU.ID_CD_BOOKING        INNER JOIN
        TB_FATURAS_TIPOS_FATP       FATP ON FATP.ID_CD_FATURA_TIPO  = FATU.ID_CD_FATURA_TIPO    INNER JOIN
        TB_LOGIN_LOGI               LOGI ON LOGI.ID_CD_LOGIN        = FATU.ID_CD_LOGIN
        OUTER APPLY (
            Select top 1 
                FL_TEM_FECHAMENTO = 'S'
                ,FAPR2.ID_CD_FATURA_PRI 
            From 
                TB_FATURA_AUX_FPAX FPAX2 INNER JOIN 
                TB_FATURA_PRINCIPAL_FAPR FAPR2 ON FAPR2.ID_CD_FATURA_PRI = FPAX2.ID_CD_FATURA_PRI  
            Where 
                FPAX2.ID_CD_FATURA = FATU.ID_CD_FATURA AND FAPR2.IN_STATUS = 1
        ) AS TEM_FECHAMENTO

        OUTER APPLY (    
                        SELECT TOP 1 
                            ID_CD_LANCAMENTO  
                            ,FL_COMPENSADO = ( SELECT TOP 1 
                                                    'N'
                                                FROM 
                                                    TB_FIN_LANCAMENTOS_PARC_FNLP FNLP2 
                                                WHERE 
                                                    FNLP2.ID_CD_LANCAMENTO  = FNLP.ID_CD_LANCAMENTO
                                                AND FNLP2.ID_CD_AUXILIAR    = FATU.ID_CD_FATURA
                                                AND FNLP2.FL_AUXILIAR       = 'FAT'  
                                                AND FNLP2.FL_SITUACAO       = 'A')
                            ,DT_ULTIMO_PAGTO = ( SELECT TOP 1 
                                                    FNLP3.DT_COMPENSADO
                                                FROM 
                                                    TB_FIN_LANCAMENTOS_PARC_FNLP FNLP3 
                                                WHERE 
                                                    FNLP3.ID_CD_LANCAMENTO  = FNLP.ID_CD_LANCAMENTO
                                                AND FNLP3.ID_CD_AUXILIAR    = FATU.ID_CD_FATURA
                                                AND FNLP3.FL_AUXILIAR       = 'FAT'  
                                                AND (FNLP3.FL_SITUACAO      = 'C' OR FNLP3.FL_SITUACAO      = 'G')
                                                ORDER BY ID_CD_PARCELA DESC)
                        FROM 
                            TB_FIN_LANCAMENTOS_PARC_FNLP FNLP 
                        WHERE 
                            FNLP.ID_CD_AUXILIAR = FATU.ID_CD_FATURA
                        AND FNLP.FL_AUXILIAR    = 'FAT'  
                        AND FNLP.FL_CANCELADO   = 'N'
                    ) AS TEM_LANCAMENTO
                        WHERE ID_CD_FATURA <> '' ) TB       
                        WHERE ((ID_CD_FATURA_TIPO = '3' AND IN_STATUS = '1') AND (ID_CD_QUEM_PAGA = @ID_CD_PESSOA OR ID_CD_QUEM_RECEBE = @ID_CD_PESSOA) AND ((FL_TEM_FECHAMENTO = '' OR FL_TEM_FECHAMENTO = NULL) AND (ID_CD_LANCAMENTO = '' OR ID_CD_LANCAMENTO = NULL)) -- Aqui tem os requisitos das faturas que devem cair na regra para ter um saldo do cliente.

If there’s something I’ve explained, please tell me I’m trying to clarify the question.

Thanks in advance for your help.

Result I hope to have is this below

Ideally you put the data structure (CREATE TABLE) with some data available (INSERT INTO) and an example of what you would expect with the sample data, so we can test several possibilities and present them to you with the already validated data

– Sorack

2019/08/01 at 13:16
Is the value of the invoice expressed in the currency indicated in DS_MOEDA_PRINCIPAL or is it expressed in any other standard currency? What are the other currencies that exist in the invoices since, it seems to me, there is only one field indicating the currency?

– anonimo

2019/08/01 at 13:22
I think I understand, is that as I use other functions within a previous to complete this listing, it would be too big to post here, but I will update with a Select and what I expect the result.

– Pietro Rey

2019/08/01 at 13:23
@anonimo the value of the invoice is in VL_FATURA_TOTAL_MOEDA and the type of the invoice currency is DS_MOEDA_PRINCIPAL. Each invoice has a value and a currency, the point is that what I need is a kind of grouping of invoices so that there is a balance for each currency if there is more than one. For example, I can have 6 invoices in USD and one in EUR, logically the values are different so I needed them separate.

– Pietro Rey

2019/08/01 at 13:26
1

In this case a GROUP BY client, currency with the SUM aggregation function over the value of the invoice, would not suffice?

– anonimo

2019/08/01 at 13:29
How I can do Group BY and SUM without losing the requirements that invoices have to meet?

– Pietro Rey

2019/08/01 at 13:42
To query that you presented is very complex and without sample data to use it is complicated to suggest an answer. Ideally you demonstrate your problem by using some Fiddle like the DB Fiddle or the SQL Fiddle.

– Sorack

2019/08/02 at 12:59
I am not able to use Fiddle, as they are several tables with FK. but I uploaded an excel with the result of my script. https://www.dropbox.com/s/msm04wae6g595vu/Pasta1.xlsx?dl=0 The idea is that I can have a balance of each client’s VL_FATURA_TOTAL_MOEDA, remembering that we have ID_CD_QUEM_PAGA (+) AND ID_CD_QUEM_RECEBE (-) AND YOUR DS_MOEDA_PRINCIPAL, for example if there is an invoice in USD and another in BRL two lines of the same customer with the balance referring to the currencies.

– Pietro Rey

2019/08/05 at 16:52

Show 3 more comments

2 answers

0

Good guys, I was able to solve the situation I had with some insights I had with everyone’s answer. Basically I did the select that I already had twice, bringing only the fields "To pay" and "To receive" if stopped and then made a third party select to be able to bring the information formatted and with the exceptions handled properly. I’m gonna drop my full code in case anyone has a similar question.

;WITH [PAGA] AS (SELECT     ID_CD_QUEM_PAGA,ID_CD_PESSOA,NM_PAGREC, ISNULL(SUM(COALESCE(VL_FATURA_TOTAL_MOEDA,0)),0)as TOTAL_ARECEBER,DS_MOEDA_PRINCIPAL  FROM(
    Select      
         FATU.ID_CD_FATURA
        ,FATU.ID_CD_BOOKING
        ,FATU.ID_CD_QUEM_PAGA
        ,FATU.ID_CD_QUEM_RECEBE
        ,FATU.ID_CD_FATURA_TIPO
        ,FATU.IN_STATUS
        ,FATU.ID_CD_FATURA_REL
        ,NM_PAGREC              = (Select
                                        CASE WHEN CRMP.IN_TIPO = 'F' THEN
                                            CRMP.NM_PESSOA+' '+CRMP.NM_SOBRENOME +'<br>'+convert(varchar,CRMP.NR_CPF_CNPJ)
                                        ELSE
                                            CRMP.NM_APELIDO  +'<br>'+convert(varchar,CRMP.NR_CPF_CNPJ)
                                        END
                                    From 
                                        TB_CRM_PESSOAS_CRMP     CRMP
                                    Where 
                                        ID_CD_PESSOA            = FATU.ID_CD_QUEM_PAGA OR ID_CD_PESSOA = FATU.ID_CD_QUEM_RECEBE
                                    )
        ,ID_CD_PESSOA   = (Select
                                        CASE WHEN CRMP.IN_TIPO = 'F' THEN
                                            CRMP.ID_CD_PESSOA
                                        ELSE
                                            CRMP.ID_CD_PESSOA
                                        END     
                                    From 
                                        TB_CRM_PESSOAS_CRMP     CRMP
                                    Where 
                                        ID_CD_PESSOA            = FATU.ID_CD_QUEM_PAGA OR ID_CD_PESSOA = FATU.ID_CD_QUEM_RECEBE
                                    )
        -- TIPO DE FATURA
        ,FATU.FL_CONV_MOEDA_BRL 

        -- BOOKING
        ,BOOK.DS_AMS_REFERENCE

        -- ADICIONAIS
        ,TEM_FECHAMENTO.FL_TEM_FECHAMENTO
        ,TEM_FECHAMENTO.ID_CD_FATURA_PRI

        -- FINANCEIRO
        ,TEM_LANCAMENTO.ID_CD_LANCAMENTO
        ,TEM_LANCAMENTO.FL_COMPENSADO 


        ,COALESCE(VL_FATURA_TOTAL,0)        AS VL_FATURA_TOTAL
        ,COALESCE(VL_FATURA_TOTAL_MOEDA,0)  AS VL_FATURA_TOTAL_MOEDA
        ,DS_MOEDA_PRINCIPAL  = (SELECT TOP(1) CASE WHEN FATU.ID_CD_QUEM_PAGA IS NOT NULL THEN FAIT.DS_MOEDA_VENDA ELSE FAIT.DS_MOEDA_CUSTO END DS_MOEDA_PRINCIPAL FROM TB_FATURA_ITENS_FAIT FAIT WHERE FAIT.ID_CD_FATURA = FATU.ID_CD_FATURA)
    From 
        TB_FATURA_FATU              FATU INNER JOIN
        TB_BOOKING_BOOK             BOOK ON BOOK.ID_CD_BOOKING      = FATU.ID_CD_BOOKING        INNER JOIN
        TB_FATURAS_TIPOS_FATP       FATP ON FATP.ID_CD_FATURA_TIPO  = FATU.ID_CD_FATURA_TIPO    INNER JOIN
        TB_LOGIN_LOGI               LOGI ON LOGI.ID_CD_LOGIN        = FATU.ID_CD_LOGIN
        OUTER APPLY (
            Select top 1 
                FL_TEM_FECHAMENTO = 'S'
                ,FAPR2.ID_CD_FATURA_PRI 
            From 
                TB_FATURA_AUX_FPAX FPAX2 INNER JOIN 
                TB_FATURA_PRINCIPAL_FAPR FAPR2 ON FAPR2.ID_CD_FATURA_PRI = FPAX2.ID_CD_FATURA_PRI  
            Where 
                FPAX2.ID_CD_FATURA = FATU.ID_CD_FATURA AND FAPR2.IN_STATUS = 1
        ) AS TEM_FECHAMENTO

        OUTER APPLY (    
                        SELECT TOP 1 
                            ID_CD_LANCAMENTO  
                            ,FL_COMPENSADO = ( SELECT TOP 1 
                                                    'N'
                                                FROM 
                                                    TB_FIN_LANCAMENTOS_PARC_FNLP FNLP2 
                                                WHERE 
                                                    FNLP2.ID_CD_LANCAMENTO  = FNLP.ID_CD_LANCAMENTO
                                                AND FNLP2.ID_CD_AUXILIAR    = FATU.ID_CD_FATURA
                                                AND FNLP2.FL_AUXILIAR       = 'FAT'  
                                                AND FNLP2.FL_SITUACAO       = 'A')
                            ,DT_ULTIMO_PAGTO = ( SELECT TOP 1 
                                                    FNLP3.DT_COMPENSADO
                                                FROM 
                                                    TB_FIN_LANCAMENTOS_PARC_FNLP FNLP3 
                                                WHERE 
                                                    FNLP3.ID_CD_LANCAMENTO  = FNLP.ID_CD_LANCAMENTO
                                                AND FNLP3.ID_CD_AUXILIAR    = FATU.ID_CD_FATURA
                                                AND FNLP3.FL_AUXILIAR       = 'FAT'  
                                                AND (FNLP3.FL_SITUACAO      = 'C' OR FNLP3.FL_SITUACAO      = 'G')
                                                ORDER BY ID_CD_PARCELA DESC)
                        FROM 
                            TB_FIN_LANCAMENTOS_PARC_FNLP FNLP 
                        WHERE 
                            FNLP.ID_CD_AUXILIAR = FATU.ID_CD_FATURA
                        AND FNLP.FL_AUXILIAR    = 'FAT'  
                        AND FNLP.FL_CANCELADO   = 'N'
                    ) AS TEM_LANCAMENTO
                        WHERE ID_CD_FATURA <> '' ) TB       
                        WHERE ((ID_CD_FATURA_TIPO = '3' AND IN_STATUS = '1' ) AND (ID_CD_FATURA_PRI IS NULL AND ID_CD_LANCAMENTO IS NULL )) AND ID_CD_QUEM_PAGA <> ''
                        GROUP BY
                        ID_CD_QUEM_PAGA,ID_CD_PESSOA,DS_MOEDA_PRINCIPAL,NM_PAGREC,DS_MOEDA_PRINCIPAL )

[RECEBE] AS (SELECT ID_CD_QUEM_RECEBE,ID_CD_PESSOA,NM_PAGREC, isnull(SUM(COALESCE(VL_FATURA_TOTAL_MOEDA,0)),0)as TOTAL_APAGAR,DS_MOEDA_PRINCIPAL  FROM(
                Select      
                     FATU.ID_CD_FATURA
                    ,FATU.ID_CD_BOOKING
                    ,FATU.ID_CD_QUEM_PAGA
                    ,FATU.ID_CD_QUEM_RECEBE
                    ,FATU.ID_CD_FATURA_TIPO
                    ,FATU.IN_STATUS
                    ,FATU.ID_CD_FATURA_REL
                    ,NM_PAGREC              = (Select
                                                    CASE WHEN CRMP.IN_TIPO = 'F' THEN
                                                        CRMP.NM_PESSOA+' '+CRMP.NM_SOBRENOME +'<br>'+convert(varchar,CRMP.NR_CPF_CNPJ)
                                                    ELSE
                                                        CRMP.NM_APELIDO  +'<br>'+convert(varchar,CRMP.NR_CPF_CNPJ)
                                                    END
                                                From 
                                                    TB_CRM_PESSOAS_CRMP     CRMP
                                                Where 
                                                    ID_CD_PESSOA            = FATU.ID_CD_QUEM_PAGA OR ID_CD_PESSOA = FATU.ID_CD_QUEM_RECEBE
                                                )
                    ,ID_CD_PESSOA   = (Select
                                                    CASE WHEN CRMP.IN_TIPO = 'F' THEN
                                                        CRMP.ID_CD_PESSOA
                                                    ELSE
                                                        CRMP.ID_CD_PESSOA
                                                    END
                                                From 
                                                    TB_CRM_PESSOAS_CRMP     CRMP
                                                Where 
                                                    ID_CD_PESSOA            = FATU.ID_CD_QUEM_PAGA OR ID_CD_PESSOA = FATU.ID_CD_QUEM_RECEBE
                                                )
                    -- TIPO DE FATURA
                    ,FATU.FL_CONV_MOEDA_BRL 

                    -- BOOKING
                    ,BOOK.DS_AMS_REFERENCE

                    -- ADICIONAIS
                    ,TEM_FECHAMENTO.FL_TEM_FECHAMENTO
                    ,TEM_FECHAMENTO.ID_CD_FATURA_PRI

                    -- FINANCEIRO
                    ,TEM_LANCAMENTO.ID_CD_LANCAMENTO
                    ,TEM_LANCAMENTO.FL_COMPENSADO 


                    ,COALESCE(VL_FATURA_TOTAL,0)        AS VL_FATURA_TOTAL
                    ,COALESCE(VL_FATURA_TOTAL_MOEDA,0)  AS VL_FATURA_TOTAL_MOEDA
                    ,DS_MOEDA_PRINCIPAL  = (SELECT TOP(1) CASE WHEN FATU.ID_CD_QUEM_PAGA IS NOT NULL THEN FAIT.DS_MOEDA_VENDA ELSE FAIT.DS_MOEDA_CUSTO END DS_MOEDA_PRINCIPAL FROM TB_FATURA_ITENS_FAIT FAIT WHERE FAIT.ID_CD_FATURA = FATU.ID_CD_FATURA)
                From 
                    TB_FATURA_FATU              FATU INNER JOIN
                    TB_BOOKING_BOOK             BOOK ON BOOK.ID_CD_BOOKING      = FATU.ID_CD_BOOKING        INNER JOIN
                    TB_FATURAS_TIPOS_FATP       FATP ON FATP.ID_CD_FATURA_TIPO  = FATU.ID_CD_FATURA_TIPO    INNER JOIN
                    TB_LOGIN_LOGI               LOGI ON LOGI.ID_CD_LOGIN        = FATU.ID_CD_LOGIN
                    OUTER APPLY (
                        Select top 1 
                            FL_TEM_FECHAMENTO = 'S'
                            ,FAPR2.ID_CD_FATURA_PRI 
                        From 
                            TB_FATURA_AUX_FPAX FPAX2 INNER JOIN 
                            TB_FATURA_PRINCIPAL_FAPR FAPR2 ON FAPR2.ID_CD_FATURA_PRI = FPAX2.ID_CD_FATURA_PRI  
                        Where 
                            FPAX2.ID_CD_FATURA = FATU.ID_CD_FATURA AND FAPR2.IN_STATUS = 1
                    ) AS TEM_FECHAMENTO

                    OUTER APPLY (    
                                    SELECT TOP 1 
                                        ID_CD_LANCAMENTO  
                                        ,FL_COMPENSADO = ( SELECT TOP 1 
                                                                'N'
                                                            FROM 
                                                                TB_FIN_LANCAMENTOS_PARC_FNLP FNLP2 
                                                            WHERE 
                                                                FNLP2.ID_CD_LANCAMENTO  = FNLP.ID_CD_LANCAMENTO
                                                            AND FNLP2.ID_CD_AUXILIAR    = FATU.ID_CD_FATURA
                                                            AND FNLP2.FL_AUXILIAR       = 'FAT'  
                                                            AND FNLP2.FL_SITUACAO       = 'A')
                                        ,DT_ULTIMO_PAGTO = ( SELECT TOP 1 
                                                                FNLP3.DT_COMPENSADO
                                                            FROM 
                                                                TB_FIN_LANCAMENTOS_PARC_FNLP FNLP3 
                                                            WHERE 
                                                                FNLP3.ID_CD_LANCAMENTO  = FNLP.ID_CD_LANCAMENTO
                                                            AND FNLP3.ID_CD_AUXILIAR    = FATU.ID_CD_FATURA
                                                            AND FNLP3.FL_AUXILIAR       = 'FAT'  
                                                            AND (FNLP3.FL_SITUACAO      = 'C' OR FNLP3.FL_SITUACAO      = 'G')
                                                            ORDER BY ID_CD_PARCELA DESC)
                                    FROM 
                                        TB_FIN_LANCAMENTOS_PARC_FNLP FNLP 
                                    WHERE 
                                        FNLP.ID_CD_AUXILIAR = FATU.ID_CD_FATURA
                                    AND FNLP.FL_AUXILIAR    = 'FAT'  
                                    AND FNLP.FL_CANCELADO   = 'N'
                                ) AS TEM_LANCAMENTO
                                    WHERE ID_CD_FATURA <> '' ) TB       
                                    WHERE ((ID_CD_FATURA_TIPO = '3' AND IN_STATUS = '1' ) AND (ID_CD_FATURA_PRI IS NULL AND ID_CD_LANCAMENTO IS NULL )) AND ID_CD_QUEM_RECEBE <> ''
                                    GROUP BY
                                    ID_CD_QUEM_RECEBE,ID_CD_PESSOA,DS_MOEDA_PRINCIPAL,NM_PAGREC,DS_MOEDA_PRINCIPAL)

SELECT Z.ID_CD_PESSOA as ID_AGENTE, 
   Z.NM_PESSOA AS AGENTE,     
   R.[TOTAL_APAGAR] AS A_PAGAR,
   R.[DS_MOEDA_PRINCIPAL] AS MOEDA,
   P.[TOTAL_ARECEBER] AS A_RECEBER,
   P.[DS_MOEDA_PRINCIPAL] AS MOEDA,

   ISNULL(P.[TOTAL_ARECEBER],0) - ISNULL(R.[TOTAL_APAGAR],0) AS [TOTAL_SALDO],
   MOEDA_SALDO =         (Case WHEN
                            P.[DS_MOEDA_PRINCIPAL] IS NULL 
                          THEN R.[DS_MOEDA_PRINCIPAL]
                          ELSE P.DS_MOEDA_PRINCIPAL
                          END)
FROM [TB_CRM_PESSOAS_CRMP] AS Z 
        LEFT OUTER JOIN [PAGA] AS P ON Z.ID_CD_PESSOA = P.ID_CD_QUEM_PAGA
        LEFT OUTER JOIN  [RECEBE] AS R ON Z.ID_CD_PESSOA = R.ID_CD_QUEM_RECEBE
WHERE ((P.ID_CD_QUEM_PAGA IS NOT NULL AND R.ID_CD_QUEM_RECEBE IS NULL) 
      OR (P.ID_CD_QUEM_PAGA IS NULL AND R.ID_CD_QUEM_RECEBE IS NOT NULL) 
      OR (P.ID_CD_QUEM_PAGA IS NOT NULL AND R.ID_CD_QUEM_RECEBE IS NOT NULL))

Browser other questions tagged sql database sql-server

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

by Ernesto Casanova • **1,344** points · Answer 1 · 2019-08-02T21:04:47+00:00

1

the logic of the invoice and the balance would put this way. When I want N rows grouped in the result (in a single Row and column).

, 'FATURA' = ISNULL(
                (SELECT STUFF((SELECT ', ' + F.[ID] 
                    FROM [dbo].[fatura_lines] F WITH (NOLOCK)
                    WHERE F.ID = CC.ID
                    FOR XML PATH('')), 1, 1,'') AS FATURA)
            , '')

I’m sorry Ernesto but I didn’t understand your reply. I apologize for the stupidity.

– Pietro Rey

2019/08/05 at 16:54
Hello alive, with Function STUFF you can consult n invoices and returns in a single line as in your example "15997, 16428", that is to say with several result lines you present in a single line, it is actually what you look for, with this approach you simplify your query. This example is only for one line, in this case "INVOICE".

– Ernesto Casanova

2019/08/05 at 21:44
Ahh yes, I understood, I had no knowledge of this STUFF as a Function. Thank you for the teaching.

– Pietro Rey

2019/08/06 at 16:48