0

I Have this query :

;with cte as (
Select tConc.Faturamento
     , tFat.TotalFatura
     , tCod.Sequencial
     , tCodDet.CdCodigoBarras
     , tCodDet.Linha
     , ValorRecebido
  From Sistema.Conciliacao_Faturamento_x_Arrecadacao as tConc
 Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
    On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
 Inner Join Arquivo.CodigoBarras as tCod
    On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
 Inner Join Arquivo.Faturamento as tFat
    On tConc.Faturamento = tFat.CdFaturamento
 Where Faturamento In(Select Faturamento
                        From Sistema.Conciliacao_Faturamento_x_Arrecadacao
                       Group By Faturamento
                      Having Count(CodigoBarras) > 01))

And the result is this one:

| Faturamento | TotalFatura | Sequencial | CdCodigoBarras | Linha | ValorRecebido |
|-------------|-------------|------------|----------------|-------|---------------|
| 2458874     | 320,90      | 5783       | 141            | 18393 | 320,90        |
| 2458874     | 320,90      | 5783       | 141            | 18394 | 320,90        |
| 3283899     | 840,50      | 5783       | 141            | 27140 | 840,50        |
| 3283899     | 840,50      | 4397       | 142            | 4010  | 840,50        |
| 3901364     | 89,44       | 21442      | 139            | 23595 | 89,44         |

Long story short, if you notice, there are some equal "faturamento".

What I need is a distinct of:

1) If Faturamento is the same, we need the biggest sequencial. Ok with this, I can order by 3.

2) If faturamento and sequencial is the same, I need the biggest cdCodigoBarras. Ok, again, I can do it with Order by 3,4.

3) If everything is the same, but valorRecebidois the same, I need to get the biggest Linha. but if ValorRecebido is different ( and faturamento, sequencia, cdcodigobarras is the same ), I need the biggest ValorRecebido.

Is this something that devs should do in the source code? I really can't think how I can do it with a Query.

I've spend some time tryint to do something with count, but even thinking I'm in the right way, I can past this when vlaorRecebidois the same, get biggest Linha, or if it's different, the biggest vlaorRecebido.

  • Create an SQL Fiddle with a test database, then we can improve your query. – Gerard H. Pille May 18 '18 at 12:22
  • I do not think this will help. I'm using a CTE, so, we need only one table with the results that I posted there ( the table ). The CTE is ok, and the query is OK. I just need a logic to extract the result as needed. – Racer SQL May 18 '18 at 12:48
  • Suit yourself, as they say. – Gerard H. Pille May 18 '18 at 13:29
  • Clearly the query is not OK or you wouldn’t be asking the question. Creating a fiddle allows someone to help. – Nick.McDermaid May 18 '18 at 21:19
2

I think you should be able to get what you want with ROW_NUMBER(). Please see the below example.

;with cte as (
Select tConc.Faturamento
     , tFat.TotalFatura
     , tCod.Sequencial
     , tCodDet.CdCodigoBarras
     , tCodDet.Linha
     , ValorRecebido
     , RN = ROW_NUMBER() OVER (PARTITION BY  tConc.Faturamento, tCod.Sequencial, tCodDet.CdCodigoBarras, ValorRecebido
                                ORDER BY tCodDet.Linha DESC, tCodDet.CdCodigoBarras DESC, tCod.Sequencial DESC)
  From Sistema.Conciliacao_Faturamento_x_Arrecadacao as tConc
 Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
    On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
 Inner Join Arquivo.CodigoBarras as tCod
    On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
 Inner Join Arquivo.Faturamento as tFat
    On tConc.Faturamento = tFat.CdFaturamento
 Where Faturamento In(Select Faturamento
                        From Sistema.Conciliacao_Faturamento_x_Arrecadacao
                       Group By Faturamento
                      Having Count(CodigoBarras) > 01))
)
SELECT * FROM cte WHERE RN = 1

You may want to change or play with the PARTITION BY section as well as the ORDER BY to get what you want. Basically, the PARTITION BY is what you want to be the business keys. And I wasn't sure if it was just the Faturamento or Faturamento + Sequencial + ... or not. Order by is if (given the same partition), what order should they appear in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.