none
How to compare value of Column A with value of Column B for all rows having the same value for Column C RRS feed

  • Question

  • Hi all,

    I am using SQL Server 2008 R2 and have a temp table with 3 columns: PersonID, EffectiveDate, and ExpirationDate.   For each PersonID there are multiple rows with different values for the EffectiveDate and ExpirationDate columns   I need one row  for each person having an effective date more than one day after the expiration date of an earlier time period for that person (the business logic behind this is to find all customers with enrollment gaps). 

    Example data:

    PersonID            Eff Date            Exp Date

    1                       1/1/1900          1/1/2001

    1                       1/1/1990          1/1/2005

    1                       7/1/2005          12/31/2013

    1                       1/1/2014           12/31/2020

    2                       1/1/1900          9/1/2013

    2                       9/2/2013         12/31/2015

    Desired result set:

    PersonID         Eff Date          Exp Date

    1                    7/1/2005        12/31/2015

    Please note the PersonID values are not consecutive and there can be 2 or more rows in the table for each Person ID.

    Any ideas how to do this?  I have thought of comparing curr.EffDate > DATEADD(d, 1, prev.ExpDate) in some type of cursor or loop but can't seem to find a complete solution.

    Thanks.


    Scott Olander

    Tuesday, September 3, 2013 8:20 PM

Answers

  • try,

    create table #temp (PersonID     int,   EffDate date, ExpDate date)
    
    insert into #temp values(1,'1/1/1900','1/1/2001'),(1,'1/1/1990','1/1/2005'),(1,'7/1/2005','12/31/2013')
    ,(1,'1/1/2014',' 12/31/2020'),(2,'1/1/1900','9/1/2013'),(2,'9/2/2013','12/31/2015')
    
    select * from #temp;
    
    with cte
    as
    ( select PersonID,EffDate,ExpDate,ROW_NUMBER() over(partition by personid order by effdate) rno from #temp
    )
    ,cte1 as 
    (select personid,EffDate,ExpDate,rno, 0 as 'problem row'  from cte where rno=1
    union all 
    select A.personid,A.EffDate,A.ExpDate,A.rno,  case when DATEDIFF(DD,B.expdate ,A.EffDate) >1 then 1 else 0 end
     from cte A inner join cte1 B
    ON A.rno=B.rno+1 and A.PersonID=B.PersonID
    )
    
    select * from cte1  where [problem row]=1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Scott Ola Wednesday, September 4, 2013 8:35 PM
    Wednesday, September 4, 2013 1:14 AM

All replies

  • Scott,

    I do not see any tuple for [PersonID] = 1 and [Exp Date] = '12/31/2015'. Are you sure that is the expected result and if so why?


    AMB

    Some guidelines for posting questions...

    Tuesday, September 3, 2013 8:38 PM
    Moderator
  • Scott, I'm tryng to write something for you but I didn't understand your request. You want just one row back each id or , eventually, more than one?


    • Edited by DIEGOCTN Tuesday, September 3, 2013 8:55 PM mistake
    Tuesday, September 3, 2013 8:51 PM
  • Hi Hunchback,

    I apologize; that was a typo. the desired result set is as follows:

    PersonID         Eff Date          Exp Date

    1                    7/1/2005        12/31/2013

    This should be the result because this customer had a gap in their enrollment (i.e. DATEDDIFF(D, '01-01-2005', '07-01-2005') > 1)).


    Scott Olander

    Tuesday, September 3, 2013 11:11 PM
  • Hi DIEGOCTN,

    Thanks for your help.  Yes, one row for each PersonID will suffice.  Basically the business requirement is to find all customers (represented here by PersonID) who have had a gap in their enrollment.


    Scott Olander

    Tuesday, September 3, 2013 11:13 PM
  • try,

    create table #temp (PersonID     int,   EffDate date, ExpDate date)
    
    insert into #temp values(1,'1/1/1900','1/1/2001'),(1,'1/1/1990','1/1/2005'),(1,'7/1/2005','12/31/2013')
    ,(1,'1/1/2014',' 12/31/2020'),(2,'1/1/1900','9/1/2013'),(2,'9/2/2013','12/31/2015')
    
    select * from #temp;
    
    with cte
    as
    ( select PersonID,EffDate,ExpDate,ROW_NUMBER() over(partition by personid order by effdate) rno from #temp
    )
    ,cte1 as 
    (select personid,EffDate,ExpDate,rno, 0 as 'problem row'  from cte where rno=1
    union all 
    select A.personid,A.EffDate,A.ExpDate,A.rno,  case when DATEDIFF(DD,B.expdate ,A.EffDate) >1 then 1 else 0 end
     from cte A inner join cte1 B
    ON A.rno=B.rno+1 and A.PersonID=B.PersonID
    )
    
    select * from cte1  where [problem row]=1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Scott Ola Wednesday, September 4, 2013 8:35 PM
    Wednesday, September 4, 2013 1:14 AM
  • This was helpful, but when applied to a table with 275,000 rows the run time was very bad.  I did show your code to a colleague and we managed to come up with another solution using the ROWNUMBER() OVER PARTION BY... portion of your query to add a sequence number column to the temp table.  I will post it here in case it is of use to anyone else.  All the same it was nice to get some exposure to CTEs as I had never had occasion to use one before.

    Thanks for the help.

    CREATE TABLE #PersonTemp

    (PersonID VARCHAR(25)

    ,EffectiveDate DATE

    ,ExpirationDate DATE

    ,SequenceNumber INT)

    INSERT INTO #PersonTemp

    SELECT PersonID, EffectiveDate, ExpirationDate, ROW_NUMBER() over(partition by MemberID order by EffectiveDate)

    FROM MemberEnrollment

    WHERE PersonID IN (SELECT PersonID

                               FROM MemberEnrollment

                               GROUP BY PersonID

                               HAVING COUNT(*) > 1)

                               AND ExpirationDate <> '9999-12-31'

    --Customers with break in coverage

    SELECT M1.PersonID

            ,EffectiveDate1 = P1.EffectiveDate

            ,ExpirationDate1 = P1.ExpirationDate

            ,EffectiveDate2 = P2.EffectiveDate

            ,ExpirationDate2 = P2.ExpirationDate

    FROM #PersonTemp P1

      INNER JOIN (SELECT *

                        FROM #PersonTemp) P2

          ON P1.PersonID = P2.PersonID

          AND P1.ExpirationDate <> '12-31-9999'

          AND P2.EffectiveDate > DATEADD(D, 1, P1.ExpirationDate)

          AND P1.EffectiveDate < P2.EffectiveDate

          AND P1.ExpirationDate < P2.ExpirationDate

          AND P1.ExpirationDate <> P1.EffectiveDate

          AND P2.ExpirationDate <> P2.EffectiveDate

         


    Scott Olander

    Wednesday, September 4, 2013 8:34 PM