Min and Max Dates

[melissapluke]

I have a table loaded like this:

Load
Item,
Start Date,
End Date,
Rate
From Table;

For one single item it looks like this:

I want to keep only the min and max start/end dates IF THE DATES ARE SEQUENTIAL. 

I want the results to look like this:

1/1/2015	6/30/2015	$0.20
7/1/2015	9/30/2015	$0.21
10/1/2015	3/31/2016	$0.20
4/1/2016	6/30/2016	$0.21
7/1/2016	12/31/2016	$0.20
1/1/2017	3/31/2017	$0.24

However, if I try something like this:

Load
Item,
Min(Start Date) as Start Date,
Max(End Date) as End Date,
Rate
From Table
Group by Item, Rate;

I get the min and max of ALL the dates, not only the dates that are in sequential order. I do not want the Max End Date of a line to overlap with the next min date.

Any ideas on what to try?

[StarinieriG]

Hi,

try to create a new field like this:

RowNo () AS NR

then order by that and create a new field, so you could use it in group by:

If(rate=Previous(rate),Peek('NR'),NR)

[sunny_talwar · Accepted solution]

Here is one way you can do this

Table:
LOAD * INLINE [
    Start Date, End Date, Rate
    01/01/2015, 03/31/2015, 0.20
    04/01/2015, 06/30/2015, 0.20
    07/01/2015, 09/30/2015, 0.21
    10/01/2015, 12/31/2015, 0.20
    01/01/2016, 03/31/2016, 0.20
    04/01/2016, 06/30/2016, 0.21
    07/01/2016, 09/30/2016, 0.20
    10/01/2016, 12/31/2016, 0.20
    01/01/2017, 03/31/2017, 0.24
];

FinalTable:
LOAD *,
	 If(Rate = Previous(Rate), Peek('Start Date New'), [Start Date]) as [Start Date New]
Resident Table
Order By [Start Date];

Right Join (FinalTable)
LOAD [Start Date New],
	 Rate,
	 Max([End Date]) as [End Date]
Resident FinalTable
Group By [Start Date New], Rate;

DROP Table Table;
DROP Field [Start Date];
RENAME Field [Start Date New] to [Start Date];

[melissapluke]

Thanks, Sunny. That will work!