SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a way to do this with lag in 2012 or without Temp tables in 2008?


Is there a way to do this with lag in 2012 or without Temp tables in 2008?

Author
Message
drew.georgopulos
drew.georgopulos
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 705
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 :-D
drew.georgopulos
drew.georgopulos
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 705
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28421 Visits: 39963
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.georgopulos
drew.georgopulos
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 705
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search