Qfyilyi

Background: I have a system that keeps track of inventory that is ordered and shipped to various places. There is an Inventory table that contains all the inventory specific properties and how much was initially ordered for each item:

Inventory Table

CREATE TABLE Inventory

(

    InventoryID int, 

    InventoryName varchar, 

    AmtOrdered float, 

    Weight float, 

    Color string

)

Then I have an InventoryTransportation table that acts as a historical record of the movement of those Ordered items as they reach an end point

InventoryTransportation Table

CREATE TABLE InventoryTransportation

(

    InventoryTransportationId int,

    fkInventoryId int, 

    AmtTransported float, 

    FromLocation string, 

    ToLocation string

)

There are defined locations that these items move through to get to a final location. (Vendor -> Warehouse, Vendor -> Site, Warehouse -> Site, Warehouse -> Vendor, Site -> Vendor).

The columns I need to calculate are Qty at Vendor, Qty At Warehouse and Qty At Site.

Currently, I am just returning all the historical data and calculating these values in my business layer. But it would be so much easier if I just did this on the database side which would probably do this much more efficiently than I am doing.

The way I calculate things now is:

Qty At Vendor = AmtOrdered + Sum(AmtTransported where ToLocation =
Vendor) - Sum(AmtTransported where FromLocation = Vendor)

Qty At Warehouse = Sum(AmtTransported where ToLocation = Warehouse) -
Sum(AmtTransported where FromLocation = Warehouse)

Qty At Site = Sum(AmtTransported where ToLocation = Site) -
Sum(AmtTransported where FromLocation = Site)

Question
Is there a way to write a query that would return the Sums as columns associated with the Inventory record that they belong to?

I started with doing Left Join subqueries to do calculations, but I dont know how to do the aggregate sum with the +if its from a location or -if its to a location portion of the formula and I dont know how to return multiple columns from that sub query (or if I have to use multiple sub queries) for each of the quantities.

I think to do the multiple columns, I have to figure out how to use the SQL Pivot command, but I haven't exactly wrapped my head around how to use that.

Any help would be greatly appreciated

UPDATE
Currently, to do this, I added a series of LeftJoins to calculate each individual metric separately. It works but I don't know if it's the right way to do this.

SELECT

    Ordered = ps.quantity

    ,[@Vendor] = ps.Quantity - COALESCE(psrToVendor.ToVendor, 0) - COALESCE(psrFromVendor.FromVendor, 0)

FROM Project.ProjectSpec ps

LEFT JOIN

(

    SELECT

        psrd.ProjectSpecID

        ,ToVendor = SUM(psrd.QuantityReceived)

    FROM Project.ProjectSpecReceivedDetails psrd

    WHERE

        psrd.Deleted = 0

        AND psrd.ToProjectSpecLocationID = 1

    GROUP BY psrd.ProjectSpecID

) psrToVendor ON psrToVendor.ProjectSpecID = ps.ID

LEFT JOIN

(

    SELECT

        psrd.ProjectSpecID

        ,FromVendor = SUM(psrd.QuantityReceived)

    FROM Project.ProjectSpecReceivedDetails psrd

    WHERE

        psrd.Deleted = 0

        AND psrd.FromProjectSpecLocationID = 1

    GROUP BY psrd.ProjectSpecID

) psrFromVendor ON psrFromVendor.ProjectSpecID = ps.ID