I have a dataset with rows with IDs, start dates, end dates, and interval lengths. I want to find the sum of all the intervals for each ID, excluding duplicate intervals. There can be 2 rows with the same start and end dates so they can't be counted twice when summing (but I also cannot remove the duplicates). For example (the sum column is what I need to calculate):
ID Start End Length Sum
1 1/1 1/5 5 10
1 1/1 1/5 5 10
1 1/10 1/15 5 10
1 1/10 1/15 5 10
2 2/1 2/10 10 15
2 2/3 2/9 7 15
2 2/20 2/25 5 15
2 2/20 2/25 5 15
The sum for ID 1 is 10 because it's only counting each date interval once.
The sum for ID 2 is 15 because even though there are 3 unique intervals, the one from 2/3 to 2/9 falls inside 2/1 to 2/10 so it shouldn't be counted.
I would like to do this in ETL but if it's possible to do in Analyzer I would go that route.