W?adys?aw Bodzek (wbodzek_nospam@poczta_nospam.onet.pl) writes:
> Example:
> Table events:
> id e_works e_stamp means dev#1 dev#2
> Row 1: 1 01-01-2008 02:00 ON OFF
> Row 2: 3 01-01-2008 04:00 ON ON
> Row 3: 2 01-01-2008 05:00 OFF ON
> Row 4: 0 01-01-2008 05:20 OFF OFF
> Row 5: 2 01-01-2008 06:00 OFF ON
> Row 6: 3 01-01-2008 08:00 ON ON
> Row 7: 2 01-01-2008 08:30 OFF ON
> Row 8: 0 01-01-2008 09:00 OFF OFF
> Row 9: 1 01-01-2008 10:00 ON OFF
> (now is 10:30)
>
> sum of device #1 between midnight and 10:00:
> (counts rows 1,2,6):
> 02:30 + 00:30 + 00:30
>= 03:30
>
> sum of device #2 between 04:30 and 10:00:
> (counts rows 2(partially),3,5,6,7):
> 00:30 + 00:20 + 02:00 + 00:30 + 00:30
>= 02:50

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@sommarskog.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