SQL temporal table logic in Alteryx data flow

[narula8]

Hello Experts,

I am trying to replicate temporal table scenario in Alteryx. Any advice would be appreciated.

A system-versioned temporal table is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (that is, the database engine).

SQL Code :

CREATE TABLE dbo.Employee (
[EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
[Name] NVARCHAR(100) NOT NULL,
[Position] VARCHAR(100) NOT NULL,
[Department] VARCHAR(100) NOT NULL,
[Address] NVARCHAR(1024) NOT NULL,
[AnnualSalary] DECIMAL(10, 2) NOT NULL,
[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

[caltang]

That’s something new. Have you tried the In-DB tool palette to achieve what you want? I think there’s a new feature coming soon that allows you to access a “Free SQL tool”. 

Have to wait for the product release.