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 |
|
sharpblue
Starting Member
2 Posts |
 Posted - 2008-02-03 : 11:35:21
|
hi i am updating a field of one table from field of another table based on certain filters.UPDATE [Item Temp]SET SRP = (SELECT [Price] FROM [Sales Price]WHERE [No] = [Item Temp].No ANDgetdate() between [Start Date] and [End Date]) Here [End Date] is default to '1900/01/01' and i have following types of records in table [Sales Price]--------------------------------------------------No Price [Start D] [Ending Date]--------------------------------------------------
1 10 2008-01-01 blank (but default is '1900/01/01'
2 10 2008-01-01 2008-01-31
2 20 2008-02-01 blank (but default is '1900/01/01'
3 10 2008-01-01 2008-01-31
3 20 2008-02-01 2008-02-28
3 30 2008-03-01 blank (but default is '1900/01/01'Here i need to pick the PRICE based on current date.Since the default value is set to 1900/01/01, BETWEEN function cannot be used. * this database is actually part of ERP application which handles this situation well <using its own technique> , but when i am trying to query directly from database, i am facing the problem. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-03 : 12:53:38
|
| UPDATE [Item Temp]SET SRP = (SELECT [Price] FROM [Sales Price]WHERE [No] = [Item Temp].No AND(getdate() between [Start Date] and [End Date]OR (getdate() > [Start Date] and [End Date]='1900/01/01'))) |
 |
|
|
sharpblue
Starting Member
2 Posts |
Posted - 2008-02-05 : 08:23:42
|
| thanks |
|
|
|
|
|
|
using conditions in WHERE clause of update query