ETL_DWH_LOCOSOFT/schema/GC_ETL_LOCOSOFT/views/transform.Zeit_Sollzeit.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform].[Zeit_Sollzeit] AS

SELECT convert(varchar, [T1].[employee_number]) AS [Employee_ID_imr]
     , [T1]."employee_number" AS "Employee Number_Employees"
     , [T1]."name" AS "Name_Employees"
     , [T1]."initials" AS "Initials_Employees"
     , [T1]."mechanic_number" AS "Mechanic Number_Employees"
     , [T1]."salesman_number" AS "Salesman Number_Employees"
     , [T1]."employment_date" AS "Employment Date_Employees"
     , [T1]."termination_date" AS "Termination Date_Employees"
     , [T1]."leave_date" AS "Leave Date_Employees"
     , '1' AS "Hauptbetrieb_imr"
     , convert(varchar, [T1].[subsidiary]) AS "Standort_imr"
     , '1' AS "Hauptbetrieb"
     , convert(varchar, [T1].[subsidiary]) AS "Standort"
     , (rtrim(((([T1]."employee_number"))))) + ' - ' + [T1]."name" AS "Monteur"
     , 'Sollzeit' AS "Zeitkategorie"
     , '' AS "Zeitkategorie2"
     , [T2]."is_latest_record" AS "Is Latest Record"
     , [T2]."employee_number" AS "Employee Number"
     , [T2]."validity_date" AS "Validity Date"
     , [T2]."dayofweek" AS "Dayofweek"
     , [T2]."work_duration" AS "Work Duration"
     , [T2]."worktime_start" AS "Worktime Start"
     , [T2]."worktime_end" AS "Worktime End"
     , [T3].[Datum] AS "Datum"
     , [T3]."wochentage id" AS "Wochentage Id"
     , [T2]."work_duration" AS "Soll-Zeit"
     , [T3].[Datum] AS "Invoice Date"
     , [T4].[Hauptbetrieb_ID] AS "Hauptbetrieb_ID"
     , [T4].[Hauptbetrieb_Name] AS "Hauptbetrieb_Name"
     , [T4].[Standort_ID] AS "Standort_ID"
     , [T4].[Standort_Name] AS "Standort_Name"
     , [T5].[Zuordnung_Produktiv] AS "Monteur_Gruppe"
     , [T5].[Monteur_Ebene_1] AS "Monteur_Gruppe_2"
     , 'Sollzeit' AS "Activity_Codes_Group1"
     , '' AS "Activity_Codes_Group2"
     , 'Sollzeit' AS "Activity_Desc"
FROM [LOCOSOFT]."dbo"."employees" [T1]
         INNER JOIN "LOCOSOFT"."dbo"."employees_worktimes" [T2] ON ([T1]."employee_number" = [T2]."employee_number")
         INNER JOIN "transform_imr"."current_date_Prognose" [T3] ON [T3].[Bundeslaender Id] = '10'
         LEFT JOIN [data].[GC_Department] [T4] ON convert(varchar, [T1].[subsidiary]) = [T4].[Standort] AND [T4].[Hauptbetrieb] = '1'
         LEFT JOIN [data].[GC_Mitarbeiter] [T5] ON convert(varchar, [T1].[employee_number]) = [T5].[Employee_ID]
WHERE ([T3]."arbeitstag mofr" = 1)
  AND ([T2]."dayofweek" = [T3]."wochentage id")
  AND ([T3].[Datum] >= convert(datetime, '2023-05-01T00:00:00.000'))
  AND ([T2]."is_latest_record" = 1)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO