SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Quantity Break Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brendanb
Starting Member

United Kingdom
7 Posts

Posted - 07/15/2013 :  01:09:08  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/15/2013 :  02:10:09  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

brendanb
Starting Member

United Kingdom
7 Posts

Posted - 07/15/2013 :  22:49:47  Show Profile  Reply with Quote
Hi visakh16,

Many thanks this seems to resolve my issue

Brendan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  01:05:59  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000