Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

search value in columns

Status
Not open for further replies.

DanHD

Technical User
Dec 1, 2008
98
NL
hello,

in the discount table is a column with volume discount:

qty discount
10 15%
20 18%
30 19%

I've a quantity of 25, how can I find the right discount, in this case 18%


Dan
 
Hi, I would have a QTY_MIN & QTY_MAX in the table

QTY_MIN QTY_MAX DISCOUNT
10 19 15%
20 29 18%
etc...

Then search for >= QTY_MIN AND <= QTY_MAX

There are two ways to write error-free programs; only the third one works.
 
Hi DanHD,

If you cannot modify the table, please post a little more about your problem - is this for straight SQL or are you using in CR, and if it's a CR issue, are you using a command object or building the selection in the CR designer?

Best,
Chris

 
hi

I can't modify the table. It's part of an ERP system and it's just a part of my problem.
But I try to break it down in small parts.

I think something with row() to get the fitst and next row 'amount' on one line (I forgot, also the item is in the table, but no rownumber)

Item qty discount
100 10 15%
101 20 18%
102 30 19%

I think the solution is to get a temp table, like:

Item qty qty1 discount
100 10 20 15%
101 20 30 18%
102 30 null 19%

Dan
 
Assuming each item has its own discount set, you can use a subquery to filter to the quantity amount desired and then extract the maximum discount:

Code:
set nocount on 
declare @tbl table(itemNo int, qty int, discount real)
insert into @tbl(itemNo, qty, discount)
select 100, 10, .15
union all 
select 100, 20, .18
union all
select 100, 30, .19
union all
select 101, 10, .11
union all 
select 101, 20, .13
union all
select 101, 30, .17


select isNull(MAX(discount),0)
from 
(
	select discount
	from @tbl
	where qty < 25 and itemNo = '101'
) as s


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
hello

I found an example on google and rewrite it for my case:

WITH MyCTE (ItemCode, Amount, Price, RowVersion)
AS(
select ItemCode, Amount, Price
, ROW_NUMBER() OVER(PARTITION BY itemcode ORDER BY Amount ASC) RowVersion
from SPP2
)
SELECT BASE.ItemCode
,BASE.Amount as AmountFrom
,LEAD.amount as AmountTo
,BASE.Price
FROM MyCTE BASE
LEFT JOIN MyCTE LEAD ON BASE.ItemCode = LEAD.ItemCode
AND BASE.RowVersion = LEAD.RowVersion-1

sofar it seems to work, I save it as a view and can query on it.

The top1 function wasn't an option because it gives other limitations in my overall query.

Dan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top