C11/System/LOCOSOFT/IQD/Zeit/Zeit_Sollzeit.sql

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",
	'01' 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",
	'1' AS "Hauptbetrieb_ID",
	'AH Lutz' AS "Hauptbetrieb_Name",
	'01' AS "Standort_ID",
	'FFB' AS "Standort_Name",
	'Monteure' AS "Monteur_Gruppe",
	'Monteure' AS "Monteur_Gruppe_2",
	'Sollzeit' AS "Activity_Codes_Group1",
	'' AS "Activity_Codes_Group2",
	'Sollzeit' AS "Activity_Desc"
FROM "dbo"."employees" T1,
	"LOCOSOFT"."dbo"."employees_worktimes" T2,
	"ims"."current_date_fuer_ims" T3
WHERE (T1."employee_number" = T2."employee_number")
	AND (
		(
			(T3."arbeitstag_mofr" = 1)
			AND (T2."dayofweek" = (cinteger(nconvert(T3."wochentage_id"), 0)))
			)
		AND (T3."datum" >= convert(DATETIME, '2023-05-01 00:00:00.000'))
		)
	-- order by "Employee Number_Employees" asc,"Datum" asc