June 22, 2012 at 11:47 am
Here is something for you to play with.
create table #mytable(
ID varchar(12),
EffectiveDate datetime,
TermDate datetime,
[Status] varchar(1)
);
GO
insert into #mytable values ('503484823','01/01/2012',Null,'A');
insert into #mytable values ('592879472','01/01/2012',Null,'A');
insert into #mytable values ('929921311','01/01/2012',Null,'A');
insert into #mytable values ('239826234','01/01/2012',Null,'A');
insert into #mytable values ('616546060','12/01/2011',Null,'A');
insert into #mytable values ('978047548','12/01/2011',Null,'A');
insert into #mytable values ('524739641','12/01/2011',Null,'A');
insert into #mytable values ('428547606','12/01/2011','03/31/2012','A');
insert into #mytable values ('428547606','04/01/2012',NULL,'A');-- Added record
insert into #mytable values ('951720863','12/01/2011','03/31/2012','A');
insert into #mytable values ('951720863','04/01/2012','04/30/2011','D');-- Added record
insert into #mytable values ('951720863','05/01/2012',Null,'A');-- Added record
insert into #mytable values ('131333186','12/01/2011',Null,'A');
insert into #mytable values ('260895908','12/01/2011',Null,'A');
insert into #mytable values ('553625622','12/01/2011',Null,'A');
insert into #mytable values ('919168813','12/01/2011','01/31/2012','A');
insert into #mytable values ('557902697','12/01/2011',Null,'A');
insert into #mytable values ('379088668','12/01/2011','02/29/2012','A');
insert into #mytable values ('602081252','12/01/2011',Null,'A');
insert into #mytable values ('716393277','12/01/2011','01/31/2012','A');
insert into #mytable values ('300006158','12/01/2011',Null,'A');
insert into #mytable values ('157499166','01/01/2012','03/31/2012','A');
insert into #mytable values ('249228354','01/01/2012',Null,'A');
insert into #mytable values ('446726968','01/01/2012','04/30/2012','A');
insert into #mytable values ('975883457','01/01/2012','03/31/2012','A');
insert into #mytable values ('847326744','01/01/2012',Null,'A');
insert into #mytable values ('434075176','01/01/2012','03/31/2012','A');
insert into #mytable values ('296098224','01/01/2012','04/30/2012','A');
insert into #mytable values ('370215156','12/01/2011',Null,'A');
insert into #mytable values ('647742333','12/01/2011',Null,'A');
insert into #mytable values ('528683544','12/01/2011','01/31/2012','A');
insert into #mytable values ('717022168','12/01/2011',Null,'A');
insert into #mytable values ('417045232','12/01/2011','02/29/2012','A');
insert into #mytable values ('485375006','12/01/2011',Null,'A');
insert into #mytable values ('963527175','12/01/2011','04/30/2012','A');
insert into #mytable values ('328693654','12/01/2011',Null,'A');
insert into #mytable values ('631187903','12/01/2011','02/29/2012','A');
insert into #mytable values ('903184004','12/01/2011',Null,'A');
insert into #mytable values ('833363486','12/01/2011',Null,'A');
insert into #mytable values ('185935730','12/01/2011',Null,'A');
insert into #mytable values ('479006496','12/01/2011','01/31/2012','A');
insert into #mytable values ('302562202','12/01/2011',Null,'A');
insert into #mytable values ('230389221','12/01/2011',Null,'A');
insert into #mytable values ('187950850','12/01/2011','02/29/2012','A');
insert into #mytable values ('543426566','12/01/2011',Null,'A');
insert into #mytable values ('387473716','01/01/2012','04/30/2012','A');
insert into #mytable values ('365407230','01/01/2012','04/30/2012','A');
insert into #mytable values ('667394514','01/01/2012',Null,'A');
insert into #mytable values ('572158672','01/01/2012',Null,'A');
insert into #mytable values ('952548642','01/01/2012',Null,'A');
insert into #mytable values ('803871629','01/01/2012',Null,'A');
insert into #mytable values ('594658511','01/01/2012',Null,'A');
insert into #mytable values ('430877573','01/01/2012',Null,'A');
GO
--SELECT *
--FROM #mytable
--WHERE EffectiveDate = '20111201'
--ORDER BY ID,EffectiveDate;
--GO
WITH basedata AS (
SELECT
ID,
MIN(EffectiveDate) EffectiveDate,
MAX(ISNULL(TermDate,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1))) AS TermDate,
[Status]
FROM
#mytable
GROUP BY
ID,
[Status]
)
SELECT
bd1.ID
FROM
basedata bd1
WHERE
bd1.EffectiveDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE()) - 6,0)
AND bd1.TermDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1)
AND bd1.[Status] = 'A'
AND NOT EXISTS (SELECT
1
FROM
basedata bd2
WHERE
bd2.ID = bd1.ID
AND bd2.[Status] = 'D'
AND bd2.EffectiveDate between DATEADD(mm,DATEDIFF(mm,0,GETDATE()) - 6,0) AND
DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1));
GO
DROP TABLE #mytable;
GO
June 22, 2012 at 5:31 pm
Thanks Lynn! This looks very close to the results that I need. I'll poke around with it.
June 25, 2012 at 1:33 pm
Hi Lynn,
I poked around with the code, and added a few records. The main one that is most important is the case where I marked it My New Added record. It's another line of dates, with a gap in December of 2011. The code will still pull the ID, even though it has a gap. Any ideas on how to work around this?
create table #mytable(
ID varchar(12),
EffectiveDate datetime,
TermDate datetime,
[Status] varchar(1)
);
GO
insert into #mytable values ('503484823','01/01/2011',Null,'A');
insert into #mytable values ('592879472','01/01/2012',Null,'A');
insert into #mytable values ('929921311','01/01/2012',Null,'A');
insert into #mytable values ('239826234','01/01/2012',Null,'A');
insert into #mytable values ('616546060','12/01/2011',Null,'A');
insert into #mytable values ('978047548','12/01/2011',Null,'A');
insert into #mytable values ('524739641','12/01/2011',Null,'A');
insert into #mytable values ('428547606','12/01/2011','03/31/2012','A');
insert into #mytable values ('428547606','04/01/2012',NULL,'A');-- Added record
insert into #mytable values ('951720863','12/01/2011','03/31/2012','A');
insert into #mytable values ('951720863','04/01/2012','04/30/2011','D');-- Added record
insert into #mytable values ('951720863','05/01/2012',Null,'A');-- Added record
insert into #mytable values ('131333186','12/01/2011',Null,'A');
insert into #mytable values ('260895908','12/01/2011',Null,'A');
insert into #mytable values ('553625622','12/01/2011',Null,'A');
insert into #mytable values ('919168813','12/01/2011','01/31/2012','A');
insert into #mytable values ('557902697','12/01/2011',Null,'A');
insert into #mytable values ('379088668','12/01/2011','02/29/2012','A');
insert into #mytable values ('602081252','12/01/2011',Null,'A');
insert into #mytable values ('716393277','12/01/2011','01/31/2012','A');
insert into #mytable values ('300006158','12/01/2011',Null,'A');
insert into #mytable values ('157499166','01/01/2012','03/31/2012','A');
insert into #mytable values ('249228354','01/01/2012',Null,'A');
insert into #mytable values ('446726968','01/01/2012','04/30/2012','A');
insert into #mytable values ('975883457','01/01/2012','03/31/2012','A');
insert into #mytable values ('847326744','01/01/2012',Null,'A');
insert into #mytable values ('434075176','01/01/2012','03/31/2012','A');
insert into #mytable values ('296098224','01/01/2012','04/30/2012','A');
insert into #mytable values ('370215156','12/01/2011',Null,'A');
insert into #mytable values ('647742333','12/01/2011',Null,'A');
insert into #mytable values ('528683544','12/01/2011','01/31/2012','A');
insert into #mytable values ('717022168','12/01/2011',Null,'A');
insert into #mytable values ('417045232','12/01/2011','02/29/2012','A');
insert into #mytable values ('485375006','12/01/2011',Null,'A');
insert into #mytable values ('963527175','12/01/2011','04/30/2012','A');
insert into #mytable values ('328693654','12/01/2011',Null,'A');
insert into #mytable values ('631187903','12/01/2011','02/29/2012','A');
insert into #mytable values ('903184004','12/01/2011',Null,'A');
insert into #mytable values ('833363486','12/01/2011',Null,'A');
insert into #mytable values ('185935730','12/01/2011',Null,'A');
insert into #mytable values ('479006496','12/01/2011','01/31/2012','A');
insert into #mytable values ('302562202','12/01/2011',Null,'A');
insert into #mytable values ('230389221','12/01/2011',Null,'A');
insert into #mytable values ('187950850','12/01/2011','02/29/2012','A');
insert into #mytable values ('543426566','12/01/2011',Null,'A');
insert into #mytable values ('387473716','01/01/2012','04/30/2012','A');
insert into #mytable values ('365407230','01/01/2012','04/30/2012','A');
insert into #mytable values ('667394514','01/01/2012',Null,'A');
insert into #mytable values ('572158672','01/01/2012',Null,'A');
insert into #mytable values ('952548642','01/01/2012',Null,'A');
insert into #mytable values ('803871629','01/01/2012',Null,'A');
insert into #mytable values ('594658511','01/01/2012',Null,'A');
insert into #mytable values ('594658511','01/01/2011','11/30/2011','A'); -- My New Added record
insert into #mytable values ('430877573','01/01/2011','07/31/2012','A');
GO
--SELECT *
--FROM #mytable
--WHERE EffectiveDate = '20111201'
--ORDER BY ID,EffectiveDate;
--GO
SELECT
ID,
MIN(EffectiveDate) EffectiveDate,
MAX(ISNULL(TermDate,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1))) AS TermDate,
[Status]
FROM
#mytable
GROUP BY
ID,
[Status]
SELECT
bd1.ID
FROM
(
SELECT
ID,
MIN(EffectiveDate) EffectiveDate,
MAX(ISNULL(TermDate,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1))) AS TermDate,
[Status]
FROM
#mytable
GROUP BY
ID,
[Status]
)bd1
WHERE
bd1.EffectiveDate <= Convert(datetime, cast(datepart(year, dateadd(yy, -1, getdate())) as CHAR(4)) + '12' +'01' )
AND bd1.TermDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1)
AND bd1.[Status] = 'A'
AND NOT EXISTS (SELECT
1
FROM
(
SELECT
ID,
MIN(EffectiveDate) EffectiveDate,
MAX(ISNULL(TermDate,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1))) AS TermDate,
[Status]
FROM
#mytable
GROUP BY
ID,
[Status])bd2
WHERE
bd2.ID = bd1.ID
AND bd2.[Status] = 'D'
AND bd2.EffectiveDate between DATEADD(mm,DATEDIFF(mm,0,GETDATE()) - 6,0) AND
DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,-1));
GO
DROP TABLE #mytable;
GO
June 26, 2012 at 5:33 pm
Nevermind 🙂 I worked it out by adding <= for the effective date, and it works perfectly now. Thanks Lynn!
June 26, 2012 at 5:35 pm
Sounds good as I haven't had a chance to even look at it.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply