Skip to main content

Hi,

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)

----------------------------------------------------

I want intentionally to expand the grain of table A. I tried different Joins and conditional lookups but I just can't reproduce the output.

How would you solve this?

Thanks in advance

gr
Jose

Hi Jose

When you need to expand the grain of a table it's usually best to implement it as a custom view in the DSA and map the fields from there to the DW.

Best regards,
Andri Heidberg

Hi José

Have you read my guide regarding this?
https://support.timextender.com/hc/en-us/articles/115005972746-Improve-execution-times-by-splitting-exchange-rates-out-on-dates

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

ly-YoespbStwb7auR-JYlQ.png

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]

Y2TmhbY2zPViFk7SmHAHnQ.png

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.

Thank you Thomas and Andri! 

Terms & ConditionsCookie settingsAccessibility statement