==============
declare @tbl table
(
category int,
startdate datetime,
enddate datetime
)
insert into @tbl
select 10, '1/1/2010 17:49:59', '10/30/2010 17:49:49' union all
select 10, '5/3/2010 17:49:59', '10/30/2010 17:49:59' union all
select 11, '1/1/2010 17:49:59', '10/30/2011 17:49:59' union all
select 11, '5/3/2010 17:49:59', '10/30/2010 17:49:59'
; with cte as
(
select category, dte = startdate, f = 'S'
from @tbl
union
select category, dte = enddate, f = 'E'
from @tbl
),
cte2 as
(
select *, row_no = row_number() over (partition by category order by dte)
from cte
)
select category = c1.category,
startdate = case when c1.f = 'E'
then dateadd(S, 1, c1.dte)
else c1.dte
end,
enddate = case when c2.f = 'S'
then dateadd(s, -1, c2.dte)
else c2.dte
end
from cte2 c1
inner join cte2 c2 on c1.category = c2.category
and c1.row_no = c2.row_no - 1
