February 9, 2010 at 8:55 am
I am a complete beginner here, so please post your response as if I'm a 5 year old...(lol)
For all entries in Charge Code List Price Prices, calculate a new List Price:
If AWP exists, List = AWP x 5
Else if Cost exists, List = Cost x 5
Else do nothing (do NOT replace List price with 0 if no Cost or AWP was found)
Each entry in the CHG_CODE_LIST_PRICE_PRICES table could have several entries. I might have 5000 records, and each record will have an AWP, LIST, etc. I cannot figure how to make the AWP from record 1 update the LIST price for record 1, then continue through each record doing this calculation.
Most of the data in the stored and updated in the CHG_CODE_LIST_PRICE_PRICES table. The COST is stored in another table called CHG_CODE_LIST_PRICES
THANK YOU in advance...:w00t::w00t::w00t:
I have tried...
UPDATE CHG_CODE_LIST_PRICE_PRICES
SET PRICE =
CASE
WHEN (PRICE_TYPE_CODE = 'LIST' AND PRICE = 0)
THEN PRICE * 5 WHEN (PRICE_TYPE_CODE = 'AWP' AND PRICE > 0)
ELSE (PRICE_TYPE_CODE = 'LIST' AND PRICE = 0)
THEN PRODUCT_COST * 5 FROM CHG_CODE_LIST_PRICES WHERE PRODUCT_COST > 0
END
it keeps resulting in
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ELSE'.
I'm really trying to update the table and set a value of AWP * 5 where list = 0 OR COST * 5 from another table where list = 0 OR NOTHING if AWP and LIST are 0
February 9, 2010 at 9:11 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2010 at 9:20 am
Sorry, this was my first ever post. Thank you for the heads up and I corrected my posting. 😀
February 9, 2010 at 9:27 am
No worries, that's why we have the article on posting best practices, for new people to read.
Doctork11 (2/9/2010)
I corrected my posting. 😀
Where? I can't see table design or any sample data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2010 at 9:32 am
It looks like you have a condition after the ELSE. That's not how CASE works, the ELSE is the catch-all for data that doesn't satisfy any of the preceding checks
CASE
WHEN <Condition 1> THEN <Value1>
WHEN <Condition 2> THEN <Value2>
WHEN <Condition 3> THEN <Value3>
ELSE <Value>
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply