Date calculation

[#1]

Hi all

I have a transaction file with customer and date (with a date format).

I would like to build a new file where for each customer I calculate duration between 2 dates

For example

- The first 2,

- The first & the last,

- The 2 last

- or any 2 dates

Maybe you have good solution for my problem

Jean-Jacques

[johnw · Accepted solution]

If I understood the question:

LOAD
Customer
,min(Date,2)-min(Date) as "days between the first two dates"
,max(Date) -min(Date) as "days between the first date and last date"
,max(Date) -max(Date,2) as "days between the last two dates"
,some other expression for whatever two dates you wanted as "days between some other two dates"
RESIDENT [Customer and Date Table]
GROUP BY Customer
;

[#3]

Yes, you understood well.

Do I need to order the file before ?

JJ

[johnw]

It is not necessary to sort the file, if that's what you are asking. The min() and max() functions don't require the data to be in order.

[#5]

Tx john