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

SELECT DISTINCT "Order Number" AS "Order Number",
	"Labour Type" AS "Labour Type",
	"Code_Labour_Types" AS "Code_Labour_Types",
	"Description_Labour_Types" AS "Description_Labour_Types",
	"Order Number_Labours" AS "Order Number_Labours",
	"Subsidiary_Labours" AS "Subsidiary_Labours",
	"Invoice Type_Labours" AS "Invoice Type_Labours",
	"Mechanic No_Labours" AS "Mechanic No_Labours",
	"AW/Std." AS "AW/Std.",
	"Zeitkategorie" AS "Zeitkategorie",
	"Zeitkategorie2" AS "Zeitkategorie2",
	"Employee Number_Employee" AS "Employee Number_Employee",
	"Name_Employee" AS "Name_Employee",
	"Monteur" AS "Monteur",
	"Invoice Date_Invoices" AS "Invoice Date_Invoices",
	"Datum" AS "Datum",
	"Duration Minutes_Times" AS "Duration Minutes_Times",
	"ben. Std." AS "ben. Std.",
	"Invoice Number" AS "Invoice Number",
	"Is Canceled" AS "Is Canceled",
	"Cancelation Number" AS "Cancelation Number",
	"Cancelation Date" AS "Cancelation Date",
	"Start Time" AS "Start Time",
	"End Time" AS "End Time",
	MIN("Invoice Date_Invoices") OVER (
		PARTITION BY "Order Number",
		"Mechanic No_Labours"
		) AS "Min_Invoice_Date"
FROM (
	SELECT "Order Number",
		'' AS "Labour Type",
		'' AS "Code_Labour_Types",
		'' AS "Description_Labour_Types",
		"Order Number_Labours",
		"Subsidiary_Labours",
		0 AS "Invoice Type_Labours",
		"Mechanic No_Labours",
		10 AS "AW/Std.",
		'ben. Std.' AS "Zeitkategorie",
		'' AS "Zeitkategorie2",
		"Employee Number_Employee",
		"Name_Employee",
		"Monteur",
		"Invoice Date_Invoices",
		"Invoice Date_Invoices" AS "Datum",
		"Duration Minutes_Times",
		"ben. Std.",
		0 AS "Invoice Number",
		"Is Canceled",
		"Cancelation Number",
		"Cancelation Date",
		"Start Time",
		"End Time",
		MIN("Invoice Date_Invoices") OVER (
			PARTITION BY "Order Number",
			"Mechanic No_Labours"
			) AS c26
	FROM (
		SELECT T2."mechanic_no" AS "Mechanic No_Labours",
			T1."order_number" AS "Order Number",
			T5."end_time" AS "End Time",
			T5."start_time" AS "Start Time",
			T4."cancelation_date" AS "Cancelation Date",
			T4."cancelation_number" AS "Cancelation Number",
			T4."is_canceled" AS "Is Canceled",
			(T5."exact_duration_seconds" / 60) / 60 AS "ben. Std.",
			T5."exact_duration_seconds" / 60 AS "Duration Minutes_Times",
			T4."invoice_date" AS "Invoice Date_Invoices",
			(rtrim((((T3."employee_number"))))) + ' - ' + T3."name" AS "Monteur",
			T3."name" AS "Name_Employee",
			T3."employee_number" AS "Employee Number_Employee",
			'0' + (rtrim((((T3."subsidiary"))))) AS "Subsidiary_Labours",
			T2."order_number" AS "Order Number_Labours"
		FROM "LOCOSOFT"."dbo"."times" T5,
			(
				"dbo"."invoices" T4 LEFT JOIN (
					(
						"dbo"."order_positions" T1 LEFT JOIN "dbo"."labours" T2 ON T1."order_number" = T2."order_number"
						) LEFT JOIN "dbo"."employees" T3 ON T2."mechanic_no" = T3."employee_number"
					) ON (T2."invoice_number" = T4."invoice_number")
					AND (T2."invoice_type" = T4."invoice_type")
				)
		WHERE (
				(T2."order_number" = T5."order_number")
				AND (T2."mechanic_no" = T5."employee_number")
				)
			AND (((convert(FLOAT, T2."time_units"))) <> 0)
		) D2
	) D1
WHERE (c26 = "Invoice Date_Invoices")
	-- order by "Order Number" asc,"Mechanic No_Labours" asc,"Start Time" asc