search value in columns

[DanHD]

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

 

[GHolden]

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.

 

[chrislarkin]

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

 

[DanHD]

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

 

[MarkSweetland]

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:

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

 

[imex]

Hi,

Try:

select top 1 * from @MyTable
where Qty <= 25
order by Qty desc

Hope it's useful.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[DanHD]

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