So I have a list of ActivityDates where I need to calculate the numbers of days worked. The core issue is that some of the dates overlap which can lead to days getting counted twice. In the SQL world, it's called a "Packing Intevals" problem, and it's obnoxious to solve. Below is my sample script.
LOAD DISTINCT %tractor,
LOAD %tractor ,
Date(floor(TimeStamp(TimeStamp#(distinct_lgh_enddate,'M/D/YYYY h:mm')))) AS distinct_lgh_enddate,
Date(floor(TimeStamp(TimeStamp#(distinct_lgh_startdate,'M/D/YYYY h:mm')))) AS distinct_lgh_startdate
At this point, I've deduped the date ranges, but I don't know how to solve what I believe is the next step which would be to join the date ranges, i.e all the %tractor='111' rows disappear and are replaced with one row [111, '6/7/2017', '6/10/2017']