October 12, 2005 at 10:43 am
I'm a developer that knows basic to intermediate SQL Server. I've got a table that has four prices for a product
PRODUCT_CODE VARCHAR(8)
PRODUCT_DESC VARCHAR(50)
PRICE1 MONEY
PRICE2 MONEY
PRICE3 MONEY
PRICE4 MONET
PRICE1 is the default, and they get more discounted as you go to PRICE2, PRICE3, etc. But, not all records will have values in all the prices; eg, there will always be a value in PRICE1 but there might be 0 values in one or more of the others.
A customer is assigned a discount/pricing level. I'd like some sort of routine that given the product code and price level 1-4 will return the product description and the correct price.
Here's my code in VBScript (classic ASP)
'========================================================================
' Get Price
' ors - Products recordset containing PRICE1 through PRICE4
' Level - which PRICE field to use (accepts int or string)
'========================================================================
function GetPrice(ors, Level)
dim p, n
if VarType(Level) = vbInteger then
n = Level
else
n = CInt(Level)
end if
do
p = ors.Fields("PRICE" & CStr(n))
if p <> 0 then exit do
n = n - 1
loop while n > 0
GetPrice = p
end function
Basically it starts with the price field corresponding to the passed price level, and loops back towards PRICE1 until it finds a non-zero value.
How would I do this in T-SQL?
October 12, 2005 at 11:25 am
Scott,
How about this...
CREATE PROCEDURE dbo.getPrice @PRODUCT_CODE VARCHAR(8)
, @PRICE_CODE INT
AS
SELECT PRICE = CASE
WHEN PRICE4 > 0.00 AND @PRICE_CODE > 3 THEN PRICE4
WHEN PRICE3 > 0.00 AND @PRICE_CODE > 2 THEN PRICE3
WHEN PRICE2 > 0.00 AND @PRICE_CODE > 1 THEN PRICE2
ELSE PRICE1
END
FROM dbo.yourTable
WHERE PRODUCT_CODE = @PRODUCT_CODE
GO
Norman
October 12, 2005 at 2:22 pm
Works like a charm! Man, I was trying things with all kinds of if else's - didn't think of putting multiple conditions in the WHEN clauses.
Thanks!
October 12, 2005 at 2:55 pm
Even better, I was able to turn it into a UDF
CREATE FUNCTION [dbo].[GetPrice]
(@ProdCode VARCHAR(8),
@PriceLevel INT)
RETURNS MONEY AS
BEGIN
DECLARE @Price MONEY
SELECT @Price = CASE
WHEN PRICE4 > 0.00 AND @PriceLevel > 3 THEN PRICE4
WHEN PRICE3 > 0.00 AND @PriceLevel > 2 THEN PRICE3
WHEN PRICE2 > 0.00 AND @PriceLevel > 1 THEN PRICE2
ELSE PRICE1
END
FROM Products
WHERE PRODUCT_CODE = @ProdCode
RETURN @Price
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply