Post reply

Removing unneed entries from a linked list

  • July 18, 2010 at 2:53 am

    #226963

    I have a table that is a linked list of statuses. I would like to remove unneeded entries from the table. The following code works partly. It removes the unneeded entries and gives me the right start date, but I need the end date to come from maxID instead of minID.

    IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL

    DROP TABLE #t1;

    GO

    create table #t1 (

    [SiteStatusStart] [datetime] NULL,

    [SiteStatusEnd] [datetime] NULL,

    [p_key] [int] NOT NULL,

    [SiteStatus] [varchar](16) NOT NULL,

    [SiteType] [varchar](16) NOT NULL,

    [SiteVR_key] [int])

    insert into #t1 values('2008-12-03', NULL, '412', 'Active','Prime','31461')

    insert into #t1 values('2006-06-14','2006-07-14','787','Active','Prime','13947')

    insert into #t1 values('2006-07-14','2006-07-18','787','Active','Prime','14284')

    insert into #t1 values('2006-07-18','2008-06-10','787','Active','Prime','14303')

    insert into #t1 values('2008-06-10','2008-06-19','787','Active','Prime','25595')

    insert into #t1 values('2008-06-19',NULL,'787','Active','Prime','26095')

    insert into #t1 values('2006-09-22',NULL,'961','Active','Prime','15270')

    insert into #t1 values('2005-08-26','2005-09-02','1001','Active','Prime Only','7901')

    insert into #t1 values('2005-09-02','2006-09-08','1001','Active','Prime','7960')

    insert into #t1 values('2006-09-08','2006-11-09','1001','Active','Prime','14898')

    insert into #t1 values('2006-11-09','2006-11-09','1001','Active','Prime','16005')

    insert into #t1 values('2006-11-09','2007-05-09','1001','Active','Prime','16086')

    insert into #t1 values('2007-05-09','2008-09-25','1001','Site wont use','Prime','18439')

    insert into #t1 values('2008-09-25',NULL,'1001','Removed ','Prime','29037')

    insert into #t1 values('2005-12-22','2006-01-20','1176','Active','Prime Only','9286')

    insert into #t1 values('2006-01-20','2006-02-22','1176','Active','Prime Only','10165')

    insert into #t1 values('2006-02-22','2006-05-25','1176','Removed ','Prime','11689')

    insert into #t1 values('2006-05-25','2006-05-25','1176','Active','Prime','13755')

    insert into #t1 values('2006-05-25','2008-08-12','1176','Removed ','Prime','13756')

    insert into #t1 values('2008-08-12','2009-04-24','1176','Active','Prime','27834')

    insert into #t1 values('2009-04-24',NULL,'1176','Site wont use ','Prime','34503')

    insert into #t1 values('2005-08-26','2005-09-01','1312','Active','Prime Only','7908')

    insert into #t1 values('2005-09-01','2005-09-02','1312','Removed ','Prime Only','7956')

    insert into #t1 values('2005-09-02','2005-09-07','1312','Active','Prime Only','7988')

    insert into #t1 values('2005-09-07',NULL,'1312','Removed ','Prime','8008')

    select * from #t1

    SELECT M.SiteStatusStart

    ,M.SiteStatusEnd

    ,M.p_key

    ,M.SiteStatus

    ,M.SiteType

    ,M.SiteVR_key

    ,F.minID, F.maxID

    from #t1 as M

    inner join

    (SELECT p_key, SiteStatus, SiteType

    ,min(SiteVR_key) as minID

    ,max(SiteVR_key) as maxID

    from #t1

    group BY p_key

    ,SiteStatus, SiteType) as F

    on M.p_key = F.p_key

    and M.SiteStatus = F.SiteStatus

    and M.SiteType = F.SiteType

    and SiteVR_key = minID

    order by p_key, M.SiteStatusStart

    ,M.SiteStatusEnd, SiteVR_key

    Any help would be appreciated.

    Fred

  • July 19, 2010 at 3:18 am

    #1194758

    After further study I recognize the best solution is none at all.

    I will remove the end date all together and the list takes care of itself.

    Fred

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply