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

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

SELECT 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",
	'1' 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.[Invoice Date] AS "Datum",
	T3."wochentage id" AS "Wochentage Id",
	T2."work_duration" AS "Soll-Zeit",
	T3.[Invoice Date] 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 "data"."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 T1.employee_number = T5.Employee_ID
WHERE (
		(
			(
				(T3."arbeitstag mofr" = 1)
				AND (T2."dayofweek" = T3."wochentage id")
				)
			AND (T3.[Invoice Date] >= 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