Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Third running streak

declare @t table (Id int, dt datetime, value int)
set dateformat 'dmy'
insert into @t
 
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8
 
SELECT      Id,
            MIN(dt) AS Startdt,
            MAX(dt) AS Enddt,
            MIN(value) AS Value
FROM        (
                SELECT Id,
                        dt,
                        value,
                        ROW_NUMBER() OVER (PARTITION BY Id, value ORDER BY dt) AS recID,
                        ROW_NUMBER() OVER (ORDER BY value, dt) AS grpID,
                        ROW_NUMBER() OVER (ORDER BY dt) AS colID
                FROM    @t
            ) AS d
GROUP BY    Id,
            grpID - recID,
            grpID - colID
ORDER BY    id,
            MIN(dt)

Print | posted on Friday, August 21, 2009 11:19 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET