January 21, 2005 at 6:47 am
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
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
January 21, 2005 at 3:54 pm
January 21, 2005 at 4:58 pm
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.
January 21, 2005 at 5:25 pm
January 21, 2005 at 5:35 pm
[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
January 21, 2005 at 5:55 pm
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.
January 21, 2005 at 6:02 pm
So, in cases where the gap is > 30 days, it is required to have certain records in the final temp table *twice* ?
January 21, 2005 at 6:10 pm
January 24, 2005 at 4:44 am
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?
January 24, 2005 at 10:30 am
>>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.
January 25, 2005 at 7:26 pm
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
January 27, 2005 at 5:05 pm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply