Compare values with different conditionals in the same table

Question

Compare values with different conditionals in the same table

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

When I make a SELECT COUNT(*) in 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)

When I select the invoices 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)

In other words, of the total 3498 I have 2697 on time, so the remaining%% of this account would be overdue. But when you query to verify this, the value is incorrect: 801

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'm looking for a way to check these records and compare them to know what's coming back on the last 1293 . I found the commands SELECT and INTERSECT but they did not help me in the solution.

sql sql-server

asked by anonymous 04.07.2016 / 20:20

2 answers

Make a function that generates the XML String and not the XML itself PHP [closed] Service done in Delphi "kill yourself" or give Stop

score 2 · Answer 1

(datarecebimento <= dataprevista OR datarecebimento <= dataAgendamento)

It is not the logical negation of

(datarecebimento > dataprevista OR datarecebimento > dataAgendamento)

For your timely notes logic:

Receipt date must be earlier than or equal to the Scheduled Date; or
Receipt date must be earlier than or equal to the Scheduling Date.

The logic of this addition then must be:

Receipt date must be later or equal to the Predicted Date; and
Receipt date must be later or equal to the Scheduling Date.

This is De Morgan's propositional logic . If the receiving date is earlier than one of the other two dates (scheduled or scheduled), the order has arrived on time, so the OR operator does not fit.

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)

score 1 · Answer 2

Marcelo, since dataAgendamento takes precedence over dataprevista and only one of the two dates should be used in the comparison, so use 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,