[SQL query] Generate a report from events?

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
.

Relevant Pages

  • RE: Calculated fields in main form based on criteria in sub form
    ... Main form (goat survey table and Total queries) ... NE Sum Query (uses information from the subform, ... other queries differ in what Quadrant equals) ...
    (microsoft.public.access.forms)
  • RE: Calculated fields in main form based on criteria in sub form
    ... Main form (goat survey table and Total queries) ... NE Sum Query (uses information from the subform, ... other queries differ in what Quadrant equals) ...
    (microsoft.public.access.forms)
  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Date Range Totals for Logical Fields
    ... Query reformatted for ease of reading ... Lead Date Lead Date By Month Signed Up Sum Of Active Recruit Sum Of Info ... Recruits Kits Sent Recruits Contact Invites ... Then put your criteria there. ...
    (microsoft.public.access.queries)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... Your idea was my first thought...so I relaxed that rule by allowing the Sum ... called OR how its used specifically within the query may have an impact? ... It could be that the Sum(HH CCY CFAMT) is not returning exactly ZERO. ... the select statement, and filled in specific criterion by criterion, to see ...
    (microsoft.public.access.queries)