Query help - Using Alias in column equation

[Former Member]

Hi All,

I have selected some data as an alias and would like to refer to the alias in another equation column. I cannot figure out how to do this correctly.

Here is my full query below, with a NULL called '3 Mnth Average' column because I am unsure how to reference the Alias and data type correctly for the required equation.

For example, I want to return the '3 Mnth Average' result being the '3 Mnth Total' Alias divided by 3. ('3 Mnth Total' / 3).... Every time I try... i fail.

Any help would be most appreciated.

Best regards,

John

SELECT T0.ITEMCODE, T0.ITEMNAME, 
Cast((T0.ONHAND - T0.IsCommited) As INT) As 'Available', 
Cast(T0.ONHAND As INT) As 'In Stock', Cast(T0.IsCommited As INT) As 'Committed',
Cast((SELECT SUM(T1.[Quantity]) FROM INV1 T1 WHERE T1.ITEMCODE = T0.ITEMCODE 
AND DATEPART(mm,T1.[DocDate])  >= DATEPART(mm,getdate())-3 
AND DATEPART(mm,T1.[DocDate]) <= DATEPART(mm,getdate())) As INT) As '3 Mnth Total',
(NULL) As '3 Mnth Average',
CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) As INT) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',
CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) As INT) AS 'JUL QTY',
CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS INT) AS 'AUG QTY',
CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) As INT) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = 
T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC QTY'
FROM dbo.OITM T0
LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.SellItem = 'Y' 
GROUP BY T0.ItemCode, T0.ItemName, T0.OnHand, T0.IsCommited, YEAR(T1.DOCDATE) 
HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())
ORDER BY T0. ITEMCODE

[Former Member · Accepted solution]

Hi John,

If you are not using temporary table, you have to rewrite complete formula to get 3 month average. There is no way to reference within the same query.

Thanks,

Gordon

[Former Member]

Hi,

What is your issue?

Can you give some more clearly? If you do so it would be more helpfull for us to analyse the problem.