Calculating Values in SQL Query and using those values in other calculations in the same query

Question

I have a table for storing inventory and when displaying the inventory I would like to do some calculations, and run a subquery which returns a value I will use in some calculations.

My starting point is this:

SELECT [lineID]
      ,[SKUNumber]
      ,[ContainerNumber]
      ,[UnitsEaches]
      ,ISNULL((select Sum(QTY) from AllocatedInventory
               Where AllocatedInventory.SKUNumber = Inventory.SKUNumber),0) as 'Allocated'
      ,[Bay]
      ,[Level]
      ,[Position]
      ,[VCP]
      ,[UnitWeight]
      ,[Comments]
      ,[DateReceived]
FROM [dbo].[Inventory]

The first thing I want to do is subtract the Allocated amount returned by the subquery from the UnitsEaches to calculate the AvailableInventory. When I try to add a SUM function using Allocated I get an error that says Allocated is an invalid column.

Once I get that sorted I need to then calculate the Master Cartons by dividing the AvailableInventory by the VCP.

Answer 1

You can't refer to an alias in the same scope where it is defined. Here, it seems like a lateral join is the simplest solution:

SELECT i.[lineID]
      ,i.[SKUNumber]
      ,[ContainerNumber]
      ,i.[UnitsEaches]
      ,ai.Allocated
      ,i.[UnitsEaches] - ai.Allocated as AvailableInventory
      ,i.[Bay]
      ,i.[Level]
      ,i.[Position]
      ,i.[VCP]
      ,i.[UnitWeight]
      ,i.[Comments]
      ,[DateReceived]
FROM [dbo].[Inventory]
OUTER APPLY (
    SELECT COALESCE(SUM(ai.QTY), 0) allocated
    FROM AllocatedInventory ai
    WHERE ai.SKUNumber = i.SKUNumber
) ai