[SQL query] Generate a report from events?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Władysław Bodzek - 26 Feb 2008 15:18 GMT

I have table an MSSQL table (I only now postgresql good) so I ask you
for help.

CREATE TABLE events (
e_works INT,
e_stamp DATETIME (or sth similar, i don't know mssql)
);

We have 2 devices monitored. When any of the devices is turned on or off
a row in this table is generated for that event:
- e_works -
0 if both devices are turned off after the change,
1 if first device is turned on, second is off,
2 if second device is turned on, first is off,
3 if both devices are turned on,
in other words it is bit-packed state of the power of devices
- e_stamp -
current date and time.

I need to get the sum of time periods where device #1 is turned on and
seperately sum for device #2. The whole summary need to be taken between
time constraints (assume '%date_start%' and '%date_end%', please write
that phrases in query).

1. Could you help me build such a query?

2. Or if not, maybe you can help me how can I use the date from previous
row (or next) when I am processing some row?

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

Thanks for any help and sorry for my poor english
Wladek

Reply to this Message

Erland Sommarskog - 26 Feb 2008 22:45 GMT

W?adys?aw Bodzek (wbodzek_nospam@poczta_nospam.onet.pl) writes:

> Example:
> Table events:
[quoted text clipped - 19 lines]
> 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;

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message