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 |
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
 Posted - 2007-06-28 : 11:11:48
|
| I have a table which contains history data. Every business day new data is added to the table from another table. But the data field has to be adjusted everytime since the date is different in the source data.The date is adjusted to -1 day on Tuesday to Friday and -3 days For Monday. I use a case statement to update. But it will scan everythingThis is my sql to do this. If there is a better way not to scan for the other fields which don't need to be changed the ELSE part.UPDATE TABLESET Position_Date= CASE WHEN DATENAME(weekday, Position_Date) IN ('Tuesday', 'Wednesday', 'Thursday', 'Friday') and Position_Date = (dateadd(day,(1),datediff(day,0,getdate())))THEN (dateadd(day,(0),datediff(day,0,getdate()))) WHEN DATENAME(weekday, Position_Date) IN ('Monday') and Position_Date = (dateadd(day,(3),datediff(day,0,getdate()))) THEN (dateadd(day,(0),datediff(day,0,getdate())))ELSE Position_DateENDbut Also there is another problem, if its 3rd July, I will have the data from external source with Position_Date as 5th july. So my update will change it to 4th. I have a holiday table in the database, how do I change it back to 3rd July. And similar thing will happen on Any holiday like thanksgiving and christmas. Ashley Rhodes |
|
|
|
|
|
|
|
Update help in a table with 500,000 rows