November 10, 2023 at 3:57 pm
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.
November 10, 2023 at 4:27 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?
Reproducibility
Consistency
Testability
Efficiency -- Saves time for people who are voluntarily trying to help free of charge, usually while working on their own jobs as well.
Gives other the ability to notice patterns/anomalies/gotchas you may not have noticed, or to question if your data looks "too" clean, and if their are non-happy-path cases that you aren't considering.
November 10, 2023 at 4:59 pm
Here is a solution using your sample data. Since you didn't supply the expected results as consumable data, I didn't compare the results with the expected results.
/* Begin of section to define the blocks. Skip if you already have such a table. */
WITH T AS
(
SELECT *
FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS t(n)
), Tally AS
(
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1 AS n
FROM T AS t1
CROSS JOIN T AS t2
), Blocks AS
(
SELECT DATEADD(MINUTE, 15 * n, '2023-01-01') AS Block_Start
, DATEADD(MINUTE, 15 * n + 15, '2023-01-01') AS Block_End
FROM Tally AS t
)
/* End of section to define the blocks. */
/* Beginning of actual solution. */
SELECT *, DATEDIFF(SECOND, d.Duration_Start, d.Duration_End) AS Duration
FROM Blocks AS b
INNER JOIN #SampleData AS sd
ON b.Block_Start <sd.EndDate
AND sd.StartDate < b.Block_End
CROSS APPLY (VALUES(CASE WHEN sd.StartDate < b.Block_Start THEN b.Block_Start ELSE sd.StartDate END
,CASE WHEN b.Block_End < sd.EndDate THEN b.Block_End ELSE sd.EndDate END
)
) d(Duration_Start, Duration_End);
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 10, 2023 at 5:02 pm
-- 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', 5, '2023-10-30 08:05:00', '2023-10-30 08:25:00'),
(1, 'Tech1', 5, '2023-10-30 08:20:00', '2023-10-30 08:35:00'),
(1, 'Tech2', 5, '2023-10-30 08:30:00', '2023-10-30 09:10:00'),
(1, 'Tech1', 5, '2023-10-30 09:05:00', '2023-10-30 09:45:00'),
(1, 'Tech3', 5, '2023-10-30 08:10:00', '2023-10-30 08:20:00'),
(1, 'Tech3', 5, '2023-10-30 08:30:00', '2023-10-30 08:45:00');
From here, I would time separated in quarter hour blocks with the calculated ProductiveSeconds fields for each ADuserGuid, WorkflowId, DispositionID, and BlockStart
November 10, 2023 at 6:29 pm
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 works before you put the script in here?
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK__SampleDa__5704A66A0B15D134'.
Cannot insert duplicate key in object 'dbo.SampleData'.
The duplicate key value is (1).
This is because "WorkflowId" is a primary key and every row has the same value.
November 10, 2023 at 6:39 pm
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 the problem to be able to confidently provide an answer that is fine, I just feel that I have explained this as best I can and I have no expectations of someone going out of their way during their working hours to provide an answer.
This is a problem I have been stuck on for quite some time and if someone chooses to help then I am much appreciative of that. Thus far I have provided sample data sets, outputs, and explained in as many words as I am now willing to the problem and desired results. I mean no disrespect but this is feeling a lot like Stack Exchange.
November 10, 2023 at 6:57 pm
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 provided so far do not relate to the inputs you have provided!
November 10, 2023 at 7:05 pm
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, extrapolating the request and explanations I've provided should be enough.
Expected output would be something along these lines - as I have previously stated. Yes, I understand the column names are different but I am not aware of any way this would negatively impact the actual ask I've communicated as changing field names would not impact the process or overall design of a query used to generate the expected output. If so, please enlighten me as I may just be slow to understanding some things.
If there is something I can provide clarification on, please let me know specifically what it is.
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 10, 2023 at 7:11 pm
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 output from those 6 rows?
November 10, 2023 at 7:14 pm
Ok thank you for your assistance!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply