How can I get overall lowest cost SQL query

Question

4.50/5 (2 votes)

See more:

Dear Experts,

I have problem in Microsoft Access/SQL query.

I have three database tables (1. Material, 2. Suppliers, 3. OFFERS) OFFERS table contains different offers of suppliers for different materials like as:

OfferID Quantity UnitPrice DelTime Supplier Material
1       1        99        5       A        Chair
2       3        90        5       A        Chair
3       5        80        5       A        Chair
4       1        95        5       B        Chair
5       1        90        5       C        Chair
6       3        85        5       C        Chair
7       10       80        5       C        Chair

I want to get the supplier with overall lowest cost for 14 chairs.

The paper work for overall lowest price is given as:

Material: Chairs
Order quantity: 14

Cost for Supplier A:
10 x 80 = 800 (as by offer 3)
3 x 90 = 270 (as by offer 2)
1 x 99 = 99 (by offer 1)
Overall Cost for supplier A is: 1169

Cost for Supplier B:
14 x 95 = 1330 (by offer 4)
Overall Cost for supplier B is: 1330

Cost for Supplier C:
10 x 80 = 800 (by offer 7)
3 x 85 = 255 (by offer 6)
1 x 90 = 90 (offer 5)
Overall Cost for supplier C is: 1145

In this, Supplier C offers the minimum cost for 14 chairs.

How I can access it via a query?

Posted 26-Nov-09 3:37am

Qasim1984

Updated 19-May-10 4:29am

Deeksha Shenoy

v6

Add a Solution

Comments

senguptaamlan 13-Dec-10 5:04am

please show some code...what you have d one...and at which point you are facing the technical difficulty....if you are asking for logic....its better to give a chance to Ur brain...

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2009-11-26T03:41:00

Solution 1

I suspect you're going to need to write some code to do this, because the SQL to calculate it for each and then work out which is cheaper will be quite complex IMO. I am no SQL guru, you may find someone who can give a simple SQL solution, but it doesn't seem that way to me.

Posted 26-Nov-09 3:41am

Christian Graus

Kschuler · Answer 2 · 2010-04-15T04:52:00

Why don't you start writing the SQL and then ask a more specific question about the problems you are having. If you don't even know where to start, you should reasearch SQL[^] first.

If you have some knowledge in SQL, you could start by researching the Group By[^], because it sounds like you are going to need to Group By your supplier.

Hope this helps.

AspDotNetDev · Answer 3 · 2010-04-15T13:42:00

Return the min of the result of the the subquery that groups by suppliers and takes the SUM of Quantity * UnitPrice. Not sure about Access, but the MSSQL version would look something like:

SQL

SELECT TOP 1
    Total,
    TotalUnits,
    Supplier
FROM
(
    SELECT
        SUM(Quantity * UnitPrice) AS Total,
        SUM(Quantity) AS TotalUnits,
        Supplier
    FROM Offers
    GROUP BY
        Supplier
) AS Totals
ORDER BY
    Total ASC

Qasim1984 · Answer 4 · 2009-11-26T05:01:00

I want to calculate overall cost offered by each supplier for a paticular material (Chair) and quantity(14) ,sort them in ascending order (by overall cost) and want to show these result in a gridview.

I need

1. SQL Query or

2. Code

In case 2 if I can get all offers from db and load it into DataTable (Dataset) How I can calculate overall cost programatically and then sort this datatable.

How can I get overall lowest cost SQL query

4 solutions

Solution 1

Solution 11

Solution 16

Solution 4

Add your solution here

Preview 0