Click here to Skip to main content
15,940,921 members
Please Sign up or sign in to vote.
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
Updated 27-Mar-13 23:43pm
v3
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

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
 
Share this answer
 
v5
Comments
Member 8445821 28-Mar-13 5:43am    
Dear Maciej Los,

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.
Maciej Los 28-Mar-13 5:50am    
Thank you for explanation. I'll upgrade my answer as soon as possible, but i need to know how can i recognize when process starts and process ends. Is there any ID, key value ([590], [591] or other) for each process?
Member 8445821 28-Mar-13 6:09am    
Dear Maciej,
Process end as well as start are identified by [592].If the values of [592] becomes 1,it means process have been started and when the value changes to 0 it indicates that the process have been stopped.If you need a ID column means you can use [590] as ID and it will get incremented when [592] changes from 0 to 1
Maciej Los 28-Mar-13 6:24am    
Please, explain it on an example data. I'm not sure [590] is a key. Have a look at your data. Process no. 125 occurs many times with 0|1 state. Which date is a "start_date" and which is "end_date"?
Member 8445821 28-Mar-13 7:31am    
table will be like as follows:


[date_time] [590] [592]
2013-03-28 16:54:00 1645 1
2013-03-28 16:54:05 1645 1
2013-03-28 16:54:10 1645 1
2013-03-28 16:54:15 1645 1
2013-03-28 16:54:20 1645 1
2013-03-28 16:54:25 1645 1
2013-03-28 16:54:30 1645 1
2013-03-28 16:54:35 1645 0
2013-03-28 16:54:40 1645 0
2013-03-28 16:54:45 1645 0
2013-03-28 16:54:50 1646 1
2013-03-28 16:54:55 1646 1
2013-03-28 16:55:00 1646 1
2013-03-28 16:55:05 1646 1
2013-03-28 16:55:10 1646 1
2013-03-28 16:55:15 1646 1
2013-03-28 16:55:20 1646 1
2013-03-28 16:55:25 1646 0
2013-03-28 16:55:30 1646 0
2013-03-28 16:55:35 1646 0
2013-03-28 16:55:40 1647 1
2013-03-28 16:55:45 1647 1
2013-03-28 16:55:50 1647 1
2013-03-28 16:55:55 1647 1
2013-03-28 16:56:00 1647 1
2013-03-28 16:56:05 1647 1

Process 1645 started execution:

2013-03-28 16:54:00 1645 1

process 1645 completed during:

2013-03-28 16:54:35 1645 0


Next Process Started:
2013-03-28 16:54:50 1646 1

the value of [590] here incremented once the value of [592] changes from 0 to 1


now i want to calculate the total duration from 1645 completion to 1646 initialization which will be

2013-03-28 16:54:35 1645 0
2013-03-28 16:54:40 1645 0
2013-03-28 16:54:45 1645 0
2013-03-28 16:54:50 1645 1

so (2013-03-28 16:54:50) - (2013-03-28 16:54:35) = 15 secs will be the answer

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900