SQL Server 2016 Error: GROUP BY expression must contain at least one column that is not an outer reference

[hgreen]

Hi all,

I am trying to build a viz that shows the fiscal year of the date selected by a user in a templated filter. For example, if a user selects May 31, 2022 I would like to display FY22 (in Australia so our fiscal year begins 1-July each year). The SQL generated is below:

SELECT TOP (500)
    (YEAR(DATEADD(month,6, CONVERT(datetime, CONVERT(VARCHAR(7),(CONVERT(VARCHAR(10),CASE WHEN CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time', 102), 120),120)+'-01 00:00:00', 120) IS NULL THEN '2014-01-01' ELSE CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time', 102), 120),120)+'-01 00:00:00', 120) END,120)) ,120)+'-01 00:00:00', 120) ))) AS [sfdata_opportunities_activities.forecast_years_start]
FROM Looker.Calendar  AS calendar
INNER JOIN Looker.SFData_Opportunities_Activities  AS sfdata_opportunities_activities ON (sfdata_opportunities_activities."Activity Date") =  (CONVERT(VARCHAR(10),calendar.calendar_date ,120))
GROUP BY
    (YEAR(DATEADD(month,6, CONVERT(datetime, CONVERT(VARCHAR(7),(CONVERT(VARCHAR(10),CASE WHEN CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time', 102), 120),120)+'-01 00:00:00', 120) IS NULL THEN '2014-01-01' ELSE CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time', 102), 120),120)+'-01 00:00:00', 120) END,120)) ,120)+'-01 00:00:00', 120) )))
ORDER BY
    1 DESC

However, there is an error when running this:

The Microsoft SQL Server 2016 database encountered an error while running this query.

Each GROUP BY expression must contain at least one column that is not an outer reference.

The only advice I’ve been able to find is to remove the Group By altogether, which I’ve tried in SQL Runner, however this results in the FY value being returned for all rows.

The query works using an Exasol connection, with modifications to the calcs to suit.

Does anyone have any advice? Should I be structuring the query differently to suit an SQL Server dialect?