Big One !

Question

Big One !

mark richardson

SSC Eights!

Points: 807
More actions
June 25, 2005 at 1:52 am

#66302

Hi all,
This is a rather big Q to ask but hey - i'll try anyway!
I have a website where orders are placed that may contain several rows of product.
In my suppliers table, I have multi-suppliers who may all have some or none of the products that are being ordered.
What i'd like to happen is a dynamic statement that looks at the stock for each supplier for each of the products in the order and determine the most efficient supplier to order all the products from (ie, the supplier that has ALL of the orderline products in stock or THE MOST of the orderlines in stock.
Could a statement also, where say a supplier is chosen from the above criteria that has 4 of the 5 products in stock, also then write a line for the next(other) supplier that has the 5th product in stock?
The result must then be written to a temp table.
ie: SUPPLIER,PRODUCT,QTY_IN_STOCK
mmm.... I know i'm asking a lot but can this be done.
Thanks,
mark

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 1

Hi Mark,

can explain what exactly want. i mean u want the supplier u had given order for all the product or non of the product or few of the product and at the mean time u want that which supplier had ordered 4 or 5 products and also the next supplier who had ordered the 5 product.am i right

pls tell me then i can try

sufian

mark richardson SSC Eights! Points: 807 More actions · Answer 2

Hi Sufian,

mmm...

Let me try explain this way:

a CUSTOMER places an order for, lets say 5 products.

In order for us to ship the order, we have several SUPPLIERS who may each have some of the products in stock to ship to the customer.

What i'd like to write to a temporary table, is the PREFFERED SUPPLIER who may have ALL of the products in stock, or, if NONE of the SUPPLIERS have ALL of the products in stock, for it to write the SUPPLIER who can fulfill MOST of the order:

for example:

Order is placed by CUSTOMER for products a1,a2,a3,a4,a5

SUPPLIER table has following entries:

SUPPLIER,PRODUCT,QTY_IN_STOCK

SUPP1 , a1 , 5

SUPP1 , a2 , 3

SUPP1 , a3 , 10

SUPP1 , a4 , 7

SUPP2 , a2 , 3

SUPP2 , a4 , 5

SUPP2 , a5 , 10

SUPP3 , a1 , 8

in the above example, to fulfil all of the order, the statement should write into the temp table:

SUPPLIER , PRODUCT , QTY_IN_STOCK

SUPP1 , a1 , 5

SUPP1 , a2 , 3

SUPP1 , a3 , 10

SUPP1 , a4 , 7

SUPP2 , a5 , 10

what the statement says is: SUPPLIER SUPP1 has MOST of the products in stock so write it's products and qty_in_stock's into the temp table, followed by a different SUPPLIER (SUPP2) who has the other product (a5) in stock.

I hope this makes sense,

Thanks for taking the time to look at this,

Regards,

Mark

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This can get you started :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=193380

mark richardson SSC Eights! Points: 807 More actions · Answer 4

Sorry - can't relate the given link to my problem ?!?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

You just need to replace the having count(*) = X to order by count(*) desc and select the top X suppliers, then let the application decide which one to pick (unless you want all the work to be done on the server).

Is there something you don't understand in the code I posted.

noeld SSC Guru Points: 96590 More actions · Answer 6

OR:

-- create table Suppliers( SUPPLIER char(5),PRODUCT varchar(5),QTY_IN_STOCK int)

--

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP1', 'a1' , 5)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP1', 'a2' , 3)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP1', 'a3' , 10)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP1', 'a4' , 7)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP2', 'a2' , 3)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP2', 'a4' , 5)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP2', 'a5' , 10)

-- insert into Suppliers (SUPPLIER, PRODUCT, QTY_IN_STOCK) values('SUPP3', 'a1' , 8)

--

declare @Prods Table( PRODUCT varchar(5))

insert into @Prods(PRODUCT)

select 'a1'

union all select 'a2'

union all select 'a3'

union all select 'a4'

union all select 'a5'

select P.PRODUCT, S.SUPP, S.MAX_QTY

from

@Prods P -- Just in case there are not suppliers for that Product yet

left join

(select PRODUCT, MIN(SUPPLIER) SUPP, MAX(QTY_IN_STOCK) MAX_QTY

from Suppliers

where QTY_IN_STOCK > 0 -- a bit of speed

group by PRODUCT ) S on P.PRODUCT = S.PRODUCT

* Noel