Requesting SQL assistance?

Asked by At

Does anyone know the code to achieve this?

Implement a new price policy on the product table based on the color of the item

If white, increase the price by 8%.
If yellow, reduce the price by 7.5%.
If black, increase the price by 17.2%.
If multi, silver, silver/black or blue take the square root of the price and double the value.
The column should be called "New price".

For each item, also calculate the commission as 37.5% of the newly computed list price.

This is what I've tried so far:

Select
  [ProductID],[Name],[Color],
  CASE
  When [Color] = 'WHITE' Then ([ListPrice]*1.08)
  WHEN [Color] = 'YELLOW' Then  ([ListPrice]*0.075)
  WHEN [Color] = 'BLACK' Then  ([ListPrice]*1.172)
  WHEN [Color] IN ('SILVER','SILVER/BLACK','BLUE') THEN SQRT([ListPrice])*2
  ELSE ''
  END AS NEW_PRICE
  From [Production].[Product];*

0 Answers