Re: [SQL query] Generate a report from events?

W?adys?aw Bodzek (wbodzek_nospam@xxxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: I upsized but these SQL statements are causing errors...
    ... I'm not too sure but according to books online they are usable here is what ... Returns the sum of all the values, or only the DISTINCT values, in the ... Returns one of two numeric or string values determined by a logical test. ... > what is the sql server equivalent sql of sum and iif? ...
    (microsoft.public.inetserver.asp.db)
  • Re: Grouping Dates
    ... AVGAS avgtrayweight ... sum these by the datemy ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Tough Sql Query
    ... wanted SUMbut the desired output you posted earlier ... That sum is hopefully always 0, or else something is really broken! ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Need help with SQL satement - newbie
    ... than one record for a given 'siteID' then it needs to total (sum) most ... Judging from the syntax, you are not using SQL Server (which is what ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Query training -- Complex queries
    ... > Update PositionsEOM ... Here you don't have a derived table, but a correlated subquery. ... UPDATE in Books Online. ... is not written for SQL Server. ...
    (microsoft.public.sqlserver.server)