Finding the total duration

Question

0.00/5 (No votes)

See more:

Hi All,
I have a table structure as follows

Date_Time            [590]  [591]  [592]
2013-03-27 11:00:00    125     7      1
2013-03-27 11:00:05    125     4      1
2013-03-27 11:00:10    125     7      1

2013-03-27 11:00:15    125     7      0
2013-03-27 11:00:20    155     7      0

2013-03-27 11:00:25    125     6      1
2013-03-27 11:00:30    185     7      1
2013-03-27 11:00:35    125     7      1

2013-03-27 11:00:40    125     2      0
2013-03-27 11:00:45    125     7      0

2013-03-27 11:00:50    155     7      1
2013-03-27 11:00:55    125     8      1
2013-03-27 11:01:00    125     7      1

In the above table,column [592] have the values either 0 or 1.when the value is 1 it means process was in progress and when the process completes the value of [592] changes to 0.Now, I want to calculate the process delay (ie) the time between taken between two process.Likewise i need to calculate the process delay for a day as well as for month.

In other words,I need to calculate the total duration of [592] when it was 0 for a day as well as for a month.

[edit]Code block added - OriginalGriff[/edit]

Posted 26-Mar-13 19:45pm

Member 8445821

Updated 27-Mar-13 23:43pm

v3

Add a Solution

Comments

_Maxxx_ 27-Mar-13 1:58am

Isn't 11:00:15 - 11:00:20 plus 11:00:40 - 11:00:45 only 10 seconds? Or are you looking for the difference between 11:00:20 and 11:00:40?

Maciej Los 27-Mar-13 3:39am

MySQL or MS SQL? Version?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-03-26T22:23:00

It's hard to answer a question with short description, but i'll try to guess...

Solution for MS SQL Server 2005 and higher.

SQL

--declare temporary table
DECLARE @tbl TABLE (Date_Time DATETIME, [590] INT, [591] INT, [592] INT)
--insert data
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:00', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:05', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:10', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:15', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:20', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:25', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:30', 1645, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:35', 1645, 0, 0)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:40', 1645, 0, 0)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:45', 1645, 0, 0)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:50', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:54:55', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:00', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:05', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:10', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:15', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:20', 1646, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:25', 1646, 0, 0)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:30', 1646, 0, 0)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:35', 1646, 0, 0)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:40', 1647, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:45', 1647, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:50', 1647, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:55:55', 1647, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:56:00', 1647, 0, 1)
INSERT INTO @tbl (Date_Time, [590], [591], [592])
VALUES ('2013-03-28 16:56:05', 1647, 0, 1)

--declare another temporary table
DECLARE @tmp TABLE(Process INT, StartDate DATETIME, EndDate DATETIME, DurationInSeconds INT)
--insert data 
INSERT INTO @tmp (Process, StartDate, EndDate, DurationInSeconds)
SELECT T.*, DATEDIFF(ss,[StartDate],[EndDate]) AS DurationInSeconds
FROM (
	SELECT t1.[590] AS Process, MIN(t1.Date_Time) AS StartDate, t2.EndDate 
	FROM @tbl AS t1 LEFT JOIN (
			SELECT [590] AS Process, MIN(Date_Time) AS EndDate
			FROM @tbl
			WHERE [592]=0
			GROUP BY [590]
			) AS t2 ON  t1.[590] = t2.Process
	WHERE t1.[592]=1
	GROUP BY t1.[590], t2.EndDate
	) AS T
ORDER BY T.Process

--view result: process start time, end time and remaining time of process
SELECT *
FROM @tmp

--execute 2 common table expression
;WITH cte_EndOfProcess AS
(   
        --get number of process and its ending date; RowNo -> temporary key
	SELECT ROW_NUMBER() OVER (ORDER BY Process) AS RowNo, Process, EndDate As ProcessEndsAt
	FROM @tmp
),
cte_StartNextProcess AS
(
        --get next number of process and its starting date; set RowNo-1 to create relation between this row and previous row of data
	SELECT ROW_NUMBER() OVER (ORDER BY Process) -1 AS RowNo, Process, StartDate As NextProcessStartsAt
	FROM @tmp 
) 
SELECT f.RowNo, f.Process, f.ProcessEndsAt, n.Process AS NextProcess, n.NextProcessStartsAt, DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt) AS TimeDiffBetweenProc
FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo

result of first SELECT statement:

Process StartDate               EndDate                 TimeDiff...
1645	2013-03-28 16:54:00.000	2013-03-28 16:54:35.000	35
1646	2013-03-28 16:54:50.000	2013-03-28 16:55:25.000	35
1647	2013-03-28 16:55:40.000	NULL	NULL

result of second SELECT statement (CTE's)

RowNo   Process ProcessEndsAt           NextP.. NextProcessStartsAt     TimeDiff...
1	1645	2013-03-28 16:54:35.000	1646	2013-03-28 16:54:50.000	15
2	1646	2013-03-28 16:55:25.000	1647	2013-03-28 16:55:40.000	15
3	1647	NULL	NULL	NULL	NULL

to count total, use:

SQL

SELECT COUNT(f.RowNo) CountOfProcesses, SUM(DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt)) AS TotalTime
FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo
WHERE COALESCE(DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt),0)>0

instead of

SQL

SELECT f.RowNo, f.Process, f.ProcessEndsAt, n.Process AS NextProcess, n.NextProcessStartsAt, DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt) AS TimeDiffBetweenProc
FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo

result:

CountOfProcesses TotalTime
2	         30