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",
	   '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."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",
	   '1' 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