W?adys?aw Bodzek (wbodzek_nospam (AT) poczta_nospam (DOT) onet.pl) writes:
I get fours spot for device one, and 4:20 for device two.

Here is a query that runs in SQL 2005. I have not taken the start time
in account, because I don't know what I should assume about the table.
Here I have simply assumed that both devices are off in the starting
point.

CREATE TABLE events (
e_works tinyint,
e_stamp DATETIME NOT NULL PRIMARY KEY
);
go
INSERT events (e_works, e_stamp)
SELECT 1, ' 01-01-2008 02:00' UNION ALL
SELECT 3, ' 01-01-2008 04:00' UNION ALL
SELECT 2, ' 01-01-2008 05:00' UNION ALL
SELECT 0, ' 01-01-2008 05:20' UNION ALL
SELECT 2, ' 01-01-2008 06:00' UNION ALL
SELECT 3, ' 01-01-2008 08:00' UNION ALL
SELECT 2, ' 01-01-2008 08:30' UNION ALL
SELECT 0, ' 01-01-2008 09:00' UNION ALL
SELECT 1, ' 01-01-2008 10:00'
go
DECLARE @endtime datetime;
SELECT @endtime = '20080101 10:30';

WITH numbered AS (
SELECT e_works, e_stamp,
rowno = row_number () OVER(ORDER BY e_stamp)
FROM events
UNION ALL
SELECT TOP 1 e_works, @endtime,
COUNT(*) OVER () + 1
FROM events
ORDER BY e_stamp DESC
)
SELECT dev1 = SUM(CASE WHEN a.e_works % 2 = 1
THEN datediff(minute, a.e_stamp, b.e_stamp)
ELSE 0
END),
dev2 = SUM(CASE WHEN a.e_works >= 2
THEN datediff(minute, a.e_stamp, b.e_stamp)
ELSE 0
END)
FROM numbered a
JOIN numbered b ON a.rowno + 1 = b.rowno;
go
DROP TABLE events;




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx