SQL Server Programming - Query a pricelist for price breaks

Author

Query a pricelist for price breaks

Darious Snell

2005-10-31, 7:03 pm

The table looks like this:
PARTNUM, BREAKPOINT, PRICE

The table might contain data that looks like this:
ABC,3,10.00
ABC,5,8.00
ABC,10,7.00
XYZ,5,13.00

How do I make a query that can give me the price if I supply a part number and quantity?

For instance, if someone is buying 4 ABCs, the query should return 10.00.
If someone is buy 6 ABCs the program should return 8.00
If someone is buying 50 ABCs the query should return 7.00

I would like to do this in a query rather than a function/SP.

Can anyone give me a hand?

Thanks in advance!

SQL

2005-10-31, 7:03 pm

create table PriceBreak (PARTNUM char(3), BREAKPOINT int , PRICE money)

insert into PriceBreak
select 'ABC',3,10.00
union all
select'ABC',5,8.00
union all
select'ABC',10,7.00
union all
select'XYZ',5,13.00

For instance, if someone is buying 4 ABCs, the query should return
10.00.
select min(PRICE) from PriceBreak where PARTNUM ='ABC' and BREAKPOINT
<= 4

If someone is buy 6 ABCs the program should return 8.00
select min(PRICE) from PriceBreak where PARTNUM ='ABC' and BREAKPOINT
<= 6

If someone is buying 50 ABCs the query should return 7.00
select min(PRICE) from PriceBreak where PARTNUM ='ABC' and BREAKPOINT
<= 50

------------------------------------------------------------------------------------------
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Raymond D'Anjou

2005-10-31, 7:03 pm

select top 1 price where partnum = @partnum and breakpoint < @quantity order
by breakpoint desc

"Darious Snell" <null@sdlkfweu.org> wrote in message
news:11mcp7nhbbjr10f@corp.supernews.com...
> The table looks like this:
> PARTNUM, BREAKPOINT, PRICE
>
> The table might contain data that looks like this:
> ABC,3,10.00
> ABC,5,8.00
> ABC,10,7.00
> XYZ,5,13.00
>
> How do I make a query that can give me the price if I supply a part number
> and quantity?
>
> For instance, if someone is buying 4 ABCs, the query should return 10.00.
> If someone is buy 6 ABCs the program should return 8.00
> If someone is buying 50 ABCs the query should return 7.00
>
> I would like to do this in a query rather than a function/SP.
>
> Can anyone give me a hand?
>
> Thanks in advance!
>
>

Anith Sen

2005-10-31, 7:03 pm

This would be one of the simpler ones:

SELECT TOP 1 price
FROM tbl
WHERE partnum = @partnum
AND breakpoint <= @qty
ORDER BY breakpoint DESC ;

--
Anith

Sponsored Links