Take a look at the ROW_NUMBER() function

WITH cteData AS (
  SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY {Fields to group by} ORDER BY {Fields that...

Drew's code can be easily modified to include the extra condition for EventType = 5001.  It is still way more efficient than my code

WITH netobjectidgroups AS
(
SELECT *
,CASE...

You need to include cs.nxtEventID in the filter for EventType = 5001

DECLARE @netObjectId varchar(50) = '693';

WITH cteStart AS (
  SELECT
      s.netobjectid
...

The query that you got from Jingyang Li over at the MSDN SQL Server forum, can be easily modified to get the correct result by simply adding a...

Your posted DDL seems to have different values to your expected outcome values.

That said, I believe that the following code should get you on your way

WITH cteStart...

I would start by creating a table of valid time slots

IF OBJECT_ID(N'dbo.Timeslots', N'U') IS NOT NULL
BEGIN
  DROP TABLE dbo.Timeslots;
END;
GO

CREATE TABLE dbo.Timeslots (
    SlotStart...

What are your data types?

What happens when the StartTime and FinishTime cross over midnight?

Are the time durations always in 60 minute segments?

• This reply was modified 6 years, 2 months ago by DesNorton.

You need to use a CTE or a sub-query to generate your aggregates, and then join to that for the update

WITH cteUpdate as (
  select Milestone.SCHOOLNUM,...

• Pacific Standard Time - Note:...

Firstly, your random data generator - Using a recursive CTE to create a list of numbers is very inefficient.  Below is a more performant script to create your sample data....

Thanks for posting the alternate solution, which seems to have a better execution plan than the one that I posted.

Aint that the truth

I'm sorry, but this amused me...

The following code is based on a solution previously provided by Drew Allen

https://www.sqlservercentral.com/Forums/FindPost2025579.aspx

WITH cteStart AS (
  SELECT  aa.account_id, aa.active_date
     ...

You can use a CASE statement

CREATE VIEW [dbo].[GCDFview]
AS
SELECT p.FirstName, p.LastName, c.city, p.instructorInt as 'Instructor'
, s.stateAbbrveation AS 'State', 'USA' AS Country, 'GCDF' AS Certifications
, p.PeopleID + CASE WHEN...