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.
| Author |
Topic |
|
tinysnail
Starting Member
3 Posts |
 Posted - 2007-08-06 : 13:28:00
|
| i have a table structure like following. i need to update action_date to be same as the closest action_date of action_code='S' for each email address (show as desired column).email_address action_code action_date desired columnxxx@yyy.com S 2007/06/27 2007/06/27xxx@yyy.com B 2007/06/30 2007/06/27xxx@yyy.com R 2007/07/01 2007/06/27xxx@yyy.com S 2007/07/15 2007/07/15xxx@yyy.com R 2007/07/20 2007/07/15yyy@zzz.com S 2007/06/21 2007/06/21yyy@zzz.com B 2007/06/30 2007/06/21is it possible to design a query to do it?Thanks for any help.-tinysnail |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-06 : 13:37:18
|
| [code]Declare @t table(email_address varchar(50), action_code char(1), action_date datetime, desireddate datetime)insert into @t select 'xxx@yyy.com', 'S', '2007/06/27' , null union all select 'xxx@yyy.com', 'B', '2007/06/30' , null union all select 'xxx@yyy.com', 'R', '2007/07/01' , null union all select 'xxx@yyy.com', 'S', '2007/07/15' , null union all select 'xxx@yyy.com', 'R', '2007/07/20' , null union all select 'yyy@zzz.com', 'S', '2007/06/21' , null union all select 'yyy@zzz.com', 'B', '2007/06/30' , null select * , dd = (Select max(Action_date) From @t T2 Where T2.email_address = T.email_Address And T2.action_date <= T.action_date and T2.action_code = 'S' )from @t T[/code]Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
|
|
|
|
|
query help