data_mapping for COMM_DAILY_DETAIL_HIST.sql

-- Data Mapping for COMM_DAILY_DETAIL_HIST

-- backup table
SELECT *
INTO COMMWEB.COMM_DAILY_DETAIL_HIST_20240424
FROM COMMWEB.COMM_DAILY_DETAIL_HIST;

-- change X_ATTRIB1 to DATE_ROW_ID
EXEC sp_rename 'COMMWEB.COMM_DAILY_DETAIL_HIST.X_ATTRIB1',  'DATE_ROW_ID', 'COLUMN'

-- change datatype to integer
ALTER TABLE [COMMWEB].COMM_DAILY_DETAIL_HIST  
ALTER COLUMN [DATE_ROW_ID] INT

-- fk relation to Calendar.ROW_ID table
ALTER TABLE [COMMWEB].COMM_DAILY_DETAIL_HIST
ADD CONSTRAINT FK_COMM_DAILY_DETAIL_HIST_DATE_ROW_ID FOREIGN KEY ([DATE_ROW_ID])
REFERENCES [RWL].[CALENDAR] ([ROW_ID]);

-- Updating DATE_ROW_ID with values from TRAN_PROCESS_DATE_TS
update [TempDataLake].[COMMWEB].COMM_DAILY_DETAIL_HIST
set [DATE_ROW_ID] = CAST(YEAR(TRAN_PROCESS_DATE_TS) AS varchar(4)) + CAST(RIGHT(FORMAT(MONTH(TRAN_PROCESS_DATE_TS), '00'), 2) AS varchar(2)) + CAST(RIGHT(FORMAT(DAY(TRAN_PROCESS_DATE_TS), '00'), 2) AS varchar(2))
FROM [TempDataLake].[COMMWEB].COMM_DAILY_DETAIL_HIST;

-- change X_ATTRIB2 to IA_ROW_ID
EXEC sp_rename 'COMMWEB.COMM_DAILY_DETAIL_HIST.X_ATTRIB2',  'IA_ROW_ID', 'COLUMN'

-- change datatype to integer
ALTER TABLE [COMMWEB].COMM_DAILY_DETAIL_HIST  
ALTER COLUMN [IA_ROW_ID] INT

-- fk relation to  COMMWEB.COMM_IA_CODE.IA_CODE table
ALTER TABLE [COMMWEB].COMM_DAILY_DETAIL_HIST
ADD CONSTRAINT FK_COMM_DAILY_DETAIL_HIST_IA_ROW_ID FOREIGN KEY ([IA_ROW_ID])
REFERENCES [COMMWEB].[COMM_IA_CODE] ([ROW_ID]);

-- Updating [IA_ROW_ID] with values from TRAN_PROCESS_DATE_TS
UPDATE [COMMWEB].COMM_DAILY_DETAIL_HIST
SET IA_ROW_ID = IC.ROW_ID
FROM [COMMWEB].COMM_DAILY_DETAIL_HIST AS DS  
INNER JOIN [COMMWEB].[COMM_IA_CODE] AS IC ON IC.IA_CODE = DS.IA_CODE;


-- UNIT TESTING for [COMMWEB].[COMM_DAILY_DETAIL_HIST]
-- where DATE_ROW_ID <> TRAN_PROCESS_DATE
select * 
from  [COMMWEB].COMM_DAILY_DETAIL_HIST
where DATE_ROW_ID <> (CAST(YEAR(TRAN_PROCESS_DATE_TS) AS varchar(4)) + CAST(RIGHT(FORMAT(MONTH(TRAN_PROCESS_DATE_TS), '00'), 2) AS varchar(2)) + CAST(RIGHT(FORMAT(DAY(TRAN_PROCESS_DATE_TS), '00'), 2) AS varchar(2)))

SELECT *
FROM [COMMWEB].COMM_DAILY_DETAIL_HIST AS d
LEFT JOIN RWL.CALENDAR AS c
ON d.DATE_ROW_ID = c.ROW_ID
WHERE d.DATE_ROW_ID <> c.ROW_ID OR c.ROW_ID IS NULL; 

--	where IA_ROW_ID not in COMM_IA_CODE.ROW_ID
SELECT *
FROM [COMMWEB].COMM_DAILY_DETAIL_HIST AS d
LEFT JOIN [COMMWEB].[COMM_IA_CODE] AS c
ON d.IA_ROW_ID = c.ROW_ID
WHERE d.IA_ROW_ID <> c.ROW_ID OR c.ROW_ID IS NULL;