Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is there a way to do this with lag in 2012 or without Temp tables in 2008? Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 12:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 363, Visits: 660
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
Post #1521633
Posted Tuesday, December 10, 2013 2:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 363, Visits: 660
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
Post #1521680
Posted Tuesday, December 10, 2013 3:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 12,952, Visits: 32,476
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1521691
Posted Tuesday, December 10, 2013 3:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 363, Visits: 660
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.
Post #1521700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse