I need to find the penultimate date between variables

[Ribeiro]

I need to find a data 6/15/2020 in the Load no script below.

See that you can find a last date. I need the penultimate.

Let v_dataI = '01/04/2020';
Let v_dataF = '15/07/2020';

Tmp:
LOAD * INLINE [
Data, ID, Store, Tipo
15/01/2020, 1, 21, VND
15/02/2020, 2, 23, VND
15/03/2020, 3, 20, VND
15/05/2020, 4, 21, VND
15/06/2020, 4, 21, VND
15/07/2020, 4, 21, VND
15/08/2020, 4, 21, VND
];

LEFT JOIN(Tmp)
LOAD
ID,


max(Date(Floor(Data)),'DD/MM/YYYY') AS Data_Tmp1


Resident Tmp where Tipo = 'VND' and Data>= '$(v_dataI)' and Data <='$(v_dataF)'

GROUP BY ID;

[fosuzuki · Accepted solution]

Hello,

try this:

(max(Date(Floor(Data)),'DD/MM/YYYY')-2) AS Data_Tmp1