You would be much better off storing your values in the DB with the correct data types.  Then you can format the values any way you want in the presentation...

You are counting the total number of unique records.

You need to get a count per unique set of values

SELECT    MainHoldingID, OfferingElementID, [Count] = COUNT(*)
FROM...

You cold always try TABLESAMPLE

SELECT *
FROM SchemaName.TableName TABLESAMPLE (100000 ROWS)

Perhaps this code will point you in the right direction

WeekNum = DATEDIFF(dd, '1753', [YourDate]) /7 *7 -- Always works for week start on Monday

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, 3 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.