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
 General SQL Server Forums
 New to SQL Server Programming
 Select price for a product based on price break

Author  Topic 

rusmanicai
Starting Member

13 Posts

Posted - 2014-12-12 : 16:43:40
Hello,
I have to add the unit price on the order acknowledgement for products on our shelf.
Each product has different price breaks stored in a table called MaterialUnitCost.
I don't know how to pull the correct price based on the order quantity.
Let's say the customer orders 200 pieces,
I sell 1 pcs @ $20
50 pcs @$15
200 pcs @$10.

My order acknowledgement should pull a unit price of $10, but it pulls $20 instead, because in my select statement I have

select materialunitcost.unitcost.

I thought I should do a loop or use the row_number function, but I am new to SQL, and I never used any of these two.

Can anyone please help me construct my select statement to accomplish this?

Thank you,
Julia.

RJulia

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-13 : 06:26:24
Please provide:
- table definitions
- sample data
- the query you're haveing trouble with
- expected output

Like this: [url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]
Go to Top of Page
   

- Advertisement -