Click here to Skip to main content
14,639,057 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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

Solution for MS SQL Server 2005 and higher.
--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:
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
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
   
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
Maciej Los 28-Mar-13 9:40am
   
See my updated solution ;)

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100