Cheers Eugene, yes I'd figured that one out at least, but that was precisely the problem; a simple grouping won't work because the months have to be contiguous, otherwise you...

Hi Eugene

For item 1, there are records after February, so there needs to be an end month. For item 2 there are no more records after May so may is...

Hi Eugene

For item 1, there are records after February, so there needs to be an end month. For item 2 there are no more records after May so may is...

Hi Eugene, to answer your question when records are in chronological order, if there are no more recent records with the same ID then that defines the current state of...

Brilliant. I don't understand it yet, but still brilliant. Many thanks for your help!

Philippe

Apologies for that, I've edited the post with what the correct results should be.

The start month is the first month that an item as a particular state (values for count...

Blinding! well done guys and many thanks

It's

ORDER BY [Date]) - ROW_NUMBER()

that I'd failed to see before as my dummy grouping

Thanks again

Philippe

Hi Dev

The criteria for a cluster is that the records are identical (time field excepted) and contiguous when ordered by time. Records that are identical but not part of the...