SQL Server Performance

A query about sucessive item transfers among warehouses

Discussion in 'SQL Server 2005 General Developer Questions' started by EMoscosoCam, Aug 12, 2008.

  1. EMoscosoCam Member

    Hello
    I have two tables: TransferMaster(#DocID, DocDate, SourceWarehouseID) and TransferDetail(#DocID, #ItemID, TargetWarehouseID, Quantity). Sometimes an Item is transferred from one Warehouse to another and then to another. Using solely this two tables, how can I make a query that gives me the actual quantity of each distinct item in each warehouse?
    Thanks a lot.
  2. preethi Member

    Hi,
    You haven't give the details on how the operation happens. So I am assuming something here.Please let us know if it clearly explains your operation.
    You will enter multiple entries in TransferMaster, one for each transfer based the Source Warehouse.
    For example when you transfer 10 items from Warehouse 1 (out of which 5 goes to warehouse 2) you need to have one entry in Transfer Master and 10 in TransferDetail (out of 10, 5 will have TragetWarehouseID 2). When Items move from Warehouse 2 to another one you will have another entry in TransferMaster with SourceWarehouseID = 2 and 1 for each time per warehouse in TransferDetails..

    You need to get the sum of quantity from TransferDetails Per TargetWarehouseID and deduct the sum from SourceWarehouseID

    SELECT A.TargetWarehouseID, A.ItemID, (A.RecivedQuantity - B.IssuedQuantity) AS BalanceQuantity
    FROM
    (SELECT TargetWarehouseID, ItemID, SUM(Quantity) AS RecivedQuantity FROM
    TransferDetail
    GROUP BY TargetWarehouseID, ItemID
    ) A
    INNER JOIN
    (SELECT TM.SourceWarehouseID, TD.ItemID, SUM(TD.Quantity) AS IssuedQuantity FROM
    TransferMaster TM INNER JOIN TransferDetail TD ON TM.DocID = TD.DocID
    GROUP BY TM.SourceWarehouseID, TD.ItemID
    ) B
    ON A.TargetWarehouseID = B.SourceWarehouseID AND A.ItemID = B.ItemID

Share This Page