Final: Load *, If(ID <> Previous(ID), Date(TOday()),If(ID = Previous(ID) and Previous(LocFlagNum)=1, Date(Peek(Date1)-1), if(ID=Previous(ID) and LocFlagNum = 0, Peek(Date1), Date))) as Date1; Load *, if(IsNum(Loc), 1,0) as LocFlagNum, ID&'|'&Loc as Key Resident Res order by ID, Rec desc, Loc;
ValidDate: Load Key, Date(Min(Date)) as ValidFromDate, Date(Max(Date1)) as ValidToDate Resident Final where LocFlagNum = 0 Group by Key;
DROP Table Res;
Highlighted record will update based on Today Date.
Thanks & Regards, Mayil Vahanan R Please close the thread by marking correct answer & give likes if you like the post.