this question exact duplicate of:
seeking optimized code sql server 2008 (because lag , lead functions not available in sql server 2008) only. following requirement.(last record end date can null or not null)
business rule is:
enddate should start date next rule otherwise rule should not consider continuous , treated other rule series, shown in below example there 3 series because fourth record , 7 record rule has braked.
ruleid name startdate enddate ----------------------------------------------------------- 2 tp1 1/1/2015 00.00.00 1/31/2015 00.00.00 2 tp1 1/31/2015 00.00.00 2/28/2015 00.00.00 2 tp1 2/28/2015 00.00.00 3/15/2015 00.00.00 2 tp1 3/18/2015 00.00.00 11/28/2015 00.00.00 2 tp1 11/28/2015 00.00.00 4/30/2016 00.00.00 2 tp1 4/30/2016 00.00.00 10/5/2016 00.00.00 2 tp1 10/25/2016 00.00.00 11/15/2016 00.00.00 2 tp1 11/15/2016 00.00.00 null result should following
2 tp1 1/1/2015 00.00.00 3/15/2015 00.00.00 2 tp1 3/18/2015 00.00.00 10/5/2016 00.00.00 2 tp1 10/25/2016 00.00.00 null
you can row_number() partition by , self join rn = rn + 1 , datediff based on can group results.
try this
declare @rules table(ruleid int,name char(3), startdate date, enddate date) insert @rules values (3 ,'tp3', '3/18/2015', '11/28/2015'), (3 ,'tp3', '11/28/2015', '4/30/2016'), (3 ,'tp3', '4/30/2016', '10/5/2016'), (3 ,'tp3', '10/25/2016', '11/15/2016'), (3 ,'tp3', '11/15/2016', null) ;with cte ( select row_number() over(partition ruleid order startdate) rn,* @rules ), cte2 ( select isnull(datediff(day,c1.enddate,c2.startdate),0) diff,c1.* cte c1 left join cte c2 on c1.rn + 1 = c2.rn , c1.ruleid = c2.ruleid ) select ruleid,name,min(startdate) startdate,nullif(max(case when enddate null '2999/12/31' else enddate end),'2999/12/31') enddate ( select c2.diff,c1.ruleid,c1.name,c1.startdate,c1.enddate cte2 c1 cross apply (select isnull(sum(diff),0) diff cte2 c2 c1.startdate > c2.startdate , c1.ruleid = c2.ruleid) c2 ) c group ruleid,name,diff order ruleid,diff 