November 8, 2023 at 11:33 pm
I've been working on this problem for a few days now and cannot find a way to solve it.
blockstart | blockend | next_blockstart | ADUserGuid | DispositionId | WorkflowId | StartDate | EndDate
-------------------------|--------------------------|------------------------|--------------------------------------|---------------|------------|------------------------|------------------------
2023-10-01 05:00:00.00 | 2023-10-01 05:14:59.000 | 2023-10-01 05:15:00.000| 123e4567-e89b-12d3-a456-426614174000 | 101 | 1001 | 2023-10-01 05:00:01.00 | 2023-10-01 05:14:58.000
2023-10-01 05:15:00.00 | 2023-10-01 05:29:59.000 | 2023-10-01 05:30:00.000| 123e4567-e89b-12d3-a456-426614174001 | 102 | 1002 | 2023-10-01 05:15:01.00 | 2023-10-01 05:29:58.000
2023-10-01 05:30:00.00 | 2023-10-01 05:44:59.000 | 2023-10-01 05:45:00.000| 123e4567-e89b-12d3-a456-426614174002 | 103 | 1003 | 2023-10-01 05:30:01.00 | 2023-10-01 05:44:58.000
2023-10-01 05:30:00.00 | 2023-10-01 05:59:59.000 | 2023-10-01 06:00:00.000| 123e4567-e89b-12d3-a456-426614174003 | 104 | 1004 | 2023-10-01 05:30:01.00 | 2023-10-01 05:59:58.000
Above is a small sample of some of the data I am working with.
Basically, I am working with a full years worth of data that is recording technician productivity. Technicians will begin work on a report that comes in to our system denoted by StartDate, and complete the report denoted by EndDate.
The BlockStart and BlockEnd columns are two columns I have created. The purpose of the columns is to divide a 24 hour day into 15 minute blocks of time so that we can determine "in this quarter hour window, how many reports did a technician complete? How many did they begin? How many did they finish? What was their productivity? How many minutes were they not actively working on reports (i.e. bathroom breaks, lunch, etc.).
A 15 minute block of time translates into 900 seconds (here I am using 899).
There are a couple scenarios here:
1. A technician starts and finishes a report within one quarter hour block
2. A technician starts a report in one quarter hour block and finishes in another (later) quarter hour block.
Reports can take anywhere from 5 minutes to over an hour (60 minutes would be 4 quarter hour blocks (4 x 15 = 60 min).
My job is to create a new column and calculate the time it takes a technician to complete a report, this field will be called "ElapsedSeconds" and is the difference in seconds between the StartDate and the EndDate.
However, let's say a technician starts a report at 7:10PM (falls within the 7:00 quarter hour block) and doesn't finish until 7:55PM -> 45 minutes have elapsed starting in the 7:00PM quarter hour block and ending in the 7:45PM quarter hour block. ElapsedSeconds would then be 45 x 60 = 2700 seconds.
BUT! How would I show actual ElapsedSeconds across the multiple quarter hour blocks?
For example, if the report was started at 7:10 in the 7:00 quarter hour block, they would have worked 5 minutes (or 300 seconds) in that block, the 7:15-7:30 blocks would be the full 900 seconds (a technician cannot possibly have more productive seconds than 900 per quarter hour block).
So at the beginning of the 7:15 quarter hour block, we would subtract the 300 seconds that have elapsed from the 2700 total for a difference of 2400 seconds remaining. This remaining time should then be allocated to the next quarter hour block, and any remaining time (time exceeding 900 seconds) should be allocated to the next block, etc.
How can I do this?
Another caveat is that sometimes technicians open multiple reports, in which case they will have multiple elapsed times for each quarter hour block that exceeds 900 seconds, in which case I need to record those and allocate any excess to the next block as with the above.
I have been on ChatGPT, and worked various ways of accomplishing this. My boss has been pressuring me to complete this as I have been working on it all week but I feel like I am at a loss.
* Edited to provide sample output:
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 05:25:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 600
1 2023-10-01 06:00:00 2023-10-01 06:45:00 2023-10-01 06:00:00 2023-10-01 06:15:00 2700 900
1 2023-10-01 06:15:00 2023-10-01 06:45:00 2023-10-01 06:15:00 2023-10-01 06:30:00 2700 900
1 2023-10-01 06:30:00 2023-10-01 06:45:00 2023-10-01 06:30:00 2023-10-01 06:45:00 2700 900
2 2023-10-01 05:05:00 2023-10-01 05:20:00 2023-10-01 05:00:00 2023-10-01 05:15:00 900 600
2 2023-10-01 05:15:00 2023-10-01 05:20:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 300
2 2023-10-01 05:30:00 2023-10-01 05:35:00 2023-10-01 05:30:00 2023-10-01 05:45:00 300 300
2 2023-10-01 07:00:00 2023-10-01 07:50:00 2023-10-01 07:00:00 2023-10-01 07:15:00 3000 900
2 2023-10-01 07:15:00 2023-10-01 07:50:00 2023-10-01 07:15:00 2023-10-01 07:30:00 3000 900
2 2023-10-01 07:30:00 2023-10-01 07:50:00 2023-10-01 07:30:00 2023-10-01 07:45:00 3000 900
2 2023-10-01 07:45:00 2023-10-01 07:50:00 2023-10-01 07:45:00 2023-10-01 08:00:00 3000 300
3 2023-10-01 05:45:00 2023-10-01 06:00:00 2023-10-01 05:45:00 2023-10-01 06:00:00 900 900
3 2023-10-01 08:15:00 2023-10-01 09:00:00 2023-10-01 08:15:00 2023-10-01 08:30:00 2700 900
3 2023-10-01 08:30:00 2023-10-01 09:00:00 2023-10-01 08:30:00 2023-10-01 08:45:00 2700 900
3 2023-10-01 08:45:00 2023-10-01 09:00:00 2023-10-01 08:45:00 2023-10-01 09:00:00 2700 900
Something like this (this is the result of one of my attempts)
Except, there would only be one litem per UserId/QuarterHourBlock/DispositionId/WorkflowId
November 9, 2023 at 9:49 am
It would be helpful if you could post your test data in consumable format, eg
DROP TABLE IF EXISTS #SomeTab;
CREATE TABLE #SomeTab(...)
INSERT #SomeTab(...)
SELECT ...
and also show us what the desired results would look like, based on that test data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 9, 2023 at 11:34 am
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 (
blockstart DATETIME,
blockend DATETIME,
next_blockstart DATETIME,
ADUserGuid UNIQUEIDENTIFIER,
DispositionId INT,
WorkflowId INT,
StartDate DATETIME,
EndDate DATETIME
);
-- Insert the data into the temporary table
INSERT INTO #SomeTab (blockstart, blockend, next_blockstart, ADUserGuid, DispositionId, WorkflowId, StartDate, EndDate)
VALUES
('2023-10-01 05:00:00.000', '2023-10-01 05:14:59.000', '2023-10-01 05:15:00.000', '123e4567-e89b-12d3-a456-426614174000', 101, 1001, '2023-10-01 05:00:01.000', '2023-10-01 05:14:58.000'),
('2023-10-01 05:15:00.000', '2023-10-01 05:29:59.000', '2023-10-01 05:30:00.000', '123e4567-e89b-12d3-a456-426614174001', 102, 1002, '2023-10-01 05:15:01.000', '2023-10-01 05:29:58.000'),
('2023-10-01 05:30:00.000', '2023-10-01 05:44:59.000', '2023-10-01 05:45:00.000', '123e4567-e89b-12d3-a456-426614174002', 103, 1003, '2023-10-01 05:30:01.000', '2023-10-01 05:44:58.000'),
('2023-10-01 05:30:00.000', '2023-10-01 05:59:59.000', '2023-10-01 06:00:00.000', '123e4567-e89b-12d3-a456-426614174003', 104, 1004, '2023-10-01 05:30:01.000', '2023-10-01 05:59:58.000');
-- Query the temporary table if needed
SELECT * FROM #SomeTab;
https://chat.openai.com/share/298f1c81-9490-47e0-9d9b-b3d2cf41302c
November 9, 2023 at 3:14 pm
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 block a technician was working on a report? For example, 900 seconds = 15 minutes. That is the maximum amount of time a technician can be productive in a quarter hour block. But technicians never start a report directly on the start of the quarter (15:00 vs 15:02 for example).
And many reports take multiple quarter hour blocks - this part is really tricky and where I am stuck. If a technician works a report spanning across multiple quarter hour blocks then some of those blocks would show a ProductiveSeconds amount of 900, but how can I allocate the remaining time to the next quarter hour block?
November 9, 2023 at 3:21 pm
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 05:25:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 600
1 2023-10-01 06:00:00 2023-10-01 06:45:00 2023-10-01 06:00:00 2023-10-01 06:15:00 2700 900
1 2023-10-01 06:15:00 2023-10-01 06:45:00 2023-10-01 06:15:00 2023-10-01 06:30:00 2700 900
1 2023-10-01 06:30:00 2023-10-01 06:45:00 2023-10-01 06:30:00 2023-10-01 06:45:00 2700 900
2 2023-10-01 05:05:00 2023-10-01 05:20:00 2023-10-01 05:00:00 2023-10-01 05:15:00 900 600
2 2023-10-01 05:15:00 2023-10-01 05:20:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 300
2 2023-10-01 05:30:00 2023-10-01 05:35:00 2023-10-01 05:30:00 2023-10-01 05:45:00 300 300
2 2023-10-01 07:00:00 2023-10-01 07:50:00 2023-10-01 07:00:00 2023-10-01 07:15:00 3000 900
2 2023-10-01 07:15:00 2023-10-01 07:50:00 2023-10-01 07:15:00 2023-10-01 07:30:00 3000 900
2 2023-10-01 07:30:00 2023-10-01 07:50:00 2023-10-01 07:30:00 2023-10-01 07:45:00 3000 900
2 2023-10-01 07:45:00 2023-10-01 07:50:00 2023-10-01 07:45:00 2023-10-01 08:00:00 3000 300
3 2023-10-01 05:45:00 2023-10-01 06:00:00 2023-10-01 05:45:00 2023-10-01 06:00:00 900 900
3 2023-10-01 08:15:00 2023-10-01 09:00:00 2023-10-01 08:15:00 2023-10-01 08:30:00 2700 900
3 2023-10-01 08:30:00 2023-10-01 09:00:00 2023-10-01 08:30:00 2023-10-01 08:45:00 2700 900
3 2023-10-01 08:45:00 2023-10-01 09:00:00 2023-10-01 08:45:00 2023-10-01 09:00:00 2700 900
Here is a sample output from one of my attempts.
Notice, the ProductiveSeconds field. This is really at the crux of the issue. When a technician works a report that spans across multiple quarter hour blocks, I would like to know their ProductiveSeconds. To do this, requires taking the total time they spent on the report (difference between StartDate and EndDate = TotalElapsedSeconds) and then allocating that amount between the quarter hour blocks they worked in - determined by the StartDate and EndDate fields.
Making things more difficult is the fact that technicians may be working on multiple reports at once. Their productivity cannot exceed 900 seconds in any quarter hour block because 900 seconds = 15 minutes. And they almost never begin working a report right at the quarter hour mark (3:00, 3:15, etc.).
November 9, 2023 at 3:48 pm
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 05:25:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 600 1 2023-10-01 06:00:00 2023-10-01 06:45:00 2023-10-01 06:00:00 2023-10-01 06:15:00 2700 900 1 2023-10-01 06:15:00 2023-10-01 06:45:00 2023-10-01 06:15:00 2023-10-01 06:30:00 2700 900 1 2023-10-01 06:30:00 2023-10-01 06:45:00 2023-10-01 06:30:00 2023-10-01 06:45:00 2700 900 2 2023-10-01 05:05:00 2023-10-01 05:20:00 2023-10-01 05:00:00 2023-10-01 05:15:00 900 600 2 2023-10-01 05:15:00 2023-10-01 05:20:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 300 2 2023-10-01 05:30:00 2023-10-01 05:35:00 2023-10-01 05:30:00 2023-10-01 05:45:00 300 300 2 2023-10-01 07:00:00 2023-10-01 07:50:00 2023-10-01 07:00:00 2023-10-01 07:15:00 3000 900 2 2023-10-01 07:15:00 2023-10-01 07:50:00 2023-10-01 07:15:00 2023-10-01 07:30:00 3000 900 2 2023-10-01 07:30:00 2023-10-01 07:50:00 2023-10-01 07:30:00 2023-10-01 07:45:00 3000 900 2 2023-10-01 07:45:00 2023-10-01 07:50:00 2023-10-01 07:45:00 2023-10-01 08:00:00 3000 300 3 2023-10-01 05:45:00 2023-10-01 06:00:00 2023-10-01 05:45:00 2023-10-01 06:00:00 900 900 3 2023-10-01 08:15:00 2023-10-01 09:00:00 2023-10-01 08:15:00 2023-10-01 08:30:00 2700 900 3 2023-10-01 08:30:00 2023-10-01 09:00:00 2023-10-01 08:30:00 2023-10-01 08:45:00 2700 900 3 2023-10-01 08:45:00 2023-10-01 09:00:00 2023-10-01 08:45:00 2023-10-01 09:00:00 2700 900
Here is a sample output from one of my attempts.
Notice, the ProductiveSeconds field. This is really at the crux of the issue. When a technician works a report that spans across multiple quarter hour blocks, I would like to know their ProductiveSeconds. To do this, requires taking the total time they spent on the report (difference between StartDate and EndDate = TotalElapsedSeconds) and then allocating that amount between the quarter hour blocks they worked in - determined by the StartDate and EndDate fields.
Making things more difficult is the fact that technicians may be working on multiple reports at once. Their productivity cannot exceed 900 seconds in any quarter hour block because 900 seconds = 15 minutes. And they almost never begin working a report right at the quarter hour mark (3:00, 3:15, etc.).
What we are asking for is a sample input in the form of a script, i.e. a table create and insert script. Then the output you are expecting from that input sample data.
November 9, 2023 at 5:26 pm
You're doing your calculation backward. You're calculating the duration and then dividing it up into blocks instead of dividing it up into blocks and calculating the duration in each block. If you want tested code, please provide CONSUMMABLE data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 9, 2023 at 6:18 pm
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', 101, '2023-01-01 12:15:15', '2023-01-01 12:22:13'),
(1, 'User2', 101, '2023-01-01 11:11:11', '2023-01-01 02:33:12')
;
Here is a very small sample set. From here, I create quarter hour block fields (BlockStart, BlockEnd) as well as an ElapsedSeconds field (DATEDIFF(SECOND, StartDate, EndDate)). But struggle with the ProductiveSeconds portion (field) that determines how productive a technician was per quarter hour block (15 minutes or 900 seconds).
November 9, 2023 at 6:25 pm
So what is the expected output, that you don't know how to get, from your script above?
November 9, 2023 at 6:50 pm
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 05:15:00 900 300
1 2023-10-01 05:15:00 2023-10-01 05:25:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 600
1 2023-10-01 06:00:00 2023-10-01 06:45:00 2023-10-01 06:00:00 2023-10-01 06:15:00 2700 900
1 2023-10-01 06:15:00 2023-10-01 06:45:00 2023-10-01 06:15:00 2023-10-01 06:30:00 2700 900
1 2023-10-01 06:30:00 2023-10-01 06:45:00 2023-10-01 06:30:00 2023-10-01 06:45:00 2700 900
2 2023-10-01 05:05:00 2023-10-01 05:20:00 2023-10-01 05:00:00 2023-10-01 05:15:00 900 600
2 2023-10-01 05:15:00 2023-10-01 05:20:00 2023-10-01 05:15:00 2023-10-01 05:30:00 900 300
2 2023-10-01 05:30:00 2023-10-01 05:35:00 2023-10-01 05:30:00 2023-10-01 05:45:00 300 300
2 2023-10-01 07:00:00 2023-10-01 07:50:00 2023-10-01 07:00:00 2023-10-01 07:15:00 3000 900
2 2023-10-01 07:15:00 2023-10-01 07:50:00 2023-10-01 07:15:00 2023-10-01 07:30:00 3000 900
2 2023-10-01 07:30:00 2023-10-01 07:50:00 2023-10-01 07:30:00 2023-10-01 07:45:00 3000 900
2 2023-10-01 07:45:00 2023-10-01 07:50:00 2023-10-01 07:45:00 2023-10-01 08:00:00 3000 300
3 2023-10-01 05:45:00 2023-10-01 06:00:00 2023-10-01 05:45:00 2023-10-01 06:00:00 900 900
3 2023-10-01 08:15:00 2023-10-01 09:00:00 2023-10-01 08:15:00 2023-10-01 08:30:00 2700 900
3 2023-10-01 08:30:00 2023-10-01 09:00:00 2023-10-01 08:30:00 2023-10-01 08:45:00 2700 900
3 2023-10-01 08:45:00 2023-10-01 09:00:00 2023-10-01 08:45:00 2023-10-01 09:00:00 2700 900
November 9, 2023 at 7:01 pm
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?
November 9, 2023 at 7:57 pm
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.
November 10, 2023 at 10:20 am
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.
Please script your sample data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2023 at 12:36 pm
max266 wrote: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.
Please script your sample data.
He did but it's a lot shorter than the original or the expected output sample.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2023 at 3:38 pm
I'm sorry, but what is the value add to including a script that generates sample data beyond what I have provided thus far?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply