July 18, 2010 at 2:53 am
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
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