sql server new users: Hi,

I got a table like this:

datetime quantity status
10/30/06 12:01:01 50 ON
10/30/06 12:02:01 50 ON
11/01/06 08:01:03 20 OFF
11/01/06 08:02:03 30 OFF
11/01/06 08:03:03 10 ON
11/01/06 08:05:03 20 ON

I need to have a statement of state changes with summary of quantity, that
has first timestamp like this:

Statement of state changes
10/30/06 12:01:01 100 ON
11/01/06 08:01:03 50 OFF
11/01/06 08:03:03 30 ON

Is any way to use a query to achieve this task?


Thanks

luke

[quoted text, click to view]

Try the following. Your sample data included.

CREATE TABLE tbl (dt DATETIME NOT NULL PRIMARY KEY /* ?? */ , quantity
INT NOT NULL, status CHAR(3) NOT NULL);

INSERT INTO tbl (dt, quantity, status)
SELECT '2006-10-30T12:01:01', 50, 'ON' UNION ALL
SELECT '2006-10-30T12:02:01', 50, 'ON' UNION ALL
SELECT '2006-11-01T08:01:03', 20, 'OFF' UNION ALL
SELECT '2006-11-01T08:02:03', 30, 'OFF' UNION ALL
SELECT '2006-11-01T08:03:03', 10, 'ON' UNION ALL
SELECT '2006-11-01T08:05:03', 20, 'ON' ;

SELECT MIN(dt) AS dt, SUM(quantity) AS quantity, status
FROM
(SELECT t1.dt, t1.quantity, t1.status, MIN(t2.dt) AS xdt
FROM tbl AS t1
LEFT JOIN tbl AS t2
ON t1.dt < t2.dt
AND t1.status<>t2.status
GROUP BY t1.dt, t1.quantity, t1.status) AS t
GROUP BY xdt, status
ORDER BY MIN(dt);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--