Maximum SQL date

Asked

Viewed 2,740 times

1

I have the following table T_PGTOS_ADESAO:

Adesão  | Tipo Adesão | Data Vencimento Boleto | Data Pgto Boleto 
12247   | Cartão      |    20-03-2018          | 03-03-2018
38497   | Boleto      |    15-05-2018          | 10-05-2018
48289   | Cartão      |    20-05-2018          | 02-01-1900 
89497   | Saque Compl.|    23-04-2018          | 02-01-1900
90849   | Boleto      |    15-05-2018          | 07-05-2018

I would like to create a column with the maximum date of Data Pgto Boleto (which in the example given is the date 10-05-2018) for all lines, thus: 

Adesão  | Tipo Adesão | Data Vencimento Boleto |Data Pgto Boleto| DT_UTLIMO_PGTO
12247   | Cartão      |    20-03-2018          | 03-03-2018     | 10-05-2018
38497   | Boleto      |    15-05-2018          | 10-05-2018     | 10-05-2018
48289   | Cartão      |    20-05-2018          | 02-01-1900     | 10-05-2018
89497   | Saque Compl.|    23-04-2018          | 02-01-1900     | 10-05-2018
90849   | Boleto      |    15-05-2018          | 07-05-2018     | 10-05-2018

but when I consult 

SELECT 
T_PGTOS_ADESAO.*,
MAX(Data Pgto Boleto) as DT_UTLIMO_PGTO
from T_PGTOS_ADESAO
GROUP BY 
Adesão
,[Tipo Adesão]
,[Data Vencimento Boleto]
,[Data Pgto Boleto]

it returns the value of Due Date Billet for each row.

could you help me, please?

sql

1 answer

1


Aggregating functions as MAX() are applied in the context of GROUP BY, and as you are grouping across all fields, MAX() will return the highest limit date of each record.

Experiment with a subselect:

SELECT 
  T_PGTOS_ADESAO.*,
  (select MAX(Data Pgto Boleto) from T_PGTOS_ADESAO) as DT_UTLIMO_PGTO
from T_PGTOS_ADESAO;

Thus, MAX() is applied in the implicit group context of all table records.

  • Thank you so much! I’ll try tomorrow and let you know if it worked =)

Browser other questions tagged sql

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