CTE (Common Table Expression)

Hi @aloz 

I missed that you added it twice I will remove the double.

Hi @aloz 

Yes, I got an old article from the old page where I use a cte query.

https://legacysupport.timextender.com/hc/en-us/articles/115005972746-Improve-execution-times-by-splitting-exchange-rates-out-on-dates

CREATE VIEW [DSA].[CurrencyExchangeRates] AS

WITH CTE AS
(SELECT 
	[CurrencyCode],
	[ExchangeRate],
	LAG([ToDate]) OVER (ORDER BY [CurrencyCode]) AS [FromDate],
	[ToDate]
FROM [ExchangeRates_V])

SELECT [CurrencyCode],
	[ExchangeRate],
	[DateValue] AS [Date] 
FROM CTE
LEFT JOIN [Date_V] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]

The issue is more likely with creating custom tables with #tempdataset

I would probably add that part to a stored procedure and then connect to it.

I got a example that does the same as above but in a better way.

CREATE PROCEDURE [DSA].[spExchangeRates]
@iCurrencyCode int = NULL,
@iMaxCurrencyCode int = NULL
AS
BEGIN
	CREATE TABLE #TempTable 
	(
		[CurrencyCode] int,
		[ExchangeRate] decimal(38,3),
		[FromDate] datetime,
		[ToDate] datetime
	)
	WHILE(@iCurrencyCode <= @iMaxCurrencyCode)
	BEGIN
		INSERT INTO #TempTable 
		(
			[CurrencyCode],
			[ExchangeRate],
			[FromDate],
			[ToDate]
		)
		SELECT [CurrencyCode],
			ISNULL([ExchangeRate],0) AS [ExchangeRate],
			ISNULL(LAG([ToDate]) OVER (ORDER BY [DW_Id]),DATEADD(yy, DATEDIFF(yy, 0, [ToDate]), 0)) AS [FromDate],
			[ToDate]
		FROM [ExchangeRates]
		WHERE [CurrencyCode] = @iCurrencyCode

		SET @iCurrencyCode = @iCurrencyCode + 1
	END
	SELECT * FROM #TempTable
	DROP TABLE #TempTable
END

To use the procedure I made a custom table insert.

DECLARE @1 as INT = (SELECT MIN([CurrencyCode]) FROM [ExchangeRates])
DECLARE @2 as INT = (SELECT MAX([CurrencyCode]) FROM [ExchangeRates])
DECLARE @Temp TABLE 
(
	[CurrencyCode] int,
	[ExchangeRate] decimal(38,3),
	[FromDate] datetime,
	[ToDate] datetime
)
INSERT @Temp EXEC [spExchangeRates]
@iCurrencyCode = @1,
@iMaxCurrencyCode = @2
SELECT [CurrencyCode],
	[ExchangeRate],
	[DateValue] AS [Date] 
FROM @Temp
LEFT JOIN [Date_V] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]

The declare @temp table is allowed and you could place it in a view as well.