Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update Field Dates

Author  Topic 

c4e4
Starting Member

16 Posts

Posted - 2007-07-31 : 12:30:31
I work with insurance data. Each member of the plan has an insur_id and a start_date and end_date for that plan. These dates are received from an outside database that needs to be cleaned when I import it into my database. Members may have multiple records because their insur_plan may change. Members who are currently eligible have no end_date - that field is NULL. Members' whose eligibility has ended have an end_date. The problem is sometimes the same member will have records with identical start dates and will have NULL end dates. The only way to pick the most recent record is to look at a field called touch_date - that field contains the date the record was entered into the system. What I would like to do is change all of the NULL end dates to '2079-06-30'. For cases when the member has two records with duplicate start_dates and both end_date are NULL, I would like to enter '2079-06-30' in the field with the most recent touch_date and in the other record I would like the date to be the (start_date - 1).

Some original data:

member_id start_date end_date touch_date insu_plan row_id
--------- ---------- ---------- ---------- --------- ------
11110002 2006-01-01 NULL 2007-06-15 1235 1
11110002 2006-01-01 NULL 2007-07-24 1240 2



I would like the data in the table to look like this after the update:

member_id start_date end_date touch_date insu_plan row_id
-------- ---------- ---------- ---------- --------- ------
11110002 2006-01-01 2005-12-31 2007-06-15 1235 1
11110002 2006-01-01 2079-06-30 2007-07-24 1240 2

The row_id for each record is the primary key.

Any assistance is appreciated.
Thanks !


JLH

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-31 : 12:45:17
HEre's something to start with:

Declare @d table (member_id int, start_date datetime, end_date datetime, touch_date datetime, insu_plan int, row_id int)
insert into @d
select 11110002, '2006-01-01', NULL, '2007-06-15', 1235, 1 union all
select 11110002, '2006-01-01', NULL, '2007-07-24', 1240, 2 union all
select 11110003, '2007-01-01', NULL, '2007-06-15', 1235, 3 union all
select 11110003, '2007-01-01', NULL, '2007-07-24', 1240, 4


select *
from @d d
Where D.row_id in (Select Max(row_Id) From @d D2 Group by Member_id )
And D.end_date is null


update d
set d.end_date = '2079-06-30'
from @d D
Where D.row_id in ((Select Max(row_Id) From @d D2 Group by Member_id ) )
and d.end_date is null

select *
from @d d

update d
set d.end_date = d.start_date - 1
from @d D
Where D.row_id in ((Select Max(row_Id) From @d D2 Where D2.end_date is null Group by Member_id ) )
and d.end_date is null

select *
from @d d


You might have to tweak this a little. You only provided us 2 rows of data which is too little to cover any scenarios.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

c4e4
Starting Member

16 Posts

Posted - 2007-07-31 : 12:47:11
Thanks.
I only provided two rows because there were only two rows like this in my actual database. It is more the exception than the rule, but I have to deal with it.
Thanks - I will give this a try!

JLH
Go to Top of Page
   

- Advertisement -