How do I get as much and as little as possible of a consult?

Asked

Viewed 93 times

0

I have the following consultation:

select DATEDIFF("D",DtVencimento,DtBaixa) as atraso
  from Entrada 
 where Id='1' and DtEmissao >='01/01/2020' 
   and DtEmissao <='01/12/2020' 
   and ((VlBaixado is not null and VlBaixado != 0) 
   and DtBaixa is not null) 
   and DATEDIFF("D",DtVencimento,DtBaixa) < 0;

When it returns me more than one line I need to identify the highest and lowest value. For example, in the result below:

delay
-6
-10
-20

I need to identify the minor (-20) and the greatest (-6) value.

Can someone help me return these values in a query in SQL Server?

sql sql-server

  • You want the values to be returned in separate lines?

  • Not necessarily, I just wish I could get one with the maximum and minimum values. I’m not sure how to do.

  • If it can be on the same line then you can use the functions Min and Max

  • Thanks @Imex I already tried to use, but the way I do was not solving.

  • @Robsoncabral Be careful when reporting the date as literal without associating what the date format is. For example, "01/12/2020" is December 1 or January 12? SQL Server can interpret in different ways, depending on the session’s DATEFORMAT. Details in the article The dangers of automatic data type conversion -> https://portosql.wordpress.com/2018/10/25/os-perigos-da-conversao-implicita-1/

  • Opa @Josédiz I am aware of this, the original query will be with the date functions picking from today to 12 months ago. Thanks for the tip, it is very important to stay attend to this even if the result does not come out as it should.

Show 1 more comment

2 answers

2


You can use CTE:

WITH foo(atraso) AS 
(select DATEDIFF("D",DtVencimento,DtBaixa) as atraso 
 from Entrada 
 where Id='1'
   and DtEmissao >='01/01/2020'
   and DtEmissao <='01/12/2020' 
   and ((VlBaixado is not null and VlBaixado != 0) 
   and DtBaixa is not null) 
   and DATEDIFF("D",DtVencimento,DtBaixa) < 0)
   
SELECT min(atraso), max(atraso) FROM foo;

  • Thanks, it helped a lot, I didn’t know how to do it this way. Decided here for what I need.

2

SELECT max(atraso) Min,
       min(atraso) Max
from (
       select DATEDIFF("D",DtVencimento,DtBaixa) as atraso
         from Entrada 
        where Id='1' and DtEmissao >='01/01/2020' 
          and DtEmissao <='01/12/2020' 
          and ((VlBaixado is not null and VlBaixado != 0) 
          and DtBaixa is not null) 
          and DATEDIFF("D",DtVencimento,DtBaixa) < 0;
     ) Tab

  • Oops, I was doing it that way, only it wasn’t working because I wasn’t putting name in the from. : -) Thanks.

Browser other questions tagged sql sql-server

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