invalid column name in query

Question

I am writing a query in my ERP and getting an error and not sure how to go about it.

DECLARE @YEAR AS INT = 2022  
  
SELECT c.CardCode  
      ,c.CardName  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) AS [Sales given year -3]  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) AS [Sales given year -2]  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) AS [Sales given year -1]  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) AS [Sales given year]  
FROM OCRD c  
     INNER JOIN OSLP sp ON c.SlpCode = sp.SlpCode  
WHERE sp.SlpName like '%'  
AND [Sales given year -3] <= 2000 AND [Sales given year -2] <= 2000 AND [Sales given year -1] <= 2000 AND [Sales given year] <= 2000   
AND (([Sales given year -3] + [Sales given year -2] + [Sales given year -1] + [Sales given year]) != 0 )  

Answer 1

If you don't want to use CTE:

Select * from (
SELECT sp.SlpName, c.CardCode
,c.CardName
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) AS [Sales given year -3]
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) AS [Sales given year -2]
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) AS [Sales given year -1]
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) AS [Sales given year]
FROM OCRD c
INNER JOIN OSLP sp ON c.SlpCode = sp.SlpCode

       ) t

  WHERE  SlpName like '%'
  AND [Sales given year -3] <= 2000 AND [Sales given year -2] <= 2000 AND [Sales given year -1] <= 2000 AND [Sales given year] <= 2000 
  AND (([Sales given year -3] + [Sales given year -2] + [Sales given year -1] + [Sales given year]) <> 0 )