December 10, 2013 at 12:07 pm
Jeff Moden was kind enough to show me how to use a tally table to fill in intervening months when I only had a start date and end date. It worked great until I noticed an abnormality in the data caused by the preparer using a flag as a marker date in the columns of 2099-12-31, and at that, it seems to me to be inconsistent too. Because the way the data is delivered, the start and end dates for coverage can be spread among three different columns, benefit start date, benefit end date and display end date, why, I don't know, but its beyond my control.
because of the business rules, it cannot be assumed that the outlier dates, 2099-12-31, can simply be converted to this year's end date, 2013-12-31 because sometimes coverage goes into 2014.
So here's my story...
any [benefitenddate] of 2099-12-31 is only a marker injected by the preparer.
sometimes, coverage details change (premium goes up or member marries or divorces or has a child) but the order of rows in the census is not continuous, so for rows 7, 8 and 9, the calculation needs to be 8 months at 1942 and three months at 1929, so the real benefit end date is in benefit end date for row 7, but in display end date for rows 8 and 9!
it cannot be assumed that if benefit end date is 2099-12-31 that it can be replaced with 2013-12-31 because some benefits actually run into 2014, and are ruled by display end date as shown in rownum 11 for member 158, but in this case, the 'real' benefit end date is in display end date for the first row, however, since the object of the game is calculate enrollment and assessment for 2013, I need to count only the months from july to december in this case.
Can this be done with lag function somehow, or in 2008 without a temp table?
CREATE TABLE [dbo].[ees](
[MemberNumber] [varchar](9) NULL,
[Carrier] [nvarchar](30) NULL,
[PlanName] [nvarchar](30) NULL,
[PlanType] [nvarchar](30) NULL,
[TierName] [nvarchar](30) NULL,
[Premium] [money] NULL,
[BenefitStartDate] [date] NULL,
[BenefitEndDate] [date] NULL,
[DisplayEndDate] [date] NULL
) ON [PRIMARY]
GO
insert ees values [151],[UHC],[Choice Plus ],[Employee Only],[1128.00],[2013-01-01],[2099-12-31],[2013-12-31],[1])
insert ees values [151],[Delta],[Dental B ],[Employee Only],[48.00],[2013-01-01],[2099-12-31],[2013-12-31],[2])
insert ees values [154],[Prescription],[Drug 2 ],[Employee + Spouse],[1154.00],[2013-01-01],[2099-12-31],[2013-12-31],[3])
insert ees values [154],[UHC],[Choice ],[Employee + Spouse],[1556.00],[2013-01-01],[2099-12-31],[2013-12-31],[4])
insert ees values [154],[Delta],[Dental 1 ],[Employee + Spouse],[73.00],[2013-01-01],[2099-12-31],[2013-12-31],[5])
insert ees values [153],[UHC],[Choice Plus ],[Employee + Spouse],[1673.00],[2013-01-01],[2099-12-31],[2013-12-31],[6])
insert ees values [157],[Aetna],[Partriot V ],[Family],[1942.00],[2013-01-01],[2013-08-31],[2013-12-31],[7])
insert ees values [157],[Delta],[Dental ],[Family],[131.00],[2013-01-01],[2099-12-31],[2013-12-31],[8])
insert ees values [157],[Aetna],[Patriot V ],[Family],[1929.00],[2013-09-01],[2099-12-31],[2013-12-31],[9])
insert ees values [158],[Aetna],[Open Access ],[Employee + Spouse],[1565.00],[2013-01-01],[2013-06-30],[2013-12-31],[10])
insert ees values [158],[Aetna],[Open Access ],[Employee + Spouse],[1676.00],[2013-07-01],[2099-12-31],[2014-06-30],[11])
insert ees values [149],[Aetna],[Open Access ],[Employee + Spouse],[1924.00],[2013-01-01],[2013-08-31],[2013-12-31],[12])
insert ees values [149],[Aetna],[Open Access ],[Employee + Spouse],[1924.00],[2013-09-01],[2099-12-31],[2013-12-31],[13])
insert ees values [149],[Prescription],[Drug ],[Employee + Spouse],[760.00],[2013-01-01],[2013-08-31],[2013-12-31],[14])
insert ees values [149],[Prescription],[Drug ],[Employee + Spouse],[760.00],[2013-09-01],[2099-12-31],[2013-12-31],[15])
insert ees values [154],[Delta],[Dental ],[Employee + Spouse],[86.00],[2013-01-01],[2099-12-31],[2013-12-31],[16])
thanks for sharing my pain 😀
December 10, 2013 at 2:45 pm
please forgive me, it was too late to edit but I just noticed that I forgot to include rownum int as the last column in the create table statement
December 10, 2013 at 3:08 pm
it took me a while just to massage your sample data into a usable format.
i ran out of gas just getting this data consumable, maybe someone else can run teh ball with it
IF OBJECT_ID('[dbo].[ees]') IS NOT NULL
DROP TABLE [dbo].[ees]
GO
CREATE TABLE [dbo].[ees] (
[MemberNumber] VARCHAR(9) NULL,
[Carrier] NVARCHAR(30) NULL,
[PlanName] NVARCHAR(30) NULL,
[PlanType] NVARCHAR(30) NULL,
[TierName] NVARCHAR(30) NULL, --<Removed /assumed bad data
[Premium] MONEY NULL,
[BenefitStartDate] DATE NULL,
[BenefitEndDate] DATE NULL,
[DisplayEndDate] DATE NULL,
[RowNumber] INT NULL)
insert ees([MemberNumber],[Carrier],[PlanName],[PlanType],[Premium],[BenefitStartDate],[BenefitEndDate],[DisplayEndDate],[RowNumber])
SELECT '151','UHC','Choice Plus ','Employee Only',1128.00,'2013-01-01','2099-12-31','2013-12-31','1'
UNION ALL SELECT'151','Delta','Dental B ','Employee Only',48.00,'2013-01-01','2099-12-31','2013-12-31','2'
UNION ALL SELECT'154','Prescription','Drug 2 ','Employee + Spouse',1154.00,'2013-01-01','2099-12-31','2013-12-31','3'
UNION ALL SELECT'154','UHC','Choice ','Employee + Spouse',1556.00,'2013-01-01','2099-12-31','2013-12-31','4'
UNION ALL SELECT'154','Delta','Dental 1 ','Employee + Spouse',73.00,'2013-01-01','2099-12-31','2013-12-31','5'
UNION ALL SELECT'153','UHC','Choice Plus ','Employee + Spouse',1673.00,'2013-01-01','2099-12-31','2013-12-31','6'
UNION ALL SELECT'157','Aetna','Partriot V ','Family',1942.00,'2013-01-01','2013-08-31','2013-12-31','7'
UNION ALL SELECT'157','Delta','Dental ','Family',131.00,'2013-01-01','2099-12-31','2013-12-31','8'
UNION ALL SELECT'157','Aetna','Patriot V ','Family',1929.00,'2013-09-01','2099-12-31','2013-12-31','9'
UNION ALL SELECT'158','Aetna','Open Access ','Employee + Spouse',1565.00,'2013-01-01','2013-06-30','2013-12-31','10'
UNION ALL SELECT'158','Aetna','Open Access ','Employee + Spouse',1676.00,'2013-07-01','2099-12-31','2014-06-30','11'
UNION ALL SELECT'149','Aetna','Open Access ','Employee + Spouse',1924.00,'2013-01-01','2013-08-31','2013-12-31','12'
UNION ALL SELECT'149','Aetna','Open Access ','Employee + Spouse',1924.00,'2013-09-01','2099-12-31','2013-12-31','13'
UNION ALL SELECT'149','Prescription','Drug ','Employee + Spouse',760.00,'2013-01-01','2013-08-31','2013-12-31','14'
UNION ALL SELECT'149','Prescription','Drug ','Employee + Spouse',760.00,'2013-09-01','2099-12-31','2013-12-31','15'
UNION ALL SELECT'154','Delta','Dental ','Employee + Spouse',86.00,'2013-01-01','2099-12-31','2013-12-31','16'
Lowell
December 10, 2013 at 3:27 pm
Thank you for your help...going over your correction, I realized that i could make the problem smaller by putting an index over carrier and benefit start date then attack each carrier separately, so i went through three iterations to get to where i think it needs to be
select memberid, carrier, planname, plantype,tiername, premium,benefitstartdate,
case when benefitenddate = '20991231' then displayenddate else benefitenddate end newenddate, displayenddate
from ees
where '20131231'>=[BenefitStartDate]
and not (carrier in ( 'Elect', 'Waive'))
order by 2
--but this left 2014 in the end date, so i tried to nest the case statement
select memberid, carrier, planname, plantype, tiername, premium, benefitstartdate,
case when benefitenddate = '20991231' then
case when year(displayenddate)='2014' then '20131231' end else benefitenddate end, displayenddate
from ees
where '20131231'>=[BenefitStartDate]
and not (carrier in ( 'Elect', 'Waive'))
order by 2
but that left nulls in the end date, so i switched the 'stuffing' for the case statement to
select memberid , carrier ,planname, plantype, tiername, premium, benefitstartdate,
case when benefitenddate = '20991231' then '20131231' else benefitenddate end, displayenddate
from ees
where '20131231'>=[BenefitStartDate]
and not (carrier in ( 'Elect', 'Waive'))
order by 2
and that left the result in the state i needed it, substituting 12/31/2013 where there was 12/31/2099 or the real benefit end date.
Thank you for the push.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply