Show only positive delay days in SQL query?

Question

Show only positive delay days in SQL query?

Navigation

#1 by (2 votes)

0

I am making an appointment where I want to calculate the number of days of delay in a due date, I would like to show only if there are days of delay, at the moment I am showing days that are not due for expiration, which should have been zero.

Ex:
Data de vencimento
2015-04-20 00:00:00.000
2015-05-20 00:00:00.000
2015-05-20 00:00:00.000
2015-06-15 00:00:00.000

Resultado para a data de hoje:
0
-30
-30
-56

select a.* 
, b.tipododocumento
, b.contacontabil
, d.nome 
, b.descricao
, a.formapagamentoA
, a.formapagamentoB
, a.data_inc
, a.data_alt
, a.letranota
, a.numeroparcela
,case a.statusregistro 
  when 'A' then 
    DATEDIFF (DAY, a.datavencimento,GETDATE() ) 
  when 'L' then
    0
  end AS diasatraso
 
from 
tb_recebimento a  
left outer join  tb_plano_contas b  on  a.controleplano = b.controleplano                     
left outer join   tb_cliente_fornecedor d   on  a.controlecliente = d.controlecliente         
Full Outer Join   tb_c_vendas c   on  c.controle = a.controlevenda                     
where a.statusregistro = 'A' 
and b.tipododocumento = 'D' 
and a.controleempresa = '1' 
order by a.datavencimento

sql-server query

asked by anonymous 20.04.2015 / 21:15

1 answer

How to get date and time in different columns through a select Sql server? Explode in Jquery

score 2 · Accepted Answer

It seems simple enough to solve:

select * from (
    select a.* 
    , b.tipododocumento
    , b.contacontabil
    , d.nome 
    , b.descricao
    , a.formapagamentoA
    , a.formapagamentoB
    , a.data_inc
    , a.data_alt
    , a.letranota
    , a.numeroparcela
    ,case a.statusregistro 
      when 'A' then 
        DATEDIFF (DAY, a.datavencimento,GETDATE() ) 
      when 'L' then
        0
      end AS diasatraso

    from 
    tb_recebimento a  
    left outer join  tb_plano_contas b  on  a.controleplano = b.controleplano                     
    left outer join   tb_cliente_fornecedor d   on  a.controlecliente = d.controlecliente         
    Full Outer Join   tb_c_vendas c   on  c.controle = a.controlevenda                     
    where a.statusregistro = 'A' 
    and b.tipododocumento = 'D' 
    and a.controleempresa = '1'  
) tabela where tabela.diasatraso >= 0
order by tabela.datavencimento