Hi,I'm in need of some help in trying to work out a query to select a correct Qty and Value break. Essentially this is a price BreakWhen I run the following code, I get 3 rows returned. Ideally I would only get the first row.The below is all based on QTY Breaks. For example, if you enter @QTY =8 You should only get the First Break.If you enter 9 or 15, you would still only get the 8+ price, until you hit 24, which it would then get the next step in pricing DECLARE @SupplierNo VARCHAR(20) DECLARE @ItemCode VARCHAR(20) DECLARE @Qty Int DECLARE @DateRange date SET @SupplierNo = 'S00034' SET @ItemCode = '0003540' SET @Qty = 8 -- Qty SET @DateRange ='2013-07-16 00:00:00.000' select FromDate,ToDate,Spp2.ItemCode as Item , Amount as QTY,Spp2.Price from SPP2 Inner Join SPP1 on SPP2.CardCode = SPP1.CardCode where SPP2.CardCode = @SupplierNo AND SPP2.SPP1LNum = SPP1.LINENUM AND SPP2.ItemCode = @ItemCode AND @DateRange Between SPP1.FromDate AND SPP1.ToDate AND SPP2.Amount >= @Qty order by SPP2.Amount
Example Data Returned:FromDate | ToDate |Item |Qty |Price--------------------------------------------------------------------------------------------2013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|8.000000 |115.0000002013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|24.000000 |110.0000002013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|36.000000 |99.990000The row in Bold is the One I should see only returned.In the below example, if I change the QTY to say 15, It should still only return the 8+ Row, But what It does is goes to the next break DECLARE @SupplierNo VARCHAR(20) DECLARE @ItemCode VARCHAR(20) DECLARE @Qty Int DECLARE @DateRange date SET @SupplierNo = 'S00034' SET @ItemCode = '0003540' SET @Qty = 15 -- Qty SET @DateRange ='2013-07-16 00:00:00.000' select FromDate,ToDate,Spp2.ItemCode as Item , Amount as QTY,Spp2.Price from SPP2 Inner Join SPP1 on SPP2.CardCode = SPP1.CardCode where SPP2.CardCode = @SupplierNo AND SPP2.SPP1LNum = SPP1.LINENUM AND SPP2.ItemCode = @ItemCode AND @DateRange Between SPP1.FromDate AND SPP1.ToDate AND SPP2.Amount >= @Qty order by SPP2.Amount
FromDate | ToDate |Item |Qty |Price--------------------------------------------------------------------------------------------2013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|24.000000 |110.0000002013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|36.000000 |99.990000