SQL Server Performance

Query about current stock in one store but not another

Discussion in 'SQL Server 2008 General Developer Questions' started by EMoscosoCam, Jun 1, 2010.

  1. EMoscosoCam Member

    Hello
    I have a CurrentStock Table with the structure (#StoreCode, #SKU, Stock). I would like to retrieve all SKUs that have at least 5 units in StoreCode = '1' but none in StoreCode = '2', then at least 5 units in StoreCode = '1' but none in StoreCode='3' and so for.
    The resultset would have the same structure (StoreCode, SKU, Stock). How can I accomplish that?
    Thanks a lot.
  2. adnan8t2 New Member

    As I understand, it would beSelect S1.StoreCode, S1.SKU, S1.Stock From CurrentStock S1 Where (S1.Stock >= 5 AND S1.StoreCode = '1')AND S1.SKU IN (Select S2.SKU From CurrentStock S2 Where S2.Stock = 0 AND S2.StoreCode = '2')AND S1.SKU IN (Select S3.SKU From CurrentStock S3 Where S3.Stock = 0 AND S3.StoreCode = '3')Hope this helpAdnan
  3. Flexdog New Member

    Your question could be alot clearer and helpful if you would include an sample dataset along with expected result. Well, here is my stab at answer your question:
    IF object_id('tempdb..#t') is not null
    DROP TABLE #t
    CREATE TABLE #t(sid int, sku varchar(16), stock int)
    INSERT #t(sid, sku, stock)
    VALUES(1,'x',6),
    (1,'y',7),
    (2,'y',5),
    (2,'x',0),
    (3,'x',0),
    (3,'y',0)
    DECLARE @targetid int=1
    SELECT t1.sid, t1.sku, t1.stock, 'storecode_with_no_stock'=t2.sid
    FROM #t t1
    INNER JOIN #t t2 ON t1.sku=t2.sku and t2.sid <> @targetid and t2.stock=0
    WHERE t1.sid =@targetid and t1.stock >=5
    If you are looking for sku that in targetid store with stock>=5 and stock=0 for all other stores, just replace the inner join set with a subquery using group-by and having clause.
    gl.
  4. preethi Member

    I believe the purpose of this query is to transfer items which are in excess in one/more stores to stores where no stock is available.
    I modified the above query slightly. Hope this helps.
    SELECT t1.sid, t1.sku, t1.stock, t2.sid, t2.stock
    FROM #t t1 INNER JOIN #t t2
    ON t1.sku=t2.sku and t2.sid <> t1.sid and t2.stock=0 and t1.stock >=5

Share This Page