date

[AjithramT]

I have three dates... now I needs to find the datedifference between the min date and middle date

then  consider the max date(third date) as a min date and find the diff between this as a min date and till today..

how to achieve this through script?

thanks,

[sunny_talwar]

Would you be able to share a few rows of sample data and the output you expect to see from the sample shared?

[AjithramT]

Event_Type, Event_Date, Hospital,   Department

C, 2/28/2019, HOSPITAL A, OR

C, 1/20/2019, HOSPITAL A, OR

C, 2/15/2019, HOSPITAL A, OR

I need a o/p like,

Event_Type    Hospital       Department    fromdate     todate            datediff

       C                     hos A                OR                  1/20/2019     2/15/2019      diff value

       C                     hos A                OR                   2/15/2019     2/28/2019

       C                     hos A                OR                  2/28/2019      today()

thanks,

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    Event_Type, Event_Date, Hospital, Department
    C, 2/28/2019, HOSPITAL A, OR
    C, 1/20/2019, HOSPITAL A, OR
    C, 2/15/2019, HOSPITAL A, OR
];

FinalTable:
LOAD Event_Type,
	 Hospital,
	 Department,
	 Date(If(Event_Type = Previous(Event_Type) and Hospital = Previous(Hospital) and Department = Previous(Department), Previous(Event_Date), Today())) as todate,
	 Event_Date as fromdate,
	 If(Event_Type = Previous(Event_Type) and Hospital = Previous(Hospital) and Department = Previous(Department), Previous(Event_Date), Today()) - Event_Date as datediff
Resident Table
Order By Event_Type, Hospital,  Department, Event_Date desc;

DROP Table Table;