DROP TABLE IF EXISTS dim.Account;
CREATE TABLE dim.Account (
id INT IDENTITY(1,1) NOT NULL
,[4444] VARCHAR(10) NULL
,[4444_desc] VARCHAR(550) NULL
,aa VARCHAR(10) NULL
,aa_desc VARCHAR(550) NULL
,fund VARCHAR(10) NULL
,gl VARCHAR(10) NULL
,gl_desc VARCHAR(550) NULL
,llll VARCHAR(10) NULL
,llll_desc VARCHAR(550) NULL
,o VARCHAR(10) NULL
,obbb VARCHAR(10) NULL
,obbb_desc VARCHAR(550) NULL
,pp VARCHAR(10) NULL
,ppss VARCHAR(10) NULL
,pprr_desc VARCHAR(550) NULL
,s VARCHAR(10) NULL
,s_desc VARCHAR(550) NULL
,effective_start_datetime DATETIME2(7) NOT NULL
,effective_end_datetime DATETIME2(7) NOT NULL
,is_current BIT NOT NULL
,hash_key AS CONVERT(VARBINARY(32), HASHBYTES(‘SHA2_256’, CONCAT(
ISNULL([4444], ”), ‘||’
,ISNULL(aa, ”), ‘||’
,ISNULL(fund, ”), ‘||’
,ISNULL(gl, ”), ‘||’
,ISNULL(llll, ”), ‘||’
,ISNULL(o, ”), ‘||’
,ISNULL(obbb, ”), ‘||’
,ISNULL(pp, ”), ‘||’
,ISNULL(ppss, ”), ‘||’
,ISNULL(s, ”)))) PERSISTED
,hash_diff AS CONVERT(VARBINARY(32), HASHBYTES(‘SHA2_256’, CONCAT(
ISNULL([4444_desc], ”), ‘||’
,ISNULL(aa_desc, ”), ‘||’
,ISNULL(gl_desc, ”), ‘||’
,ISNULL(llll_desc, ”), ‘||’
,ISNULL(obbb_desc, ”), ‘||’
,ISNULL(pprr_desc, ”), ‘||’
,ISNULL(s_desc, ”)))) PERSISTED
);
CREATE UNIQUE INDEX UX_Account_current
ON dim.Account (hash_key)
INCLUDE (hash_diff)
WHERE is_current = 1;
SET IDENTITY_INSERT dim.Account ON;
INSERT INTO dim.Account (
id
,[4444]
,[4444_desc]
,aa
,aa_desc
,fund
,gl
,gl_desc
,llll
,llll_desc
,o
,obbb
,obbb_desc
,pp
,ppss
,pprr_desc
,s
,s_desc
,effective_start_datetime
,effective_end_datetime
,is_current
)
VALUES (
–1
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘Unknown’
,‘1900-01-01’
,‘9999-12-31 23:59:59.9999999’
,1
);
SET IDENTITY_INSERT dim.Account OFF;
CREATE OR ALTER PROCEDURE dim.usp_Load_Account
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
DECLARE @end DATETIME2(7) = DATEADD(MINUTE, –1, @now);
DECLARE @future DATETIME2(7) = ‘9999-12-31 23:59:59.9999999’;
DROP TABLE IF EXISTS #src;
;WITH src_cast AS (
SELECT DISTINCT
CAST([4444] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS [4444]
,CAST([4444 Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS [4444_desc]
,CAST([AA] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS aa
,CAST([AA Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS aa_desc
,CAST([Fund] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS fund
,CAST([GL] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS gl
,CAST([GL Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS gl_desc
,CAST([LLLL] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS llll
,CAST([LLLL Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS llll_desc
,CAST([O] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS o
,CAST([OBBB] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS obbb
,CAST([OBBB Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS obbb_desc
,CAST([PP] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS pp
,CAST([PPSS] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS ppss
,CAST([PPRR Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS pprr_desc
,CAST([S] AS VARCHAR(10)) COLLATE DATABASE_DEFAULT AS s
,CAST([S Desc] AS VARCHAR(550)) COLLATE DATABASE_DEFAULT AS s_desc
FROM LakehouseName.dbo.[RawTable]
)
SELECT
[4444]
,[4444_desc]
,aa
,aa_desc
,fund
,gl
,gl_desc
,llll
,llll_desc
,o
,obbb
,obbb_desc
,pp
,ppss
,pprr_desc
,s
,s_desc
,CONVERT(VARBINARY(32), HASHBYTES(‘SHA2_256’, CONCAT(
ISNULL([4444], ”), ‘||’
,ISNULL(aa, ”), ‘||’
,ISNULL(fund, ”), ‘||’
,ISNULL(gl, ”), ‘||’
,ISNULL(llll, ”), ‘||’
,ISNULL(o, ”), ‘||’
,ISNULL(obbb, ”), ‘||’
,ISNULL(pp, ”), ‘||’
,ISNULL(ppss, ”), ‘||’
,ISNULL(s, ”)))) AS hash_key
,CONVERT(VARBINARY(32), HASHBYTES(‘SHA2_256’, CONCAT(
ISNULL([4444_desc], ”), ‘||’
,ISNULL(aa_desc, ”), ‘||’
,ISNULL(gl_desc, ”), ‘||’
,ISNULL(llll_desc, ”), ‘||’
,ISNULL(obbb_desc, ”), ‘||’
,ISNULL(pprr_desc, ”), ‘||’
,ISNULL(s_desc, ”)))) AS hash_diff
INTO #src
FROM src_cast;
CREATE CLUSTERED INDEX CIX_src ON #src (hash_key);
BEGIN TRY
BEGIN TRANSACTION;
UPDATE d
SET effective_end_datetime = @end
,is_current = 0
FROM dim.Account d
INNER JOIN #src s
ON s.hash_key = d.hash_key
WHERE d.is_current = 1
AND d.id <> –1
AND s.hash_diff <> d.hash_diff;
INSERT INTO dim.Account (
[4444]
,[4444_desc]
,aa
,aa_desc
,fund
,gl
,gl_desc
,llll
,llll_desc
,o
,obbb
,obbb_desc
,pp
,ppss
,pprr_desc
,s
,s_desc
,effective_start_datetime
,effective_end_datetime
,is_current
)
SELECT
s.[4444]
,s.[4444_desc]
,s.aa
,s.aa_desc
,s.fund
,s.gl
,s.gl_desc
,s.llll
,s.llll_desc
,s.o
,s.obbb
,s.obbb_desc
,s.pp
,s.ppss
,s.pprr_desc
,s.s
,s.s_desc
,@now
,@future
,1
FROM #src s
WHERE NOT EXISTS (
SELECT 1
FROM dim.Account d
WHERE d.is_current = 1
AND d.id <> –1
AND d.hash_key = s.hash_key
AND d.hash_diff = s.hash_diff
);
UPDATE d
SET effective_end_datetime = @end
,is_current = 0
FROM dim.Account d
WHERE d.is_current = 1
AND d.id <> –1
AND NOT EXISTS (
SELECT 1
FROM #src s
WHERE s.hash_key = d.hash_key
);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
DROP TABLE IF EXISTS #src;
END;
