Hi,

Yes this is possible via a View. After you created the view, if you want you can store the outcome in a table by dragging the view into your DSA or MDW.

Hope this helps.

Kind regards,

Devin

Maybe this link helps to create the view.

After this you only have to drag the view onto the DSA or MDW to save the outcome as a table.

Dear @aloz ,

Devin's view option is good. Or, you could also drag the table (again) to the layer you are working in. just keep the columns that you want and do the transformations and then do a lookup. You could also deselect the Enable physical valid table so it would act as a view. But now you have full traceability out of the box
 

= Daniel

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.

Hi @aloz do the above comments answer your question? If so please help us by marking a best answer above. Please let us know if you have any follow up questions