56 lines
1.7 KiB
Transact-SQL
56 lines
1.7 KiB
Transact-SQL
CREATE OR ALTER FUNCTION pricing.guidance_logic (
|
|
@target_price NUMERIC(20,5),
|
|
@last_price NUMERIC(20,5),
|
|
@list_price NUMERIC(20,5)
|
|
)
|
|
RETURNS @result TABLE (
|
|
guidance_price NUMERIC(20,5),
|
|
guidance_reason NVARCHAR(MAX)
|
|
)
|
|
AS
|
|
BEGIN
|
|
DECLARE @price NUMERIC(20,5);
|
|
DECLARE @reason NVARCHAR(MAX) = '';
|
|
DECLARE @floored NUMERIC(20,5);
|
|
DECLARE @capped NUMERIC(20,5);
|
|
DECLARE @effective_price NUMERIC(20,5);
|
|
|
|
IF @target_price IS NOT NULL AND @last_price IS NOT NULL
|
|
BEGIN
|
|
SET @floored = dbo.GREATEST_NUMERIC205(@target_price, @last_price * 0.95);
|
|
SET @capped = dbo.LEAST_NUMERIC205(@floored, @last_price);
|
|
SET @price = dbo.LEAST_NUMERIC205(
|
|
ISNULL(@list_price, 1e9),
|
|
@capped
|
|
);
|
|
|
|
IF @price = @last_price
|
|
BEGIN
|
|
SET @reason = 'Cap at last price';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @reason = 'Using target price';
|
|
IF @target_price < @last_price * 0.95
|
|
SET @reason += ', floored to 5% below last price';
|
|
IF @target_price > @last_price
|
|
SET @reason += ', capped to not exceed last price';
|
|
IF @list_price IS NOT NULL AND @price = @list_price AND @target_price > @list_price
|
|
SET @reason += ', capped to not exceed list price';
|
|
END
|
|
END
|
|
ELSE IF @last_price IS NOT NULL
|
|
BEGIN
|
|
SET @price = @last_price;
|
|
SET @reason = 'Last price - no target';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @price = @target_price;
|
|
SET @reason = 'Target price - no prior sale';
|
|
END
|
|
|
|
INSERT INTO @result VALUES (@price, @reason);
|
|
RETURN;
|
|
END
|