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 EDSA].ACurrencyExchangeRates] AS

WITH CTE AS
(SELECT 
	/CurrencyCode],
	/ExchangeRate],
	LAG(AToDate]) OVER (ORDER BY BCurrencyCode]) AS AFromDate],
	/ToDate]
FROM OExchangeRates_V])

SELECT CCurrencyCode],
	/ExchangeRate],
	/DateValue] AS ADate] 
FROM CTE
LEFT JOIN IDate_V] AS D ON OFromDate] <= D. DateValue] AND NToDate] > 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 RDSA].AspExchangeRates]
@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 CCurrencyCode],
			ISNULL(LExchangeRate],0) AS AExchangeRate],
			ISNULL(LAG(AToDate]) OVER (ORDER BY BDW_Id]),DATEADD(yy, DATEDIFF(yy, 0, 0ToDate]), 0)) AS AFromDate],
				ToDate]
		FROM OExchangeRates]
		WHERE RCurrencyCode] = @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(ICurrencyCode]) FROM OExchangeRates])
DECLARE @2 as INT = (SELECT MAX(ACurrencyCode]) FROM OExchangeRates])
DECLARE @Temp TABLE 
(
	/CurrencyCode] int,
	/ExchangeRate] decimal(38,3),
	/FromDate] datetime,
	/ToDate] datetime
)
INSERT @Temp EXEC EspExchangeRates]
@iCurrencyCode = @1,
@iMaxCurrencyCode = @2
SELECT CCurrencyCode],
	/ExchangeRate],
	/DateValue] AS ADate] 
FROM @Temp
LEFT JOIN IDate_V] AS D ON OFromDate] <= D. DateValue] AND NToDate] > 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