56 lines
1.3 KiB
SQL
56 lines
1.3 KiB
SQL
--SELECT * INTO rlarp.target_prices FROM usmidsap02.ubm.pricequote.target_prices_view
|
|
--DROP TABLE pricing.target_prices
|
|
DELETE FROM pricing.target_prices
|
|
|
|
SELECT
|
|
stlc,
|
|
ds,
|
|
chan,
|
|
tier,
|
|
vol,
|
|
-- Extract lower bound: text between '[' and ','
|
|
TRY_CAST(SUBSTRING(vol, 2, CHARINDEX(',', vol) - 2) AS INT) AS lower_bound,
|
|
-- Extract upper bound: text between ',' and ')'
|
|
CASE
|
|
WHEN RIGHT(vol, 2) = ',)' THEN NULL
|
|
ELSE TRY_CAST(SUBSTRING(vol, CHARINDEX(',', vol) + 1, LEN(vol) - CHARINDEX(',', vol) - 1) AS INT)
|
|
END AS upper_bound,
|
|
price,
|
|
math
|
|
INTO
|
|
pricing.target_prices
|
|
FROM
|
|
usmidsap02.ubm.pricequote.target_prices_view;
|
|
|
|
INSERT INTO
|
|
pricing.target_prices
|
|
SELECT
|
|
stlc,
|
|
ds,
|
|
chan,
|
|
tier,
|
|
vol,
|
|
-- Extract lower bound: text between '[' and ','
|
|
TRY_CAST(SUBSTRING(vol, 2, CHARINDEX(',', vol) - 2) AS INT) AS lower_bound,
|
|
-- Extract upper bound: text between ',' and ')'
|
|
CASE
|
|
WHEN RIGHT(vol, 2) = ',)' THEN NULL
|
|
ELSE TRY_CAST(SUBSTRING(vol, CHARINDEX(',', vol) + 1, LEN(vol) - CHARINDEX(',', vol) - 1) AS INT)
|
|
END AS upper_bound,
|
|
price,
|
|
math
|
|
FROM
|
|
usmidsap02.ubm.pricequote.target_prices_view;
|
|
|
|
|
|
SELECT TOP 100 * FROM rlarp.target_prices;
|
|
|
|
SELECT * FROM rlarp.target_prices tp
|
|
WHERE
|
|
stlc = 'XNS0T1G3'
|
|
AND ds = 'v1:T..PLT..'
|
|
AND chan = 'DIR'
|
|
AND tier = 1
|
|
AND 1.01 >= lower_bound
|
|
AND (1.01 < upper_bound OR upper_bound IS NULL);
|