--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);