15,940,921 members
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.

Posted
Updated 27-Mar-13 23:43pm
v3
__ 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?

## 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.
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```

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```

v5
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