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