(

EmployeeId int,

StartDateTime datetime,

EndDateTime datetime,

ShiftType nvarchar(10)

);

insert into observation (EmployeeID, StartDateTime, EndDateTime, ShiftType) values

(12041, '2018-07-20 13:30:00.000', '2018-07-20 14:00:00.000', 'Shift' ),

(12041, '2018-07-20 14:00:00.000', '2018-07-20 15:00:00.000', 'Shift' ),

(12041, '2018-07-20 16:00:00.000', '2018-07-20 17:00:00.000', 'Public'),

(12041, '2018-07-20 17:00:00.000', '2018-07-20 17:06:00.000', 'Shift' ),

(12041, '2018-07-20 19:00:00.000', '2018-07-20 20:00:00.000', 'Public'),

(37821, '2018-07-20 09:00:00.000', '2018-07-20 10:00:00.000', 'Shift' );

select * from observation;

convert(date, o.StartDateTime) as StartDate,

convert(date, o.EndDateTime) as EndDate,

o.ShiftType

from observation o

group by o.EmployeeID,

convert(date, o.StartDateTime),

convert(date, o.EndDateTime),

o.ShiftType;

(

select o.EmployeeId,

convert(date, o.StartDateTime) as StartDate,

convert(date, o.EndDateTime) as EndDate,

o.ShiftType

from observation o

group by o.EmployeeID,

convert(date, o.StartDateTime),

convert(date, o.EndDateTime),

o.ShiftType

)

select g.*,

row_number() over(order by g.EmployeeId, g.StartDate, g.EndDate, g.ShiftType desc) as GroupId

from cte_group g;

(

select o.EmployeeId,

convert(date, o.StartDateTime) as StartDate,

convert(date, o.EndDateTime) as EndDate,

o.ShiftType

from observation o

group by o.EmployeeID,

convert(date, o.StartDateTime),

convert(date, o.EndDateTime),

o.ShiftType

),

cte_groupnum as

(

select g.*,

row_number() over(order by g.EmployeeId, g.StartDate, g.EndDate, g.ShiftType desc) as GroupId

from cte_group g

)

select o.*,

gn.GroupId

from observation o

join cte_groupnum gn

on gn.EmployeeId = o.EmployeeId

and gn.ShiftType = o.ShiftType

and gn.StartDate = convert(date, o.StartDateTime)

and gn.EndDate = convert(date, o.EndDateTime);