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 |
|
anjali5
Posting Yak Master
121 Posts |
 Posted - 2011-11-04 : 11:08:45
|
| I have two table Timeline table and reports tableTimeline table has this dataTimeline UNIT Date number closing date CNumber 2 lastMessageDate Last Code2010-03-01 00:00:00.000 21623 201001080710 5 2010-04-01 10:47:00.000 201345 2010-02-25 08:27:00.000 2 92010-03-01 00:00:00.000 45679 200907030907 9 2010-04-22 13:55:00.000 2009026 2010-02-25 09:19:00.000 2 10Reports Code UNIT DATE Number UpateNumber 3 45679 2010-04-22 13:55:00.000 9 1215 123456 2010-04-22 13:55:00.000 8 131I want to write something like thisif convert(varchar(10),Date,111) = convert(varchar(10),closing date,111)and convert(varchar(10),Date,111)= TimelineBEGIN UPDATE Timeline set Code = (select max(Code) from reports r where r.unit = timeline.unit and r.date = timeline.date and (r.updateNumber = 121 or r.updateNumber = 101))where r.unit = Timeline.unit and r.date = timeline.date and r.number = timeline.numberelse if closing date is null or closing date > dateadd(hour,23, dateadd(minute ,59, CONVERT(datetime,CONVERT(VARCHAR(23),Timeline, 112) ,112))) -- I have two update statements hereI solved this using cursore, but I was wondering if there is any way, i can eliminate cursors.Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-04 : 12:43:30
|
| if all updates are on same table you can reduce them to single update with case expressions within each column and eliminate cursor altogether------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
stored procedure