Show only positive delay days in SQL query?

Question

Show only positive delay days in SQL query?

Asked 9 years, 5 months ago

Viewed 773 times

0

I am making a query where I intend to calculate the amount of days of delay in a salary, I would like to show only if there are days of delay, at the moment I am showing days remaining for the salary, which should come 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

What is being shown and what is the desired behavior?

– Leonel Sanches da Silva

2015/04/20 at 22:05
Add in my question the result of the fields I need, when the date is negative is because it has not yet reached the due date, after that becomes positive. want shows only days after due date

– Harry

2015/04/20 at 22:37
Won if the due date is less than today’s date (or a date base date type last date low cnab file of the bank) , the subtraction of the two dates gives the days in arrears , without taking into account holidays or weekend , functions and examples to rodo on the web believe ...

– Motta

2015/04/21 at 01:17
@Motta, you don’t understand my question!

– Harry

2015/04/22 at 13:55
@itasouza, I should not have understood even , sorry , anyway seems to have been cured, []s

– Motta

2015/04/22 at 17:54

1 answer

Browser other questions tagged sql-server query

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

by Leonel Sanches da Silva • **88,623** points · Answer 1 · 2015-04-21T19:53:33+00:00

Seems pretty simple 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