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

Question

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

Asked 4 years ago

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?

You want the values to be returned in separate lines?

– imex

2020/12/08 at 13:18
Not necessarily, I just wish I could get one with the maximum and minimum values. I’m not sure how to do.

– Robson Cabral

2020/12/08 at 13:24
If it can be on the same line then you can use the functions Min and Max

– imex

2020/12/08 at 13:27
Thanks @Imex I already tried to use, but the way I do was not solving.

– Robson Cabral

2020/12/08 at 13:56
@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/

– José Diz

2020/12/08 at 14:56
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.

– Robson Cabral

2020/12/08 at 15:47

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.

– Robson Cabral

2020/12/08 at 13:53

Browser other questions tagged sql sql-server

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

by Rodrigo Zem • **895** points · Answer 1 · 2020-12-08T13:47:59+00:00

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