How do I use this table variable correctly in a select statement?-CodePudding

I have inserted a value into a variable as shown below:

DECLARE @VARIABLE TABLE (VARIABLECOLUMN NUMERIC)

INSERT INTO @VARIABLE
SELECT SUM(VARIABLECOLUMN)/12 AS VARIABLECOLUMNALIAS
FROM TABLE1
WHERE LOCATION = 'SOMEWHERE'
AND DATE BETWEEN DATEADD(WEEK,-12,GETDATE()) AND GETDATE();

And my intention is to use the value of this variable in another select statement like below:

SELECT [ADATE]
,DATEPART(month, [ADATE]) as 'Month'
,DATEPART(YEAR, [ADATE]) AS 'YEAR'
,SUM([SALES])/@VARIABLE as 'sales'
FROM TABLE2
where [ADATE] > '2021-09-30'
and datepart(dw,ADATE) = 6
--and [ADATE] BETWEEN @STARTDATE AND @ENDDATE
and [LATE] = 'Late'
and [LOCATION] = 'SOMEWHERE'
GROUP BY ADATE, DATENAME(month, [ADATE]), DATENAME(YEAR, [ADATE])

So as seen I am trying to divide a column in another table by the value of the variable however this does not work and I don't know where to go from here. Note: Table1 and Table2 have no relationship.

If someone could point me in the right direction for a solution I would appreciate it. Thank you.

CodePudding user response：

This should work for you.

DECLARE @VARIABLE NUMERIC


SELECT @VARIABLE = SUM(VARIABLECOLUMN)/12
FROM TABLE1
WHERE LOCATION = 'SOMEWHERE'
AND DATE BETWEEN DATEADD(WEEK,-12,GETDATE()) AND GETDATE();