Kingston Dhasian (9/11/2013)

---

One of the ways to achieve it..

DECLARE@tableSTK TABLE

(

ItemIDVARCHAR(8),

ItemDescVARCHAR(100),

PriceMONEY

)

DECLARE @tableABC TABLE

(

ItemIDVARCHAR(8),

ConditionAmtMONEY,

Return_AmtMONEY

)

INSERT@tableSTK

( ItemID, ItemDesc, Price )

SELECT'04400110', 'A4 Paper', 30.00 UNION ALL

SELECT'08800220', 'Bic Pen 5.0', 20.00

INSERT@tableABC

( ItemID, ConditionAmt, Return_Amt )

SELECT'04400110', 100.00, 28.50 UNION ALL

SELECT'04400110', 200.00, 27.00 UNION ALL

SELECT'04400110', 300.00, 25.50 UNION ALL

SELECT'08800220', 50.00, 19.00 UNION ALL

SELECT'08800220', 80.00, 18.00 UNION ALL

SELECT'08800220', 150.00, 15.00 UNION ALL

SELECT'08800220', 200.00, 14.00

DECLARE@ConditionAmt INT,

@ItemID VARCHAR(8)

SELECT@ConditionAmt = 95.00,

@ItemID = '08800220'

; WITH cte_table AS

(

SELECTROW_NUMBER() OVER( PARTITION BY ItemID ORDER BY ConditionAmt ) AS RN, * FROM

(

SELECTItemID, 0 AS ConditionAmt, Price

FROM@tableSTK

UNION ALL

SELECTItemID, ConditionAmt, Return_Amt

FROM@tableABC

) AS T

)

SELECTT1.ItemID, T1.Price

FROMcte_table AS T1

LEFT JOIN cte_table AS T2 ON T1.ItemID = T2.ItemID AND T1.RN = T2.RN - 1

WHERET1.ItemID = @ItemID AND T1.ConditionAmt <= @ConditionAmt AND ( T2.ConditionAmt > @ConditionAmt OR T2.ConditionAmt IS NULL )