Your query is probably trying to access all the tables at the same time and on a not very powerful machine with a slow disk it might be slowing it...

Heh... pretty typical for things like this.  The OP asks a question and then never participates in their own thread.  While some great code has been posted, there are...

Have you tried rebuilding the clustered index?

.

• This reply was modified 1 year, 10 months ago by Jonathan AC Roberts. Reason: duplicate

Try installing the table valued function here: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

Then to select 15 minute intervals as in your example:

SELECT Value
  FROM dbo.DateRange('1900-01-01 00:00', '1900-01-01 23:59'...

Why do you say "Expected output would be something along these lines"?

You only have 6 rows in your input data, surely it can't be too difficult to type the expected...

So what is the output you are expecting from that input?

All you need to do is provide a list of the rows you expect from your input.

The outputs you have...

So what is the output you are expecting from that input?

Also, there is an error in your script. Surely it isn't difficult to paste it into SSMS and check it...

It's quite simple really. All we want is a short script that creates the input data, then the results you expect from that input data.

What happened to the times 2023-01-01 01:23:45 to 2023-01-01 02:34:56 in your input?

Shouldn't they also be in the output?

So what is the expected output, that you don't know how to get, from your script above?

TechnicianID StartTime EndTime BlockStart BlockEnd TotalDurationSeconds ProductiveSeconds 1 2023-10-01 05:00:00 2023-10-01 05:10:00 2023-10-01 05:00:00 2023-10-01 05:15:00 600 600 1 2023-10-01 05:10:00 2023-10-01 05:25:00 2023-10-01 05:00:00 2023-10-01 05:15:00 900 300...

I asked ChatGPT to script it:

-- Drop the temporary table if it exists
DROP TABLE IF EXISTS #SomeTab;

-- Create the temporary table
CREATE TABLE #SomeTab (
   ...

From ChatGPT:

It seems that you need a workaround for the "calendar" table that is going away. In your updated code, you tried using @mydate and a "currentdate" table without populating...

sys.dm_exec_requests  and sys.dm_os_waiting_tasks dynamic management view provide more detailed information about the wait types and wait resources, making it easier to diagnose performance issues.