locked
format eligibility dates as per the logic RRS feed

  • Question

  • hello all,

    i am working on the logic to format the eligibility records for the healthcare datawarehouse, hope you have idea about Member Eligibility, Claims etc.

    Description,

    1] it is possible for a subscriber to have two or more active plans at the same time. In this case we will pick the plan with the richest benefits for the overlapping period. We still have to keep the other plans if they are covering periods not covered by the plan with the richest benefits

    2]  [MBR_PLCY_SEQ_CD] should be captured based on the different plans from the different subscribers. Richest plan comes first ( In case there is two equal plans we still have to give them different Seq Code)

    i have a eligibility data like above which i need to modify to look like below, in above a plan A6 is richest so is coming with MBR_PLCY_SEQ_ID=1 which i have got so far as 1 for A6 and 2 for S2 by using unpivoting the IND columns(coming in last).

    I just confused how to split the entries for plan S2, like below.

    any help or guide willl be appreciated.

    thanks, 


    Dilip Patil..

    Sunday, December 4, 2016 11:18 AM

Answers

  • Thank you, that was a lot clearer!

    You forgot one thing, though: which version of SQL Server you are using. The query below requires SQL 2012 or later.

    It also requires an auxilliary table of dates for which I first provide the script:

    CREATE TABLE dates (
       thedate   aba_date  NOT NULL,
       CONSTRAINT pk_dates PRIMARY KEY (thedate)
    )
    go
    -- Get a temptable with numbers. This is a cheap, but not 100% reliable.
    -- Whence the query hint and all the checks.
    SELECT TOP 80001 n = IDENTITY(int, 0, 1)
    INTO   #numbers
    FROM   sysobjects o1
    CROSS  JOIN sysobjects o2
    CROSS  JOIN  sysobjects o3
    CROSS  JOIN  sysobjects o4
    OPTION (MAXDOP 1)
    go
    -- Make sure we have unique numbers.
    CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
    go
    -- Verify that table does not have gaps.
    IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
       (SELECT MIN(n) FROM #numbers) = 0 AND
       (SELECT MAX(n) FROM #numbers) = 80000
    BEGIN
       DECLARE @msg varchar(255)

       -- Insert the dates:
       INSERT dates (thedate)
          SELECT dateadd(DAY, n, '19800101')
          FROM   #numbers
          WHERE  dateadd(DAY, n, '19800101') < '21500101'

       SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) + ' rows into #numbers'
       PRINT @msg
    END
    ELSE
       RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
    go
    DROP TABLE #numbers

    Equipped with this table, I first explode the intervals so that I can rank for each day. Then use LAG and LEAD to identify the dates where the rank changes, and then I compact the intervals from this.

    ; WITH explode_dates AS (
       SELECT A.MBR_NBR, A.PLN_ID, d.thedate,
              richness = CASE A.MBR_MED_BNFT_IND WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.DRUG_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.HOSP_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END
       FROM  AMEMBER_ELIG_STG A
       JOIN  sommar_1910..dates d ON d.thedate BETWEEN A.MBR_ELIG_EFFDTE AND A.MBR_TRM_EFFDTE
    ), ranking AS (
       SELECT MBR_NBR, PLN_ID, thedate,
              dense_rank () OVER(PARTITION BY MBR_NBR, thedate ORDER BY richness DESC) AS rank
       FROM   explode_dates
    ), laglead AS (
       SELECT MBR_NBR, PLN_ID, rank, thedate,
              start_interval =
                 CASE WHEN rank <> lag(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                 END,
              end_interval =
                 CASE WHEN rank <> lead(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                  END
        FROM  ranking
    ), compact_intervals AS (
       SELECT MBR_NBR, PLN_ID, rank, start_interval, thedate AS MBR_ELIG_EFFDTE,
              LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
       FROM   laglead
       WHERE  start_interval = 1 OR end_interval = 1
    )
    SELECT MBR_NBR, PLN_ID, rank, MBR_ELIG_EFFDTE, MBR_ELIG_TRM_DATE
    FROM   compact_intervals
    WHERE  start_interval = 1
    


    • Edited by Erland SommarskogMVP Sunday, December 4, 2016 9:25 PM Formatting
    • Proposed as answer by Naomi N Tuesday, December 6, 2016 11:11 AM
    • Marked as answer by Rishabh Patil Wednesday, December 7, 2016 9:18 AM
    Sunday, December 4, 2016 9:23 PM
  • Hi Dilip Patil,

    For your new situation, you could have a look at following demo that’s based on Erland Sommarskog’s code. If your logic is very complex, you could also consider calculating the result by using while loop to compare the “richer plan” with other records in each group of “MBR_NBR”.

    select * from AMEMBER_ELIG_STG
    select * from AMEMBER_ELIG_STG_HSTRY
    
    ;WITH explode_dates AS (
       SELECT A.MBR_NBR, A.PLN_ID, d.thedate,SBSCBR_ID,
              richness = CASE A.MBR_MED_BNFT_IND WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.DRUG_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.HOSP_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END
    		,SBSCBR_IDCnt = dense_rank()over(PARTITION by MBR_NBR order by SBSCBR_ID) + dense_rank()over(PARTITION by MBR_NBR order by SBSCBR_ID desc) -1
       FROM  AMEMBER_ELIG_STG A
       JOIN  dates d ON d.thedate BETWEEN A.MBR_ELIG_EFFDTE AND A.MBR_TRM_EFFDTE
    )
    ,ranking AS (
       SELECT MBR_NBR, PLN_ID, thedate,richness, SBSCBR_IDCnt,SBSCBR_ID,
              dense_rank () OVER(PARTITION BY MBR_NBR, thedate ORDER BY richness DESC) AS rank
       FROM   explode_dates
    )
    ,laglead AS (
       SELECT MBR_NBR, SBSCBR_ID,PLN_ID,SBSCBR_IDCnt, rank, thedate,richness, 
              start_interval =
                 CASE WHEN rank <> lag(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                 END,
              end_interval =
                 CASE WHEN rank <> lead(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                  END
        FROM  ranking
    )
    ,compact_intervals1 as
    (
    	select 
    		MBR_NBR,SBSCBR_ID, PLN_ID, SBSCBR_IDCnt,rank, start_interval, richness
    		,thedate AS MBR_ELIG_EFFDTE
    		,LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
    	from laglead
    	where SBSCBR_IDCnt = 2 and (start_interval = 1 or end_interval = 1)
    )
    ,compact_intervals2 as
    (
    	select 
    		MBR_NBR,SBSCBR_ID, PLN_ID,SBSCBR_IDCnt, rank, start_interval, richness
    		,thedate AS MBR_ELIG_EFFDTE
    		,LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
    	from laglead
    	where SBSCBR_IDCnt = 1 and rank = 1 and (start_interval = 1 or end_interval = 1)
    )
    ,compact_intervals AS (
       Select * from compact_intervals1
       union all
       select * from compact_intervals2
    )
    SELECT *
    FROM   compact_intervals
    WHERE  start_interval = 1

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Naomi N Tuesday, December 6, 2016 11:11 AM
    • Marked as answer by Rishabh Patil Wednesday, December 7, 2016 9:18 AM
    • Edited by Albert_ Zhang Thursday, December 8, 2016 7:53 AM Add "SBSCBR_ID,SBSCBR_IDCnt"
    Tuesday, December 6, 2016 6:04 AM

All replies

  • i am working on the logic to format the eligibility records for the healthcare datawarehouse, hope you have idea about Member Eligibility, Claims etc.

    Maybe someone else has knowledge about your business domain, but I don't.

    Generally, if you want help with crafting queries, it is often a good idea to post:

    1) CREATE TABLE statements for your table(s).
    2) INSERT statements with sample data.
    3) The desired result given the sample.
    4) A short description of the business rules that explains why you want that result.
    5) Which version of SQL Server you are using.

    By posting points 1 and 2 it is possible to copy the scripts into a query window to develop a tested solution. We can't do that from a screen shot.

    And while you have sort of provided point 4, it mainly leaves me in the dark. For instance, it is not clear to why A6 is "richer" than S2. Also, it seems that what you want to achieve a second row for S2, but why is by no means clear to me.

    Sunday, December 4, 2016 4:16 PM
  • table schema for screen 1

    CREATE TABLE [dbo].[AMEMBER_ELIG_STG](
    [MBR_NBR] [varchar](100) NULL,
    [SBSCBR_ID] [varchar](100) NULL,
    [MBR_ELIG_EFFDTE] [date] NULL,
    [MBR_TRM_EFFDTE] [date] NULL,
    [PLN_ID] [char](4) NULL,
    [PLN_ID_DSCRPTN] [varchar](100) NULL,
    [MBR_MED_BNFT_IND] [char](1) NULL,
    [DRUG_BNFT_IND] [char](1) NULL,
    [HOSP_BNFT_IND] [char](1) NULL
    )

    table schema for screen 2

    CREATE TABLE [dbo].[AMEMBER_ELIG_STG_HSTRY](
    [MBR_NBR] [varchar](100) NULL,
    [ELIG_FLAG] [char](1) NULL DEFAULT ((1)),
    [DSNRL_RSN_CD] [varchar](100) NULL,
    [SBSCRBR_NBR] [varchar](100) NULL,
    [MBR_GRP_NBR] [varchar](100) NULL,
    [PLN_ID] [char](4) NULL,
    [MBR_PLCY_SEQ_CD] [smallint] NULL,
    [MBR_ELIG_EFFDTE] [date] NULL,
    [MBR_TRM_EFFDTE] [date] NULL,
    [MBR_MED_BNFT_IND] [char](1) NULL,
    [DRUG_BNFT_IND] [char](1) NULL,
    [HOSP_BNFT_IND] [char](1) NULL
    )

    below is the sample record query respectively

    1]

    INSERT INTO [dbo].[AMEMBER_ELIG_STG]
               ([MBR_NBR]
               ,[SBSCBR_ID]
               ,[MBR_ELIG_EFFDTE]
               ,[MBR_TRM_EFFDTE]
               ,[PLN_ID]
               ,[PLN_ID_DSCRPTN]
               ,[MBR_MED_BNFT_IND]
               ,[DRUG_BNFT_IND]
               ,[HOSP_BNFT_IND])
         VALUES
     ('14957130','9017628605','2016-01-01','2016-08-13','A6','WAGE CLASS I','Y','Y','Y')
    ,('14957130','9013010007','2016-08-01','2016-09-30','S2','FULL COVERAGE','N','Y','Y')

    2]

      

    INSERT INTO [dbo].[AMEMBER_ELIG_STG_HSTRY]
               ([MBR_NBR]
               ,[DSNRL_RSN_CD]
               ,[SBSCRBR_NBR]
               ,[MBR_GRP_NBR]
               ,[PLN_ID]
               ,[MBR_PLCY_SEQ_CD]
               ,[MBR_ELIG_EFFDTE]
               ,[MBR_TRM_EFFDTE]
               ,[MBR_MED_BNFT_IND]
               ,[DRUG_BNFT_IND]
               ,[HOSP_BNFT_IND])

    VALUES ('14957130','','9017628605','','A6','1','2016-01-01','2016-08-13','Y','Y','Y')
    ,('14957130','','9013010007','','S2','2','2016-08-01','2016-08-13','N','Y','Y')
    ,('14957130','','9013010007','','S2','1','2016-08-14','2016-09-30','N','Y','Y')

    actually the data in the second table are the result set to be generate through sql 

    A6 is richer plan because it has 3 'y' in for the columns

               ,[MBR_MED_BNFT_IND]
               ,[DRUG_BNFT_IND]
               ,[HOSP_BNFT_IND]

    and for plan S2 the second row should insert if the 

    [MBR_ELIG_EFFDTE] and [MBR_TRM_EFFDTE] covering the most eligible plan which is A6 then there should be entry for the S2 for the period what A6 has and the MBR_PLCY_SEQ_CD should be one because A6 has 1 and the second entry for the S2 will be excluding the period of the first plan A6 and MBR_PLCY_SEQ_CD will be 2 this time.

    hope this makes sence.

     


    Dilip Patil..

    Sunday, December 4, 2016 6:00 PM
  • Thank you, that was a lot clearer!

    You forgot one thing, though: which version of SQL Server you are using. The query below requires SQL 2012 or later.

    It also requires an auxilliary table of dates for which I first provide the script:

    CREATE TABLE dates (
       thedate   aba_date  NOT NULL,
       CONSTRAINT pk_dates PRIMARY KEY (thedate)
    )
    go
    -- Get a temptable with numbers. This is a cheap, but not 100% reliable.
    -- Whence the query hint and all the checks.
    SELECT TOP 80001 n = IDENTITY(int, 0, 1)
    INTO   #numbers
    FROM   sysobjects o1
    CROSS  JOIN sysobjects o2
    CROSS  JOIN  sysobjects o3
    CROSS  JOIN  sysobjects o4
    OPTION (MAXDOP 1)
    go
    -- Make sure we have unique numbers.
    CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
    go
    -- Verify that table does not have gaps.
    IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
       (SELECT MIN(n) FROM #numbers) = 0 AND
       (SELECT MAX(n) FROM #numbers) = 80000
    BEGIN
       DECLARE @msg varchar(255)

       -- Insert the dates:
       INSERT dates (thedate)
          SELECT dateadd(DAY, n, '19800101')
          FROM   #numbers
          WHERE  dateadd(DAY, n, '19800101') < '21500101'

       SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) + ' rows into #numbers'
       PRINT @msg
    END
    ELSE
       RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
    go
    DROP TABLE #numbers

    Equipped with this table, I first explode the intervals so that I can rank for each day. Then use LAG and LEAD to identify the dates where the rank changes, and then I compact the intervals from this.

    ; WITH explode_dates AS (
       SELECT A.MBR_NBR, A.PLN_ID, d.thedate,
              richness = CASE A.MBR_MED_BNFT_IND WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.DRUG_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.HOSP_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END
       FROM  AMEMBER_ELIG_STG A
       JOIN  sommar_1910..dates d ON d.thedate BETWEEN A.MBR_ELIG_EFFDTE AND A.MBR_TRM_EFFDTE
    ), ranking AS (
       SELECT MBR_NBR, PLN_ID, thedate,
              dense_rank () OVER(PARTITION BY MBR_NBR, thedate ORDER BY richness DESC) AS rank
       FROM   explode_dates
    ), laglead AS (
       SELECT MBR_NBR, PLN_ID, rank, thedate,
              start_interval =
                 CASE WHEN rank <> lag(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                 END,
              end_interval =
                 CASE WHEN rank <> lead(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                  END
        FROM  ranking
    ), compact_intervals AS (
       SELECT MBR_NBR, PLN_ID, rank, start_interval, thedate AS MBR_ELIG_EFFDTE,
              LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
       FROM   laglead
       WHERE  start_interval = 1 OR end_interval = 1
    )
    SELECT MBR_NBR, PLN_ID, rank, MBR_ELIG_EFFDTE, MBR_ELIG_TRM_DATE
    FROM   compact_intervals
    WHERE  start_interval = 1
    


    • Edited by Erland SommarskogMVP Sunday, December 4, 2016 9:25 PM Formatting
    • Proposed as answer by Naomi N Tuesday, December 6, 2016 11:11 AM
    • Marked as answer by Rishabh Patil Wednesday, December 7, 2016 9:18 AM
    Sunday, December 4, 2016 9:23 PM
  • Thanks Erland,

    This works perfect for the given example.

    actually i want to have the result as above when SBSCBR_ID is different.

    let say if i have some other member record in the AMEMBER_ELIG_STG table who have same SBSCBR_ID for the two plans i should not insert two rows for the plan which covered the other plan.

    let me explain ..let me insert new member eigibility in AMEMBER_ELIG_STG

     

    INSERT INTO [dbo].[AMEMBER_ELIG_STG]
               ([MBR_NBR]
               ,[SBSCBR_ID]
               ,[MBR_ELIG_EFFDTE]
               ,[MBR_TRM_EFFDTE]
               ,[PLN_ID]
               ,[PLN_ID_DSCRPTN]
               ,[MBR_MED_BNFT_IND]
               ,[DRUG_BNFT_IND]
               ,[HOSP_BNFT_IND])
         VALUES
     ('15678722','9011002666','2016-01-01','2016-07-30','A9','WAGE CLASS II','N','N','Y')
    ,('15678722','9011002666','2016-02-14','2016-09-30','A6','FULL COVERAGE','Y','Y','Y')

    the result for this member record should be look like below..

    INSERT INTO [dbo].[AMEMBER_ELIG_STG_HSTRY]
               ([MBR_NBR]
               ,[DSNRL_RSN_CD]
               ,[SBSCRBR_NBR]
               ,[MBR_GRP_NBR]
               ,[PLN_ID]
               ,[MBR_PLCY_SEQ_CD]
               ,[MBR_ELIG_EFFDTE]
               ,[MBR_TRM_EFFDTE]
               ,[MBR_MED_BNFT_IND]
               ,[DRUG_BNFT_IND]
               ,[HOSP_BNFT_IND])

    VALUES ('15678722','','9011002666','','A9','1','2016-01-01','2016-02-13','N','N','Y')
    ,('15678722','','9011002666','','A6','1','2016-02-14','2016-09-30','Y','Y','Y')

    in this the SBSCRBR_NBR is same for all the plans, and by consideraing term date of the plan A9 which is originally 07/30/2016 in the first table and that got termed as we have the plan A6 is active with latest term date as 09/30/2016 in the first table.

    So we need to term it before the active plan effective date.

    thanks for your valuable time :)



    Dilip Patil..

    Monday, December 5, 2016 4:02 PM
  • Ehum, that made the problem quite a bit more difficult it seems to me.

    What happens if the same MBR_NR has a couple of subscriptions with various plans? It would be useful with more example before I dive too deep into this (which I don't have time for tonight anyway).

    Monday, December 5, 2016 11:06 PM
  • Hi Dilip Patil,

    For your new situation, you could have a look at following demo that’s based on Erland Sommarskog’s code. If your logic is very complex, you could also consider calculating the result by using while loop to compare the “richer plan” with other records in each group of “MBR_NBR”.

    select * from AMEMBER_ELIG_STG
    select * from AMEMBER_ELIG_STG_HSTRY
    
    ;WITH explode_dates AS (
       SELECT A.MBR_NBR, A.PLN_ID, d.thedate,SBSCBR_ID,
              richness = CASE A.MBR_MED_BNFT_IND WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.DRUG_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.HOSP_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END
    		,SBSCBR_IDCnt = dense_rank()over(PARTITION by MBR_NBR order by SBSCBR_ID) + dense_rank()over(PARTITION by MBR_NBR order by SBSCBR_ID desc) -1
       FROM  AMEMBER_ELIG_STG A
       JOIN  dates d ON d.thedate BETWEEN A.MBR_ELIG_EFFDTE AND A.MBR_TRM_EFFDTE
    )
    ,ranking AS (
       SELECT MBR_NBR, PLN_ID, thedate,richness, SBSCBR_IDCnt,SBSCBR_ID,
              dense_rank () OVER(PARTITION BY MBR_NBR, thedate ORDER BY richness DESC) AS rank
       FROM   explode_dates
    )
    ,laglead AS (
       SELECT MBR_NBR, SBSCBR_ID,PLN_ID,SBSCBR_IDCnt, rank, thedate,richness, 
              start_interval =
                 CASE WHEN rank <> lag(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                 END,
              end_interval =
                 CASE WHEN rank <> lead(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                  END
        FROM  ranking
    )
    ,compact_intervals1 as
    (
    	select 
    		MBR_NBR,SBSCBR_ID, PLN_ID, SBSCBR_IDCnt,rank, start_interval, richness
    		,thedate AS MBR_ELIG_EFFDTE
    		,LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
    	from laglead
    	where SBSCBR_IDCnt = 2 and (start_interval = 1 or end_interval = 1)
    )
    ,compact_intervals2 as
    (
    	select 
    		MBR_NBR,SBSCBR_ID, PLN_ID,SBSCBR_IDCnt, rank, start_interval, richness
    		,thedate AS MBR_ELIG_EFFDTE
    		,LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
    	from laglead
    	where SBSCBR_IDCnt = 1 and rank = 1 and (start_interval = 1 or end_interval = 1)
    )
    ,compact_intervals AS (
       Select * from compact_intervals1
       union all
       select * from compact_intervals2
    )
    SELECT *
    FROM   compact_intervals
    WHERE  start_interval = 1

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Naomi N Tuesday, December 6, 2016 11:11 AM
    • Marked as answer by Rishabh Patil Wednesday, December 7, 2016 9:18 AM
    • Edited by Albert_ Zhang Thursday, December 8, 2016 7:53 AM Add "SBSCBR_ID,SBSCBR_IDCnt"
    Tuesday, December 6, 2016 6:04 AM
  • I realise that I'm clutching at straws, but maybe you should check with your business people that you have the condition on subscriber id correctly. Because it would be simpler if the split should be done only if subscriber id is the same...

    (But, alas, it is rarely that programmers can enforce changes to the business requirements on the grounds that it's simpler to implement.)

    Tuesday, December 6, 2016 8:14 AM
  • Thanks Erland,

    If MBR_NBR has multiple subscription with various plans, we have to deal with it like you did for MBR_NBR 14957130 earlier.

    I just want to split the dates if the member has multiple subscription, as you did for first scenario.

    I think i can take the count of the subscription for the member in the middle of cte and check if the the count is <=1

    here i did smaller change in the compact_intervals  CTE

    , compact_intervals AS 
    (
       SELECT MBR_NBR,SBSCBR_ID, PLN_ID, MBR_PLCY_SEQ_CD, start_interval, thedate AS MBR_ELIG_EFFDTE,
              LEAD(thedate) OVER (PARTITION BY MBR_NBR,SBSCBR_ID,PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE,A
       FROM   laglead
       WHERE  (start_interval = 1 OR end_interval = 1)
       AND MBR_PLCY_SEQ_CD=(CASE WHEN A=1 THEN 1 ELSE MBR_PLCY_SEQ_CD END)
    )

      and this is giving me the expected result for these two, i need to check this for other members before implementing this.

     


    Dilip Patil..

    Tuesday, December 6, 2016 9:30 AM
  • Thanks Albert,

    your solution is very close to my requirement, will let you know once i am done with the implementation.

    thanks again,

     


    Dilip Patil..

    Tuesday, December 6, 2016 11:04 AM
  • Erland, Just one more favour please

    if you can check for the case like for a member 15678722

     the insert defination for the above is

    INSERT INTO [dbo].[AMEMBER_ELIG_STG_HSTRY]
               ([MBR_NBR]
               ,[DSNRL_RSN_CD]
               ,[SBSCRBR_NBR]
               ,[MBR_GRP_NBR]
               ,[PLN_ID]
               ,[MBR_PLCY_SEQ_CD]
               ,[MBR_ELIG_EFFDTE]
               ,[MBR_TRM_EFFDTE]
               ,[MBR_MED_BNFT_IND]
               ,[DRUG_BNFT_IND]
               ,[HOSP_BNFT_IND])

    VALUES ('14957130','','9017628605','','A6','1','2016-01-01','2016-08-13','Y','Y','Y')
    ,('14957130','','9013010007','','S2','2','2016-08-01','2016-08-13','N','Y','Y')
    ,('14957130','','9013010007','','S2','1','2016-08-14','2016-09-30','N','Y','Y')

    I want to update a Dest_flag column for the the records which 

    A member should have active plan in the history table which are flagged as 0

    Plan A9 overlaps A6 for the term period so should flagged as 1.

    Thanks, 

          


    Dilip Patil..

    Thursday, December 8, 2016 1:52 AM
  • Hi Dilip Patil,

    I have changed my code in my above reply and you could have a look at it. In my updated demo, I add a new column “SBSCBR_IDCnt” indicate whether the SBSCBR_IDs are the same or not. Finally, calculating them respectively and using “union all” to get the result.

    >> I want to update a Dest_flag column for the the records… Plan A9 overlaps A6 for the term period so should flagged as 1.

    You could test my above updated code and add this following code to get the column “Dest_flag” in the final query.

    case when richness =3 then 0 else 1 end as Dest_flag

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 8, 2016 8:06 AM
  • Thanks Albert,

    Actually your query updating both rows for plan A, look in the my above  screen, highlighted yellow with Dest_flag =1 
    that should only have one.

     


    Dilip Patil..

    Thursday, December 8, 2016 8:27 AM
  • Hi Dilip Patil

    >> highlighted yellow with Dest_flag =1  that should only have one.

    I’m sorry I’m not sure how do you calculate the “Dest_flag”.

    Do you want to get the “Dest_flag” directly based on the result of Erland Sommarskog’s code or do you want to change your initial expect result since your initial expected result doesn’t has two “A9” for “15678722”?

    If it’s the former, you could have a try to use following code.

    ,TMP AS
    (
    	SELECT MBR_NBR, PLN_ID, rank, MBR_ELIG_EFFDTE, MBR_ELIG_TRM_DATE
    	FROM   compact_intervals
    	WHERE  start_interval = 1
    )
    ,CTE AS
    (
    	select *, COUNT(1) over (partition by MBR_NBR,PLN_ID) PLN_IDCNT, COUNT(1) over (partition by MBR_NBR, MBR_ELIG_EFFDTE) MBR_ELIG_EFFDTECNT
    	from tmp t
    )
    SELECT *, CASE WHEN PLN_IDCNT= 2 AND MBR_ELIG_EFFDTECNT = 2 THEN 1 ELSE 0 END AS Dest_flag
    FROM CTE

    If it’s the later, you’d post your new issue in a new thread, so that we could better help you.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 8, 2016 9:44 AM
  • I'm not sure that I understand the new requirement. Possibly the logic is that DEST_FLAG should be 1, if the ranking had been > 1, if it wasn't for that condition on subscriber id. In such case you could have two ranking columns one you modify and one you do not.

    Belated, here is a solution that addresses the issue with the subcriber id in a different way. Alas, it requires materialising the CTE #laglead where we have one row per day. But it should cover all sorts of combinations, I think.

    ; WITH explode_dates AS (
       SELECT A.MBR_NBR, A.SBSCBR_ID, A.PLN_ID, d.thedate,
              richness = CASE A.MBR_MED_BNFT_IND WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.DRUG_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END +
                         CASE A.HOSP_BNFT_IND    WHEN 'Y' THEN 1 ELSE 0 END
       FROM  AMEMBER_ELIG_STG A
       JOIN  sommar_1910..dates d ON d.thedate BETWEEN A.MBR_ELIG_EFFDTE AND A.MBR_TRM_EFFDTE
    ), ranking AS (
       SELECT MBR_NBR, SBSCBR_ID, PLN_ID, thedate,
              dense_rank () OVER(PARTITION BY MBR_NBR, thedate ORDER BY richness DESC) AS rank
       FROM   explode_dates
    )
       SELECT MBR_NBR, SBSCBR_ID, PLN_ID, rank, thedate,
              start_interval =
                 CASE WHEN rank <> lag(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                 END,
              end_interval =
                 CASE WHEN rank <> lead(rank, 1, 0) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate)
                      THEN 1
                      ELSE 0
                  END
        INTO  #laglead
        FROM  ranking

    UPDATE #laglead
    SET    rank = (SELECT MIN(b.rank)
                   FROM   #laglead b
                   WHERE  a.MBR_NBR = b.MBR_NBR
                     AND  a.SBSCBR_ID = b.SBSCBR_ID
                     AND  a.PLN_ID <> b.PLN_ID
                     AND  a.thedate = b.thedate)
    FROM   #laglead a
    WHERE  EXISTS (SELECT *
                   FROM   #laglead b
                   WHERE  a.MBR_NBR = b.MBR_NBR
                     AND  a.SBSCBR_ID = b.SBSCBR_ID
                     AND  a.PLN_ID <> b.PLN_ID
                     AND  a.rank    > b.rank
                     AND  a.thedate = b.thedate)                 
    ; WITH compact_intervals AS (
       SELECT MBR_NBR, SBSCBR_ID, PLN_ID, rank, start_interval, thedate AS MBR_ELIG_EFFDTE,
              LEAD(thedate) OVER (PARTITION BY MBR_NBR, PLN_ID ORDER BY thedate) AS MBR_ELIG_TRM_DATE
       FROM   #laglead
       WHERE  start_interval = 1 OR end_interval = 1
    )
    SELECT MBR_NBR, SBSCBR_ID, PLN_ID, rank, MBR_ELIG_EFFDTE, MBR_ELIG_TRM_DATE
    FROM   compact_intervals
    WHERE  start_interval = 1

    Thursday, December 8, 2016 11:06 PM