OLE DB Load into FactHoursByWeek in Operations

OleDbConnection

AccessMode

3

AlwaysUseDefaultCodePage

False

CommandTimeout

0

DefaultCodePage

1252

OpenRowset

OpenRowsetVariable

ParameterMapping

SqlCommand

IF 2=3
    BEGIN
    SELECT 
       CAST(0 AS INT) AS EmployeeAK
      ,CAST('aaa' AS VARCHAR(8)) AS PositionAK
      ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK  
      ,CAST(0 AS INT) AS StartDateSK
      ,CAST(0 AS INT) AS EndDateSK  
      ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
      -- Calculate the average for the OverTime hours over the number of days in the Reporting Period
      ,CAST(0 AS NUMERIC(9,2)) AS HoursWorked
     END
DECLARE @OverTimeType VARCHAR(4)
-- Find out what TimeType to use for OverTime Hours
SET @OverTimeType =
(
  SELECT CAST(DESCRIPTION AS VARCHAR(4)) AS TimeTypeAK
  FROM UTL_CODE_TABLE
  WHERE TABLE_NAME = 'DW-PARMS'
      AND CODE = 'HR-OVERTM'
      AND STATUS = 'A'
)
  -- Get the Normal and OverTime hours if we are reporting per Day from Attendance-Day
  SELECT 
       AttendDay.PERSON_ID_TD AS EmployeeAK
      ,POSITION_CODE_TD AS PositionAK
      ,CAST(
            REPLICATE('0', 9 - LEN(AttendDay.PERSON_ID_TD)) + CAST(AttendDay.PERSON_ID_TD AS VARCHAR(9))
            + 
            REPLICATE(' ', 8 - LEN(AttendDay.POSITION_CODE_TD)) + CAST(AttendDay.POSITION_CODE_TD AS VARCHAR(8))
            +  
            REPLICATE('0', 3 - LEN(AttendDay.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendDay.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3))
            AS VARCHAR(20)) AS AssignmentAK  
      ,StartDate.SundayDate AS StartDateSK
      ,EndDate.SaturdayDate AS EndDateSK
      ,CAST(
         CASE
           WHEN TimeType.OVERTIME_IND = 1 THEN AttendDay.TIME_TYPE_CODE 
           ELSE 'NORM'
         END AS VARCHAR(4)) AS TimeTypeAK
      ,CAST(ISNULL(AttendDay.HOURS_USED,0) AS NUMERIC(9,2)) AS HoursWorked
  FROM ATTENDANCE_AND_DAY_A AttendDay
  
  -- Find the Sunday before the Reporting Day
  OUTER APPLY
  (
     SELECT MAX(DateSK) AS SundayDate
     FROM EnterpriseDW.dbo.DimDate GetSun
     WHERE GetSun.DayOfWeek = 1
         AND GetSun.DateSK <= AttendDay.ATTENDANCE_DAY_DATE
  ) StartDate
  -- Find the Saturday after the Reporting Day
  OUTER APPLY
  (
     SELECT MIN(DateSK) AS SaturdayDate
     FROM EnterpriseDW.dbo.DimDate GetSat
     WHERE GetSat.DayOfWeek = 7
         AND GetSat.DateSK >= AttendDay.ATTENDANCE_DAY_DATE
  ) EndDate
   
  LEFT OUTER JOIN TIME_TYPE_A TimeType
  ON
  (
     TimeType.TIME_TYPE_CODE = AttendDay.TIME_TYPE_CODE
  )   
      
  WHERE AttendDay.ATTENDANCE_DAY_INTERNAL_ID IS NOT NULL
  AND (AttendDay.TIME_TYPE_CODE = 'NORM' OR TimeType.OVERTIME_IND = 1)
  AND AttendDay.UNPROCESSED_IND = 0

SqlCommandVariable

User::Get_HoursWorked_ReportByDay

AssignmentAK

2382

2382

EmployeeAK

2376

2376

EndDateSK

2565

2565

ErrorCode

1142

1142

ErrorColumn

1143

1143

HoursWorked

2394

2394

PositionAK

2379

2379

StartDateSK

2562

2562

TimeTypeAK

2391

2391

AssignmentAK

2381

2381

EmployeeAK

2375

2375

EndDateSK

2564

2564

HoursWorked

2393

2393

PositionAK

2378

2378

StartDateSK

2561

2561

TimeTypeAK

2390

2390