I need help!

  • Please help.

     

    I have a unique requirement.

     

    I have a table with data as shown below. Please copy it to Query Analyzer for clarity.

     

    MembersName                                   BenefitCategory BenefitStartDate            BenefitEndDate                                        

    WENDY SULLIVAN                  PENSION         2004-06-23 00:00:00.000     2004-07-04 00:00:00.000                               

    WENDY SULLIVAN                  REHAB           2004-07-05 00:00:00.000     NULL                                                  

    PETER CORNELIUS ROODVELDT       PENSION         2004-03-31 00:00:00.000     2004-08-01 00:00:00.000                               

    PETER CORNELIUS ROODVELDT       REHAB           2004-08-02 00:00:00.000     2004-10-11 00:00:00.000                                

    PETER CORNELIUS ROODVELDT       PENSION         2004-10-12 00:00:00.000     2004-10-12 00:00:00.000                               

    PETER CORNELIUS ROODVELDT       REHAB           2004-10-12 00:00:00.000     NULL

     

    This table will ALWAYS have a minimum of 2 records for each member but can potentially have up to 8 records. In the example above, SULLIVAN has 2 records and ROODVELDT has 4 records. THERE ARE 2000 Members in this table.

     

    What I need to do is compare the BenefitEndDate of the first record to the BenefitStartDate of the second record. If its < 30 days, I Insert the second record to a temp table ELSE I Insert both records to the temp table.

     

    For ROODVELDT, I need compare the BenefitEndDate of the first record to the BenefitStartDate of the second record. If its < 30 days, I Insert the second record to a temp table ELSE I Insert both records to the temp table. I then need to compare BenefitEndDate of the second record to the BenefitStartDate of the third record. If its < 30 days, I Insert the third record to a temp table ELSE I Insert both the second and third records to the temp table. I then need to compare the BenefitEndDate of the third record to the BenefitStartDate of the fourth record. If its < 30 days, I Insert the fourth record to a temp table ELSE I Insert both the third and fourth records to the temp table.

     

    I have written a cursor. However, it only caters for up to 4 records for any given member. I HAVE NOT INCLUDED THE < 30 DAYS CALCULATION YET AS I’M TRYING TO GET THE CUSROR WORKING FIRST OR PERHAPS THINK OF A BETTER WAY OF DOING THIS!

     

    The code for the cursor is at the end.

     

    There are 2 problems.

     

    (A) What if there are more than 4 records for a member?

     

    (B) How do you do it for all 2000 members? If exclude the WHERE clause in the SELECT list for the cursor, it only works for the first 4 records in the table.

     

    CAN THIS BE DONE WITH A CURSOR? IF SO, WHAT AM I DOING WRONG IN MY CURSOR CODE BELOW?

     

    OR IS THERE SOME BETTER (LESS COMPLEX) WAY OF DOING THIS?

     

    DECLARE @MembersName1 VARCHAR(50),@BenefitCategory1 VARCHAR(20),

          @BenefitStartDate1 DATETIME, @BenefitEndDate1 DATETIME,

     

         @MembersName2 VARCHAR(50),@BenefitCategory2 VARCHAR(20),

          @BenefitStartDate2 DATETIME, @BenefitEndDate2 DATETIME,

     

          @MembersName3 VARCHAR(50),@BenefitCategory3 VARCHAR(20),

          @BenefitStartDate3 DATETIME, @BenefitEndDate3 DATETIME,

     

          @MembersName4 VARCHAR(50),@BenefitCategory4 VARCHAR(20),

          @BenefitStartDate4 DATETIME, @BenefitEndDate4 DATETIME

     

    DECLARE xyz CURSOR FOR

    SELECT MembersName, BenefitCategory, BenefitStartDate, BenefitEndDate

    FROM #PenReh

    --WHERE MembersName = 'WENDY SULLIVAN'

    --WHERE MembersName = 'PETER CORNELIUS ROODVELDT'

     

    OPEN XYZ

     

    FETCH NEXT FROM XYZ INTO @MembersName1, @BenefitCategory1,

          @BenefitStartDate1, @BenefitEndDate1

          INSERT #FinalResult

          SELECT @MembersName1, @BenefitCategory1,

          @BenefitStartDate1, @BenefitEndDate1

     

    FETCH NEXT FROM XYZ INTO @MembersName2, @BenefitCategory2,

          @BenefitStartDate2, @BenefitEndDate2

          INSERT #FinalResult

          SELECT @MembersName2, @BenefitCategory2,

          @BenefitStartDate2, @BenefitEndDate2

     

    FETCH NEXT FROM XYZ INTO @MembersName3, @BenefitCategory3,

          @BenefitStartDate3, @BenefitEndDate3

          INSERT #FinalResult

          SELECT @MembersName3, @BenefitCategory3,

          @BenefitStartDate3, @BenefitEndDate3

     

    FETCH NEXT FROM XYZ INTO @MembersName4, @BenefitCategory4,

          @BenefitStartDate4, @BenefitEndDate4

          INSERT #FinalResult

          SELECT @MembersName4, @ClaimedReceivedDate4, @BenefitCategory4,

          @BenefitStartDate4, @BenefitEndDate4, @BenefitDecision4,

                @CaseManagersName4

     

    CLOSE XYZ

     

    DEALLOCATE XYZ

     


    Kindest Regards,

  • Is there anyone that can help with the dilemma I'm in? Surely there is someone or all of you that has understood what I need to do!


    Kindest Regards,

  • I would do this using a temporary table and a set-based operation rather than via cursors.

    Create a table to apply a sequence number to the data:

    Declare @Tbl Table (

     Sequence int Identity,

     MembersName varchar(50),

     BenefitCategory varchar(20),

     BenefitStartDate smalldatetime,

     BenefitEndDate smalldatetime,

     DaysToNext smallint

    )

     

    Populate it with the correct sort order, so that ascending sequence represents the correct order of events for each member:

    Insert Into  @Tbl

    (MembersName, BenefitCategory, BenefitStartDate, BenefitEndDate, DaysToNext)

    Select MembersName, BenefitCategory, BenefitStartDate, BenefitEndDate, 31

    From Benefit

    Order by MembersName, BenefitStartDate, IsNull(BenefitEndDate, '01 Jan 2079')

    Correlated update, to set the number of days between end date of 1 record and the start date of next record for that member:

    Update t1

    Set DaysToNext = DateDiff(dd, t1.BenefitEndDate, t2.BenefitStartDate)

    From @Tbl As t1, @Tbl As t2

    Where t1.Sequence = t2.Sequence - 1

    And   t1.MembersName = t2.MembersName

     

    Check the result:

    Select * From @Tbl

     

    Note: If/When you migrate to Yukon/2005, you'll be able to use the RANK() Over functionality instead of the temp table & identity.

     

     

     

     

     

  • PW,

    Your solution works well for the <30 Days calculation but how do I treat it as 1 record when its < 30 Days? Could I please get you or anyone to read the original post from me as maybe I didn't explain it well enough and I may have confused you.


    Kindest Regards,

  • [Edit]

    What would really clarify the problem is if you took the ROODVELDT data and explain exactly what you expect to see in the temp table for that member

  • For ROODVELDT it would be,

    PETER CORNELIUS ROODVELDT      REHAB                2004-08-02 00:00:00.000                                2004-10-11 00:00:00.000                               

    PETER CORNELIUS ROODVELDT      PENSION              2004-10-12 00:00:00.000                                2004-10-12 00:00:00.000                               

    PETER CORNELIUS ROODVELDT      REHAB                2004-10-12 00:00:00.000                                NULL

    The < 30 Days calculation will only be done to see which record needs to be inserted into the temp table. If its <30 Days then we insert the BenefitStartDate record only, if its >30 Days then we insert both the BenefitStartDate & BenefitEndDate records.


    Kindest Regards,

  • So, in cases where the gap is > 30 days, it is required to have certain records in the final temp table *twice* ?

     

  • PW,

    You have just discovered a flaw in the requirement. I will go back to the Business Analyst and inform him of this.

    Thanks so much for understanding what I'm trying to do. I'll find out just what exactly the BA is trying to acieve and get back to this post or begin a new one.


    Kindest Regards,

  • PW,

    I have clarified the rquirement from the BA and I have misunderstood something minor.

    What you have done is exactly what I needed, now I just have to SELECT the NULL & > 30 Days records from the temp table.

    Thanks so much for your help.

    I would like to clarify 1 thing with your solution. I'm having difficulty understanding the "Where t1.Sequence = t2.Sequence - 1" part of the update.

    I understand that you are doing a self join on the 1 table. Could you or someone explain how this part of the update works?


    Kindest Regards,

  • >>Could you or someone explain how this part of the update works?

    It's a self-join that joins adjacent records in the sequence for each member.

    "Where t1.Sequence = t2.Sequence - 1"

    t1 is the table being updated - taking 1 record as an example, if t1 has a sequence of 2, then it will be joined with a record from t2 with a sequence of 3. This allows the number of days from end date to next start date to be calculated.

  • Let me get this right, for the following records below for ROODVELT, What your saying is that the t1 has the forst record with a sequenceid of 1 and the t2 has a sequenceid of 2 and then you are comparing the benefitenddate from the t1 record to the benfitstartdate of the t2 record. Is that correct? I'm a little confused to be honest!

    1 PETER CORNELIUS ROODVELDT       PENSION         2004-03-31 00:00:00.000     2004-08-01 00:00:00.000                               

    2 PETER CORNELIUS ROODVELDT       REHAB           2004-08-02 00:00:00.000     2004-10-11 00:00:00.000                               

    3 PETER CORNELIUS ROODVELDT       PENSION         2004-10-12 00:00:00.000     2004-10-12 00:00:00.000                               

    4 PETER CORNELIUS ROODVELDT       REHAB           2004-10-12 00:00:00.000     NULL


    Kindest Regards,

  • PW,

    You may have missed my question due to the amount of new threads. When you get a chance, could you please answer?


    Kindest Regards,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply