Ok thank you for your assistance!

The idea is the same, I cannot post the actual data I am working with so I have been using GPT to generate a sample set of data. From there,...

You're correct, the PK should actually be a composite key between the BlockStart, DispositionId,  ADUserGuid, and WorkflowId. If you feel you do not have adequate data or information related to...

-- Create the SampleData table

CREATE TABLE SampleData (

WorkflowId INT PRIMARY KEY,

ADUserGuid NVARCHAR(50),

DispositionId INT,

StartDate DATETIME,

EndDate DATETIME

);

GO

-- Insert sample data into the SampleData table

INSERT INTO SampleData (WorkflowId, ADUserGuid, DispositionId, StartDate, EndDate)

VALUES

(1, 'Tech1',...

I'm sorry, but what is the value add to including a script that generates sample data beyond what I have provided thus far?

If it was included then yes. Sorry, this is a different data set I put together. But the idea, process, and everything about the data is very similar.

It would look something like this below:

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

My apologies,

CREATE TABLE SampleData(

WorkflowId INT,

ADUserGuid NVARCHAR(50),

DispositionId INT,

StartDate DATETIME,

EndDate DATETIME

);

INSERT INTO SampleData (WorkflowId, ADUserGuid, DispositionId, StartDate, EndDate) VALUES

(1, 'User1', 101, '2023-01-01 01:23:45', '2023-01-01 02:34:56'),

(1, 'User1', 101, '2023-01-01 03:21:30', '2023-01-01 04:41:20'),

(1, 'User1',...

• This reply was modified 1 year ago by  max266.

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

1 2023-10-01 05:15:00 2023-10-01...

Thank you, but the main issue is less with generating quarter hour blocks, and instead creating a field called "ProductiveSeconds" that calculates how many seconds in any given quarter hour...