Quantity Break Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Quantity Break Help

Author

Topic

brendanb
Starting Member

7 Posts

Posted - 2013-07-15 : 01:09:08

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 Break

When 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.000000
2013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|24.000000 |110.000000
2013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|36.000000 |99.990000

The 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.000000
2013-07-15 00:00:00.000 | 2013-07-31 00:00:00.000 |0003540|36.000000 |99.990000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-15 : 02:10:09

[code]
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 TOP 1 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 DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

brendanb
Starting Member

7 Posts

Posted - 2013-07-15 : 22:49:47

Hi visakh16,

Many thanks this seems to resolve my issue

Brendan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 01:05:59

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources