finding time difference with minimum function

[jayati_shrivast]

Hi All,

I have 2 date fields as DATE 1  AND DATE2 in the format 'DD-MM-YYYY hh:mm:ss'. i need to find date difference between these 2 fields as      " min(Date1 ) - (DATE2)" and store this date diff as a separate field in table layout

i  should get data as follows  : I ONLY need to find the difference between first occurence of DATE1 with the corresponding DATE2

DATE1                                   DATE 2                                              DATEDIFF

17-10-2018 12:15:00        17-10-2018  12:17:00                00:02:00

17-10-2018 12:19:00         17-10-2018 12:21:00

18-10-2018 13:20:00         18-10-2018  13:25:00                00:05:00

18-10-2018 13:27:00          18-10-2018  13:31:00

..... AND SO ON...

PLS HELP ME OUT......

Thanks

[rangam_s]

date(date#(DATE1,'DD-MM-YYYY hh:mm:ss')-date#(DATE2,'DD-MM-YYYY  hh:mm:ss'),'hh:mm:ss')

Choose Duration in number formatting.

[OmarBenSalem]

Try sthing as follow:

Let's assume this is ur main table:

:

Table:

Load Date1,Date2, other fields ...;

Create a new date field based on Date1

Table:

Load Date1,Date2, other fields, Date(Date1) as MyDate ...;

Create a new aggregated table (group by MyDate):

load minDate2-minDate1 as Difference, MyDate

load min(Date1) as MinDate1, min(Date2) as minDate2, MyDate resident Table group by MyDate;

[rangam_s]

date(min(date#(DATE1,'DD-MM-YYYY hh:mm:ss'))-min(date#(DATE2,'DD-MM-YYYY  hh:mm:ss')),'hh:mm:ss')

Choose Duration in number formatting.