Home Forums SQL Server 2008 SQL Server 2008 - General Is there a way to do this with lag in 2012 or without Temp tables in 2008? RE: Is there a way to do this with lag in 2012 or without Temp tables in 2008?

  • December 10, 2013 at 3:08 pm

    #1672459

    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!