February 23, 2017 at 10:33 am
Hi
I need to find the total time had spend by finding min time and max time between number of rows for each task.
Eg Data
Create Table #Temp
(
Taskid int,
StartTime datetime,
EndTime DateTime
)
INSERT into #Temp
select 1, '2017-02-23 09:48:47.413',NULL Union all
select 1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' Union all
select 1, '2017-02-23 09:49:47.413',Null Union all
select 2, '2017-02-23 10:40:47.413',2017-02-23 11:55:47.413 Union all
select 2, '2017-02-23 10:39:47.413', NULL Union all
select 2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413'
select * from #Temp
For Task 1, Minimum Start Time from 3 Rows is 2017-02-23 09:48:47.413 and max End time is 2017-02-23 10:59:47.413
Required output DateDiff(Minute, 2017-02-23 09:48:47.413, 2017-02-23 10:59:47.413,) = 71
For Task 2, Minimum Start Time from 3 rows is 2017-02-23 10:39:47.413 and Max End Time is 2017-02-23 11:55:47.413
Required Output : 76 Min
How do i achieve this requirement when the time stamp are in different rows with respect to TaskID.
Many Thanks in Advance.
February 23, 2017 at 10:42 am
Well based on your data assuming that EndTime will always have at least 1 not null value greater than the minimum time from Starttime,
SELECT Taskid, MIN(StartTime), MAX(EndTime), DATEDIFF(minute, MIN(StartTime), MAX(EndTime))
FROM #Temp
GROUP BY Taskid
Also it looks like your sample results are wrong 😉 the minimum for task 2 is 2017-02-23 10:11:47.413
February 24, 2017 at 3:42 am
Thank you ZZartin, I am glad for your reply. It helped me.
February 24, 2017 at 1:12 pm
Just because I think this is helpful, I wrote a tSQLt test for this. If you give it a try, you might find it's easy to test lots of cases quickly and different code.Create Table TimeTests
(
Taskid int,
StartTime datetime,
EndTime DateTime
)
INSERT into TimeTests
VALUES
(1, '2017-02-23 09:48:47.413',NULL ),
(1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ),
(1, '2017-02-23 09:49:47.413',Null ),
(2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ),
(2, '2017-02-23 10:39:47.413', NULL ),
(2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413')
select * from TimeTests
GO
CREATE OR ALTER PROCEDURE RunTimeTests
AS
BEGIN
SELECT
Taskid,
MIN(StartTime),
MAX(EndTime),
DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime))
FROM TimeTests
GROUP BY Taskid;
END;
GO
EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests'
GO
CREATE OR ALTER PROCEDURE [tTimeTests].[test calculation min max time from timetests]
AS
BEGIN
-- assemble
EXEC tsqlt.FakeTable @TableName = N'TimeTests', @SchemaName = N'dbo'
INSERT into TimeTests
VALUES
(1, '2017-02-23 09:48:47.413',NULL ),
(1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ),
(1, '2017-02-23 09:49:47.413',Null ),
(2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ),
(2, '2017-02-23 10:39:47.413', NULL ),
(2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413')
CREATE TABLE tTimeTests.Expected
( taskid INT, Mindtime DATETIME2(3), maxtime DATETIME2(3), Minutes int)
INSERT tTimeTests.Expected
VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
, (2, '2017-02-23 10:11:47.413', '2017-02-23 11:55:47.413', 104)
SELECT *
INTO tTimeTests.Actual
FROM tTimeTests.Expected
WHERE 1 = 0;
-- act
INSERT tTimeTests.Actual EXEC RunTimeTests;
-- assert
EXEC tsqlt.AssertEqualsTable
@Expected = N'tTimeTests.Expected', @Actual = N'tTimeTests.Actual', @Message = N'Incorrect times'
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply