SQL Server 2022 Select - Window Clause with multiple window definitions stops code suggestions

JustinBBBB

Here is an example:

;With cte1 as (
    Select tn.value, tn.value + 1 AS valPlus1 from generate_series(0,100,1) tn
), cte2 AS (
	SELECT
		c.[value],
		SUM(c.[value]) OVER lastTenWin AS last10Sum
		, SUM(c.[value]) OVER lastElevenWin AS last11Sum -- comment me and the second window name below to make Prompt work again
		--, SUM(c.[value]) OVER (ORDER BY c.value ROWS BETWEEN 10 preceding AND CURRENT ROW) AS last11Sum  -- uncomment me with the other two commented and Prompt still works.
	FROM 
		[cte1] c
	WINDOW
		lastTenWin AS (ORDER BY c.value ROWS BETWEEN 9 preceding AND CURRENT ROW)
		, lastElevenWin AS (ORDER BY c.value ROWS BETWEEN 10 preceding AND CURRENT ROW)
)
SELECT * FROM cte2 ORDER BY value

This is valid SQL as multiple window definitions are allowed.

Jon_Kirkwood

Thank you for reporting this, it has been escalated to our developers for their consideration as a fix in a future release of SQL Prompt.

It is in their backlog to be investigated and I will endeavour to update this forum post with any findings and/or releases that include a fix for this code suggestion issue.