Compare values with different conditionals in the same table

Asked

Viewed 945 times

0

When making a SELECT COUNT(*) in the view vwNotaFiscal I get the total of lines: 3498

SELECT COUNT(*) FROM dbo.vwNotaFiscal
WHERE 
tbEmbarques_emissao BETWEEN CONVERT(DATE,'01/05/2016',103) AND CONVERT(DATE,'31/05/2016',103)
AND (dataRecebimento is not null and recebimento_embarque is not null)

By selecting the tax bills that were delivered on time, I make the SELECT below and return me 2697

SELECT COUNT(*) FROM dbo.vwNotaFiscal
WHERE 
tbEmbarques_emissao BETWEEN CONVERT(DATE,'01/05/2016',103) AND CONVERT(DATE,'31/05/2016',103)
AND (dataRecebimento is not null and recebimento_embarque is not null) 
AND (datarecebimento <= dataprevista OR datarecebimento <= dataAgendamento)

That is, of the total 3498 I have 2697 within the time limit, thus the 801 remaining of that account would be the late ones. However, when making the query to verify this, the value is incorrect: 1293

SELECT COUNT(*) FROM dbo.vwNotaFiscal
WHERE 
tbEmbarques_emissao BETWEEN CONVERT(DATE,'01/05/2016',103) AND CONVERT(DATE,'31/05/2016',103)
AND (dataRecebimento is not null and recebimento_embarque is not null) 
AND (datarecebimento > dataprevista OR datarecebimento > dataAgendamento)

I am looking for a way to check these records and compare them to know what is returning to more in the latter SELECT. I found the controls INTERSECT and EXCEPT but they did not help me in the solution.

sql sql-server

  • Marcelo, from what I was able to extract from your conversation with @Ciganomorrisonmendez, only one of the dates should be used in the comparison, by the name of their fields, I can infer that the dataPrevista will always be completed and will be used if dataAgendamentois void, or by dataAgendamento takes precedence over the dataPrevista.

  • Exactly, @Tobymosque.

  • Curiosity, and if you did this: AND NOT (Date Receipt <= Date Expected or Date Received <= Date Gendering)

  • Nothing, @Cantoni... Adding the line, the count returns 103, the value is not that.

  • @Marcelodeandrade, without access to data is difficult to help in cases like this. But I suggest you do some checks, for example, the 801 records were not found via SQL, but doing (3498-2697=801). There may be records in your database that do not meet the SQL of the out-of-date you are trying to do. Maybe only the 103 found by my logic and the Gypsy meet. The rest must have some problem in the data that do not make them be returned. It’s time to scan some records and check what happens to the data. Export everything to Excel and filter there.

  • @Cantoni just what I’m trying to do now. I want a valid way to compare the values returned between these two queries.

Show 1 more comment

2 answers

2

(datarecebimento <= dataprevista OR datarecebimento <= dataAgendamento)

It is not the logical negation of

(datarecebimento > dataprevista OR datarecebimento > dataAgendamento)

By your logic of notes on time:

  • Date of Receipt must be prior to or equal to the Due Date; or
  • Receiving Date must be prior to or equal to the Scheduling Date.

The logic of supplementing this, then, must be:

  • Date of Receipt needs to be posterior or equal to the Expected Date; and
  • Date of Receipt needs to be posterior or equal to Scheduled Date.

This is De Morgan’s propositional logic. If the date of receipt is prior to one of the other two dates (scheduled or scheduled), the order arrived on time, and so does not fit the operator OR.

So the right sentence is:

SELECT COUNT(*) FROM dbo.vwNotaFiscal
WHERE 
tbEmbarques_emissao BETWEEN CONVERT(DATE,'01/05/2016',103) AND CONVERT(DATE,'31/05/2016',103)
AND (dataRecebimento is not null and recebimento_embarque is not null) 
AND (datarecebimento > dataprevista AND datarecebimento > dataAgendamento)

  • The problem that when using the operand AND, I state that the two dates of the record must obey this condition, correct? Since the records obey only one of these two dates as criterion each time. In the case of your SELECT I had a total of 103 records only.

  • Then you may have to increase the criteria. I don’t know how your bank is, but some more things will have to be checked.

  • This will be a problem, because there are no more criteria to be met.

  • Another alternative would be to materialize the result of each condition separately, but I’d need a Fiddle for that.

  • I’ll see if there’s a way to make it available

1

Marcelo, since dataAgendamento takes precedence over dataprevista and only one of the two dates should be used in the comparison, so use the ISNULL.

DECLARE @DataIni as DATE;
DECLARE @DataFin as DATE;

SET @DataIni = '2016-05-01';
SET @DataFin = '2016-05-31';

WITH CTE_Pedidos as (
    SELECT 
        notaFiscalId, 
        CAST(CASE 
            WHEN datarecebimento <= IsNull(dataAgendamento, dataprevista) THEN 1
            ELSE 0
        END AS BIT) IsNoPrazo
    FROM dbo.vwNotaFiscal
    WHERE 
        tbEmbarques_emissao BETWEEN @DataIni AND @DataFin AND 
        (dataRecebimento is not null and recebimento_embarque is not null) 
)

SELECT
    Total.Quantidade AS Total,
    NoPrazo.Quantidade AS NoPrazo,
    ForaPrazo.Quantidade AS ForaPrazo
FROM
    (SELECT COUNT(notaFiscalId) AS Quantidade FROM CTE_Pedidos) AS Total,
    (SELECT COUNT(notaFiscalId) AS Quantidade FROM CTE_Pedidos WHERE IsNoPrazo = 1) AS NoPrazo,
    (SELECT COUNT(notaFiscalId) AS Quantidade FROM CTE_Pedidos WHERE IsNoPrazo = 0) AS ForaPrazo,

Browser other questions tagged sql sql-server

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