I am trying to build a transformation process in TX based on the following SQL code.
------------------------------------------
SELECT distinct [BusinessEntityID]
,B.MonthKey ,[StartDate] ,[EndDate]
,[DepartmentName]
FROM [TX_DSA].[dbo].[EmployeeDepartmentHistory] A join dbo.Calendar B ON B.Date >= A.StartDate where b.DateValue >= a.StartDate AND (a.EndDate is null or a.EndDate <= b.DateValue)
Anyway I have done the same for that exact table based on experience I got from that guide.
First I have this stored procedure
CREATE PROCEDURE [DSA].[spPayRates] @iBEntity int = NULL, @iMaxBEntity int = NULL AS BEGIN CREATE TABLE #TempTable ( [BusinessEntityID] int, [RateChangeDate] datetime, [RateChangeDateEnd] datetime, [Rate] decimal(38,4), [PayFrequency] int ) WHILE(@iBEntity <= @iMaxBEntity) BEGIN INSERT INTO #TempTable ( [BusinessEntityID], [RateChangeDate], [RateChangeDateEnd], [Rate], [PayFrequency] ) SELECT [BusinessEntityID], [RateChangeDate], ISNULL(LEAD([RateChangeDate]) OVER (ORDER BY [BusinessEntityID]),(SELECT MAX([DateValue]) FROM [DSA].[Date])) AS [RateChangeDateEnd], [Rate], [PayFrequency] FROM [HR].[EmployeePayHistory] WHERE [BusinessEntityID] = @iBEntity
SET @iBEntity = @iBEntity + 1 END SELECT * FROM #TempTable DROP TABLE #TempTable END
Secondly I have a Custom Table Insert in a table.
DECLARE @1 as INT = (SELECT MIN([BusinessEntityID]) FROM [HR].[EmployeePayHistory]) DECLARE @2 as INT = (SELECT MAX([BusinessEntityID]) FROM [HR].[EmployeePayHistory]) DECLARE @Temp TABLE ( [BusinessEntityID] int, [RateChangeDate] datetime, [RateChangeDateEnd] datetime, [Rate] decimal(38,4), [PayFrequency] int ) INSERT @Temp EXEC [DSA].[spPayRates] @iBEntity = @1, @iMaxBEntity = @2 SELECT [BusinessEntityID], [DateValue] AS [Date], CASE WHEN [PayFrequency] = 1 THEN [Rate]/30.4375 ELSE [Rate]/14 END AS [Rate], [PayFrequency] FROM @Temp LEFT JOIN [DSA].[Date] AS D ON [RateChangeDate] <= D.[DateValue] AND [RateChangeDateEnd] > D.[DateValue]
This 2 part script is based on the fact that you don't always gets a straight array of dates and if I did not do this it would mix them up.