Group By Date - Track Changes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Date - Track Changes

Author

Topic

jamesg
Starting Member

1 Post

Posted - 2002-04-09 : 12:01:23

I have an archive table:
ArchiveDate SomeValue Status
2/3/2002, blah, Status1
3/3/2002, blah, Status2
4/3/2002, da blah, Status2
5/3/2002, da blah, Status1
6/3/2002, da blah, Status2
7/3/2002, Also blah, Status3
8/3/2002, Also blah, Status3

My problem is that I need the date of status changes only (including when reverting to a previously used status) i.e. the following result set:
ArchiveDate Status
2/3/2002, Status1
3/3/2002, Status2
5/3/2002, Status1
6/3/2002, Status2
7/3/2002, Status3

The only solution I can come up with (I don't use cursors) is to create a Status archive table to track only status changes.

Any ideas?
TIA

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-09 : 14:07:24

Here are two different possible solutions. The first is a set based solution; the second is an iterative solution that uses a temp table rather than a cursor.


--temp table for set-based solution
create table #temp (
    ArchiveDate smalldatetime not null,
    Status tinyint not null)
go
--data
insert #temp values('2/3/02',1)
insert #temp values('3/3/02',2)
insert #temp values('5/3/02',1)
insert #temp values('6/3/02',2)
insert #temp values('7/3/02',3)
insert #temp values('8/3/02',3)

--temp tables for iterative solution
select identity(int,1,1) as ident, *
into #temp2
from #temp
order by ArchiveDate ASC

select *
into #temp3
from #temp

delete #temp3
go


--This is the set-based solution
select
    ArchiveDate,
    Status
from
    #temp t1
where
    Status <> coalesce((
    select
        top 1
        Status
    from
        #temp
    where
        t1.ArchiveDate > ArchiveDate
    order by
        ArchiveDate DESC),0)
order by
    ArchiveDate
go

--This is the iterative solution
declare @ident int, @maxident int
select
    @ident = 1,
    @maxident = max(ident)
from
    #temp2
--loop throug the records
while @ident <= @maxident
begin
    insert #temp3(ArchiveDate, Status)
    --special case for the first record
    select
        ArchiveDate,
        Status
    from
        #temp2
    where
        ident = @ident and
        @ident = 1
    union
    select
        a.ArchiveDate,
        a.Status
    from
        #temp2 a
        inner join #temp2 b
            on (a.ident = b.ident + 1 and
                a.Status <> b.Status)
    where
        a.ident = @ident

    set @ident = @ident + 1
end
select
    ArchiveDate,
    Status
from
    #temp3
order by
    ArchiveDate
go

--clean up
drop table #temp
drop table #temp2
drop table #temp3
go

Hope that helps. Good Luck!!

<O>

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-04-10 : 16:09:02

>> I have an archive table: .. <<

It would have been nice to see the DDL for it, so we could answer your questions without guessing. My guess is that your schema is wrong. You want to archive events and events have a duration, so you should have had something like:

CREATE TABLE Archives
(keycolumn INTEGER NOT NULL PRIMARY KEY,
...
start_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
end_date DATETIME, -- null means still current
status CHAR(10) NOT NULL,
...);

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources