In my SELECT statment I am putting the results from one CASE statment into a Column Alasis which I now need to referance in a different CASE statment. How can I go about doing this? Here is my current code, the Alasis I am trying to reference is called "Ending"
--SELECT LocationName, ProductName, PRoductNumber, (StartingGross WHERE dbo.Terminal.Groos_Gallons_Terminal IS y ELSE StragingNET), dbo.Rcpt_And_Adj.Gross/Net, +
--dbo.Transaions.Gallons/NetGallons = Sales, EndingGroos/EndingNet = End, dbo.Terminal.Groos_Gallons_Terminal = Type, If END > 0 PRING Error
--FROM dbo.DailyInventory
--JOIN dbo.Terminal ON JDELocation
--Join dbo.Rcpt_And_Adj ON JDELocation
--JOIN dbo.Transactions ON JDLocation
--GROUPBY (dbo.DailyInventory.ProductNumber)
SELECT term.Terminal_Name AS Name,
di.ProductName + ' (' + di.ProductNumber + ')' AS Product,
null AS 'Total Tank Capacity',
Beginning = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
di.StartingGrossInv
ELSE
di.StartingNetInv
END,
Adjustments = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
SUM(
CASE WHEN ra.Type = 'A'
THEN
ra.Gross_Gallons
ELSE
0
END)
ELSE
SUM(
CASE WHEN ra.Type = 'A'
THEN
ra.NET_Gallons
ELSE
0
END)
END,
Ending = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
di.EndingGrossInv
ELSE
di.EndingNetInv
END,
Type = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
'Gross'
ELSE
'NET'
END,
tp.Temp,
tp.Gravity,
null AS 'End < Capacity',
'End > ' = CASE WHEN Ending > 0
THEN
'OK'
ELSE
'***ERROR***'
END
FROM dbo.DailyInventory as di
JOIN dbo.Terminal AS term
ON di.JDELocation = term.JDELocation
JOIN dbo.Terminal_Product AS tp
ON di.JDELocation = tp.JDELocation
AND di.ProductNumber = tp.Product_Number
LEFT JOIN dbo.Transactions AS trans
ON di.JDELocation = trans.JDELocation
LEFT JOIN dbo.Rcpt_And_Adj AS ra
ON di.JDELocation = ra.JDELocation
AND trans.Ticket_Number = ra.Ticket_Number
WHERE term.Terminal_ProductionTest = 1
AND tp.Enabled = 'y'
AND di.InventoryDate BETWEEN '03-26-2013' AND GETDATE()
GROUP BY term.Terminal_Name,
di.ProductName,
di.ProductNumber,
term.Gross_Gallons_Terminal,
di.StartingGrossInv,
di.StartingNetInv,
di.EndingGrossInv,
di.EndingNetInv
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia