Can This Be Done? - Aaron Bertrand [SQL Server MVP]

Here is one way.  Not sure if it matches all of your business requirements
for a larger set...


USE tempdb;
GO

CREATE TABLE dbo.foo
(
[Status] CHAR(1),
dt DATETIME
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.foo SELECT 'A', '2007-12-21T14:31:58';
INSERT dbo.foo SELECT 'A', '2007-12-23T10:00:00';
INSERT dbo.foo SELECT 'B', '2007-12-26T08:28:11';
INSERT dbo.foo SELECT 'A', '2007-12-26T08:28:51';
INSERT dbo.foo SELECT 'C', '2008-03-11T12:58:46';
INSERT dbo.foo SELECT 'C', '2008-03-11T12:59:40';
INSERT dbo.foo SELECT 'C', '2008-05-07T17:25:46';
GO
;
WITH f1 AS
(
SELECT Status, dt,
r = ROW_NUMBER() OVER (ORDER BY dt)
FROM dbo.foo f
), f2 AS
(
SELECT Status, dt,
r = ROW_NUMBER() OVER (ORDER BY dt)-1
FROM dbo.foo
)
SELECT
Status = COALESCE(f2.status, f1.status),
dt = COALESCE(f2.dt, f1.dt)
FROM f1 LEFT OUTER JOIN f2
ON f1.r = f2.r
AND f1.Status != f2.Status
WHERE (f2.Status IS NULL AND f1.r = 1)
OR (f2.Status IS NOT NULL)
ORDER BY f1.dt;

GO

DROP TABLE dbo.foo;
GO




On 5/8/08 11:32 AM, in article
45df004b-8363-45b8-9e4f-ccb79b6f6278@e53g2000hsa.googlegroups.com,

Can This Be Done? - Plamen Ratchev

Here is one way (SQL Server 2005):

WITH Ranked
AS
(SELECT status, status_date,
ROW_NUMBER() OVER(ORDER BY status_date) -
ROW_NUMBER() OVER(PARTITION BY status
ORDER BY status_date) AS grp
FROM Foo)
SELECT status, MIN(status_date) AS status_date
FROM Ranked
GROUP BY status, grp;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Can This Be Done? - bria

I am having fits trying to write a query or stored procedure to
accomplish relative grouping of items in a table. Take the following
example data set, which is order by a date field:

StatusA   2007-12-21 14:31:58.707
StatusA   2007-12-23 10:00:00.000
StatusB   2007-12-26 08:28:11.947
StatusA   2007-12-26 08:28:51.430
StatusC   2008-03-11 12:58:46.473
StatusC   2008-03-11 12:59:40.520
StatusC   2008-05-07 17:25:46.743

I would like a stored procedure to return the following results:

StatusA   2007-12-21 14:31:58.707
StatusB   2007-12-26 08:28:11.947
StatusA   2007-12-26 08:28:51.430
StatusC   2008-03-11 12:58:46.473

Is this possible, and if so how? I know I can bring this result set
back into my program and do it in code, but I'd rather do it in a
stored procedure if possible.

Can This Be Done? - --CELKO--

Can you change this table to correct the design flaw?  You model
history with (start_time, end_time) pairs and not chronons.  Use a
NULL end_date to show a duration that it is still current.

Can This Be Done? - Razvan Socol

Here is another way of saying (almost) the same thing:

WITH x AS (
SELECT Status, dt,
ROW_NUMBER() OVER (ORDER BY dt) as r
FROM dbo.foo
)
SELECT f1.status, f1.dt
FROM x f1 LEFT JOIN x f2 ON f1.r = f2.r+1
AND f1.Status <> f2.Status
WHERE (f2.r IS NULL AND f1.r = 1)
OR (f2.r IS NOT NULL)
ORDER BY f1.dt;

--
Razvan Socol
SQL Server MVP