C11/System/LOCOSOFT/IQD/Zeit/Zeit_Stempelungen_neu.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",
	T2."employee_number" AS "Employee Number_Times",
	T2."order_number" AS "Order Number_Times",
	T2."start_time" AS "Start Time_Times",
	T2."type" AS "Type_Times",
	T3."Order Number" AS "Order Positions_Times",
	T2."end_time" AS "End Time_Times",
	T2."exact_duration_seconds" / 60 AS "Duration Minutes_Times",
	T4."type" AS "Type_Time_Types",
	T4."description" AS "Description_Time_Types",
	T2."start_time" AS "Datum",
	'1' AS "Hauptbetrieb",
	'0' + (rtrim((((T1."subsidiary"))))) AS "Standort",
	(rtrim((((T1."employee_number"))))) + ' - ' + T1."name" AS "Monteur",
	CASE 
		WHEN (T4."type" = 1)
			THEN ((T2."exact_duration_seconds" / 60) / 60)
		ELSE (0)
		END AS "Anwesenheit",
	CASE 
		WHEN (T4."type" = 2)
			THEN ((T2."exact_duration_seconds" / 60) / 60)
		ELSE (0)
		END AS "produktiv",
	CASE 
		WHEN (T4."type" = 1)
			THEN ((T2."exact_duration_seconds" / 60) / 60)
		ELSE (0)
		END AS "unproduktiv",
	CASE 
		WHEN (T2."type" = 2)
			THEN ('produktiv')
		ELSE ('unproduktiv')
		END AS "Zeitkategorie",
	'' AS "Zeitkategorie2",
	(T2."exact_duration_seconds" / 60) / 60 AS "Ges. Std.",
	T2."order_number" AS "Order Number",
	(datepart(weekday, T2."start_time")) AS "Wochentag_Datum",
	T1."is_flextime" AS "Is Flextime_Employees",
	T1."break_time_registration" AS "Break Time Registration_Employees"
FROM "dbo"."employees" T1,
	(
		(
			"dbo"."times" T2 LEFT JOIN "dbo"."time_types" T4 ON T2."type" = T4."type"
			) LEFT JOIN "ims"."offene_Auftraege_Ums_ben_AW_fuer_Monteure" T3 ON T3."order_number" = T2."order_number"
		)
WHERE (T1."employee_number" = T2."employee_number")
	-- order by "Employee Number_Employees" asc,"Start Time_Times" asc,"Order Number_Times" asc