4 Replies Latest reply: Nov 24, 2009 11:12 PM by Jean-Jacques Jesua

Date calculation

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

• Date calculation

If I understood the question:

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
;

• Date calculation

Yes, you understood well.

Do I need to order the file before ?

JJ

• Date calculation

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.