SQL Update Question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Update Question

Author

Topic

sql_beginner
Starting Member

4 Posts

Posted - 2006-12-27 : 11:04:25

I am trying to update the expiration date based on the effective data for a member.

For example

Member EffectiveDate ExpirationDate
ABC 06/01/2004 12/31/9999
ABC 09/01/2004 12/31/9999

Should be updated to

Member EffectiveDate ExpirationDate
ABC 06/01/2004 8/31/2004
ABC 09/01/2004 12/31/9999

This is the logic I use

update Sample_table
set ExpirationDate = Latest_EffectiveDate
from (select Member, min(EffectiveDate) as Earlier_EffectiveDate,
max(EffectiveDate)-1 as Latest_EffectiveDate
from Sample_table b
where ExpirationDate = '12/31/9999'
Group by Member
having count(*) > 1 ) aa
where Sample_table.Member=aa.Member and
Sample_table.EffectiveDate = aa.Earlier_EffectiveDate

This logic works fine as long as there are only two rows for a member with Expirationdate '12/31/9999'.

But when there is three or more rows the logic does not update properly

For example

Member EffectiveDate ExpirationDate
ABC 06/01/2004 12/31/9999
ABC 09/01/2004 12/31/9999
ABC 11/01/2004 12/31/9999

gets update to (Using the logic above)

Member EffectiveDate ExpirationDate
ABC 06/01/2004 10/31/2004
ABC 09/01/2004 12/31/9999
ABC 11/01/2004 12/31/9999

But the expected result is

Member EffectiveDate ExpirationDate
ABC 06/01/2004 8/31/2004
ABC 09/01/2004 10/31/2004
ABC 11/01/2004 12/31/9999

I am not sure how to tweak the above logic to get the expected result. Any help will be greatly helpful.

Thanks
Ram

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 11:37:30

This should do it


update Sample_table
set ExpirationDate = (select isnull(min(EffectiveDate) - 1, '12/31/9999')
from Sample_table b
where b.ExpirationDate = '12/31/9999' AND b.EffectiveDate > a.EffectiveDate
	and b.Member = a.Member)
from Sample_table a

sql_beginner
Starting Member

4 Posts

Posted - 2006-12-27 : 12:00:30

Thanks snSql..It works fine!!

Ram

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources