I did this exercise a few years ago. There were many, many combinations that caused problems, plus we had cases where the html markups were quoted in the text...

With the sample data thing, with this one it's incredibly...

I think this is right.  Naturally you'll need to confirm for yourself, as I don't have any additional test data.

IF OBJECT_ID('tempdb.dbo.#data') IS NOT NULL
  ...

Could do this with recursion, from right to left, but for now I'll just do a simple cursor loop.  Hopefully that will perform well enough for what you need. ...

Hi Scott,

Thanks for the quick reply - I'm just finishing for the day and I'll pick this up in the morning, at first glance it looks promising.

Paul

I think this should do it

;

WITH Category (CategoryID,PersonID,StartDate,EndDate)

AS

(

SELECT ROW_NUMBER () OVER (PARTITION BY PersonID ORDER BY StartDate),PersonID,COALESCE(StartDate,'Jan 1, 1900'),COALESCE(EndDate, 'Jan 1, 2100')

FROM ActiveCategory

),

Cat2 (PersonID,StartDate,EndDate)

AS

(SELECT COALESCE(c1.PersonID,c2.PersonID), DATEADD(day,1,COALESCE(c1.EndDate,'Dec 31, 1899')),DATEADD(day,-1,COALESCE(c2.StartDate,'Jan 2,...

Hello Nevyn,

I've worked through your solution now and it works in every situation but one (it's far better than what I came up with, I didn't think of using a...

Thanks for that mate, I'm just working through your solution but you are correct about the sample data being incorrect. I've updated the original post to reflect this.

I'm 60% through...