Post reply

Big One !

  • 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

  • June 25, 2005 at 5:41 am

    #568453

    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

  • June 26, 2005 at 3:05 am

    #568463

    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

  • June 26, 2005 at 5:45 am

    #568465

    This can get you started :

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

  • June 26, 2005 at 6:43 am

    #568467

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

     

  • June 27, 2005 at 9:04 am

    #568634

    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.

  • June 27, 2005 at 9:24 am

    #568643

    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

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

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