Column outside the SELECT WHERE condition

Asked

Viewed 516 times

2

I need to make a query that returns a column with the amount of orders that the customer made within the period past in the WHERE and put together another column with the total orders that the customer has made so far. Sort of like this:

[CLIENTE][COMPRAS NO PERÍODO][COMPRAS NO TOTAL]
 Marcos           5                 28
 Flávio           7                 35
 Roberta          9                 32

How would I do that?

Thank you!

EDIT

Here’s the SQL I’m using for the tests. What I really want is the [Total Title] field with the same idea I mentioned above: (Ah, detail: I can’t change the structure of the comic. It’s already deployed)

SELECT CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem 'OP'
        ,NotasFiscais.DataEmissao 'Data Emissão'
        ,NotasFiscais.NumNota 'Nota'
        ,OrcHdr.NomeCliente 'Cliente'
        ,NotasFiscais.ValorTotalNota 'Valor NF'
        ,'PercComissao' = CASE WHEN Natureza LIKE 'C' AND OrdensProducao.Cancelado = 0 
                          THEN SUM(Valor/SPreco) END
        ,'Titulo Período' = COUNT(FIN_Titulos.IdTipoDocumento)
        ,'Titulo Total' = ???

FROM OrcHdr 
INNER JOIN OrdensProducao ON OrcHdr.NumOrdem = OrdensProducao.NumOrdem
INNER JOIN CustosVendaItemPed ON OrdensProducao.NumOrdem = CustosVendaItemPed.NumPedido
INNER JOIN ItemNota ON OrdensProducao.NumOrdem = ItemNota.NumOrdem 
INNER JOIN NotasFiscais ON ItemNota.ObjID_Nota = NotasFiscais.ObjID
INNER JOIN FIN_Titulos ON NotasFiscais.ObjID = FIN_Titulos.ObjIDDoctoOrigem

WHERE CustosVendaItemPed.Descricao NOT LIKE 'CIN%' 
AND CustosVendaItemPed.Descricao NOT LIKE 'Centro Integrado%' 
AND CustosVendaItemPed.Descricao NOT LIKE 'não pagar%' 
AND NotasFiscais.Situacao LIKE 'N' 
AND ItemNota.Fatura LIKE 'F' 
AND ItemNota.Devolucao NOT LIKE 'D' 
AND NotasFiscais.NaturezaOperacao NOT LIKE 'reme%' 
AND NotasFiscais.NaturezaOperacao NOT LIKE 'doa%' 
AND FIN_Titulos.DebCred LIKE 'C' 
AND FIN_Titulos.Situacao <> 3 
AND DataVencimento >= '2012-11-01 00:00:00.000' 
AND DataVencimento <= '2012-11-30 00:00:00.000')

GROUP BY 
        CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem
        ,NotasFiscais.DataEmissao
        ,NotasFiscais.NumNota
        ,OrcHdr.NomeCliente
        ,NotasFiscais.ValorTotalNota
        ,CustosVendaItemPed.Natureza
        ,OrdensProducao.Cancelado
        ,DataVencimento

Order By 
        CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem
sql sql-server select

  • It depends on your modeling, present your pro staff structure and the code you’ve achieved so far. But a path would be sub-query’s...

  • You can show the bank modeling. Where the FKS are.

  • Wouldn’t you decide to put an underspend on your select? That’s kind of how I proposed.

3 answers

4


Another alternative would be to use CROSS APPLY or OUTER APPLY, both perform a function or query about the result of a select, for example:

Consider the tables

Tabela Cliente
| Id | Nome  |
|  1 | Pedro |
|  2 | João  |

Tabela Compra
| IdCliente | Produto |
|     1     | Arroz   |
|     1     | Feijão  |

To know how many products each customer bought, you could do so:

SELECT
    cli.Id,
    cli.Nome,
    prd.Qtde
FROM
    Clientes cli -- select principal
    OUTER APPLY (SELECT COUNT(*) Qtde 
                 FROM Compra cmp 
                 WHERE (cmp.IdCliente = cli.Id)) prd -- select secundário

The result would be:

| Id | Nome  | Qtde |
|  1 | Pedro |   2  |
|  2 | João  |   0  |

The CROSS APPLY and the OUTER APPLY are similar to the INNER JOIN and to the LEFT JOIN, respectively, as the CROSS causes only the common records among the select main and the select are displayed, different from the OUTER, which returns all records from select and more the records of the select secondary found.

For this example above, you could use the CROSS or the OUTER, because there will always be a record on select secondary school.

Notes: the tables of the examples were simplified to facilitate understanding and the "nomenclatures", select main and secondary only served to link the example with the text.

If you want to know more about the CROSS and the JOIN, you can take a look at that link.

  • and with respect to performance? I know that my example is nothing recommended, but it is functional. usually in these types of cases I prefer to denormalize the bank to win in consultation performance.

  • I did not get to make performance comparisons, but you could perform a test with both approaches and compare, I found it interesting to post this other option because I’ve had a similar case and the OUTER APPLY was the solution

  • I’ll try to find an example in my system to see the performance.

  • 1

    It worked perfectly and the return was faster compared to the use of Subconsulta. Thank you!

2

The solution to your case is to use a subconsulta, which is as follows:.

,'Titulo Total' = (SELECT COUNT(*) 
                   FROM FIN_Titulos TitulosTotal 
                   WHERE NotasFiscais.ObjID = TitulosTotal.ObjIDDoctoOrigem)

  • I’m trying to do something like this. Let’s see...

  • 1

    from what I could understand from your consultation, it would be something similar ,'Titulo Total' = (select count(*) from OrdensProducao OrdensProducao2 where OrcHdr.NumOrdem = OrdensProducao2.NumOrdem)

  • Great! Helped me a lot Pablo, thanks! I had to make some modifications, but the idea is to use Subconsulta yes.

2

I made my SELECT like this:

SELECT CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem 'OP'
        ,NotasFiscais.DataEmissao 'Data Emissão'
        ,NotasFiscais.NumNota 'Nota'
        ,OrcHdr.NomeCliente 'Cliente'
        ,NotasFiscais.ValorTotalNota 'Valor NF'
        ,'PercComissao' = CASE WHEN Natureza LIKE 'C' AND OrdensProducao.Cancelado = 0 
                          THEN SUM(Valor/SPreco) END
        ,'Titulo Período' = COUNT(FIN_Titulos.IdTipoDocumento)
        ,'Titulo Total' = (SELECT COUNT(*) 
                           FROM FIN_Titulos TitulosTotal 
                           WHERE NotasFiscais.ObjID = TitulosTotal.ObjIDDoctoOrigem)

Thank you!

Browser other questions tagged sql sql-server select

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