Odtnhj

Using SQL Server 2014

Example Table:

| ID | Date_Time | ID_Plan |
| 1 | 2018-12-19 21:21:00.000 | A1 |
| 2 | 2018-12-19 21:22:00.000 | A1 |
| 3 | 2018-12-19 21:23:00.000 | NULL |
| 4 | 2018-12-19 21:24:00.000 | NULL |
| 5 | 2018-12-19 21:25:00.000 | A2 |
| 6 | 2018-12-19 21:26:00.000 | A2 |
| 7 | 2018-12-19 21:27:00.000 | A2 |
| 8 | 2018-12-19 21:28:00.000 | A3 |
| 9 | 2018-12-19 21:29:00.000 | A3 |
| 10 | 2018-12-19 21:30:00.000 | NULL |
| 11 | 2018-12-19 21:31:00.000 | NULL |
| 12 | 2018-12-19 21:32:00.000 | NULL |
| 13 | 2018-12-19 21:33:00.000 | A4 |
| 14 | 2018-12-19 21:34:00.000 | A4 |
| 15 | 2018-12-19 21:35:00.000 | A4 |
| 16 | 2018-12-20 21:36:00.000 | NULL |

IF OBJECT_ID('tempdb..#tb_CountFinal') IS NOT NULL
DROP TABLE #tb_CountFinal

CREATE TABLE #tb_CountFinal
(
 [ID] [int] IDENTITY(1,1) NOT NULL,
 Date_Time DATETIME,
 ID_Plan NVARCHAR(100)
)


INSERT INTO #tb_CountFinal
SELECT '12/19/2018 21:21','A1'union all
SELECT '12/19/2018 21:22','A1'union all
SELECT '12/19/2018 21:23',NULL union all
SELECT '12/19/2018 21:24',NULL union all
SELECT '12/19/2018 21:25','A2'union all
SELECT '12/19/2018 21:26','A2'union all
SELECT '12/19/2018 21:27','A2'union all
SELECT '12/19/2018 21:28','A3'union all
SELECT '12/19/2018 21:29','A3'union all
SELECT '12/19/2018 21:30',NULL union all
SELECT '12/19/2018 21:31',NULL union all
SELECT '12/19/2018 21:32',NULL union all
SELECT '12/19/2018 21:33','A4'union all
SELECT '12/19/2018 21:34','A4'union all
SELECT '12/19/2018 21:35','A4'union all
SELECT '12/20/2018 21:36',NULL

I'm interested in a query which would output rows similar to the following:

| Start_Date | End_Date | Plan_ID | Count_PlantID |
| 12/19/2018 21:21 | 12/19/2018 21:22 | A1 | 2 |
| 12/19/2018 21:23 | 12/19/2018 21:24 | NULL | 2 |
| 12/19/2018 21:25 | 12/19/2018 21:27 | A2 | 3 |
| 12/19/2018 21:28 | 12/19/2018 21:29 | A3 | 2 |
| 12/19/2018 21:30 | 12/19/2018 21:32 | NULL | 3 |
| 12/19/2018 21:33 | 12/19/2018 21:35 | A4 | 3 |
| 12/20/2018 21:36 | 12/20/2018 21:36 | NULL | 1 |

For each NULL entries I made a key by looking at the previous starting [ID] column, then performed the Group By on that new column. Try it.

select min(Date_Time) as Start_Date, max(Date_Time) as End_Date
 , min(ID_Plan) as Plan_ID, count(1) as Count_PlanID
from (
 select s.*
 , case when ID_Plan is null 
 then 
 cast(
 (
 select max([ID]) 
 from #tb_CountFinal as x 
 where x.ID < s.ID and ID_Plan is not null
 ) as varchar(100)
 )
 else 
 ID_Plan 
 end as ID_Plan_Adv_Start
 from #tb_CountFinal as s
 ) as dt
group by dt.ID_Plan_Adv_Start
order by Start_Date