C11/System/OPTIMA/IQD/Zeiten/Auftraege_Mont_SPP_1340_benutzte_AW.sql

SELECT T1."ORDER_NUMBER" AS "Order Number",
	T1."INVOICE_DATE" AS "Datum",
	T1."CLIENT_DB" AS "Hauptbetrieb",
	(substring(T1."DEPARTMENT", 2, 1)) AS "Marke",
	(substring(T1."DEPARTMENT", 4, 1)) AS "Kostenstelle",
	T2."Zuordnung" AS "Umsatzart",
	(rtrim(T3."PROFILE_CODE")) + ' - ' + T4."SEL_NAME" AS "Monteur",
	T5."EDITED_USED_TIME_INT" AS "benutzte Zeit (Auftrag)",
	T6."WORK_LEADER_GROUP" AS "Work Leader Group",
	T6."WORKLEADER_TEXT" AS "Workleader Text",
	T6."WORK_LEADER_GROUP" + ' - ' + T6."WORKLEADER_TEXT" AS "Monteur_Gruppe",
	'ben. Zeit' AS "Activity_Codes_Group1",
	'' AS "Activity_Codes_Group2",
	'' AS "Activity_Desc",
	T7."Hauptbetrieb_ID" AS "Hauptbetrieb Id",
	T7."Hauptbetrieb_Name" AS "Hauptbetrieb Name",
	T7."Standort_ID" AS "Standort Id",
	T7."Standort_Name" AS "Standort Name",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 4)
			THEN ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + T1."MODEL_TEXT" + ' - ' + T1."NAME")
		ELSE NULL
		END AS "Order Number_Mont"
FROM (
	(
		(
			"OPTIMA"."import"."ORDER_HEADER" T1 LEFT JOIN "OPTIMA"."data"."GC_Umsatzart" T2 ON (T2."Client_DB" = T1."CLIENT_DB")
				AND (T2."Geschaeftsbuchungsgruppe" = T1."CUSTOMER_GROUP")
			) LEFT JOIN "OPTIMA"."import"."ORDER_LINE" T8 ON (T1."ORDER_NUMBER" = T8."ORDER_NUMBER")
			AND (T1."CLIENT_DB" = T8."CLIENT_DB")
		) LEFT JOIN "OPTIMA"."data"."GC_Department" T7 ON (left(T1."DEPARTMENT", 2)) = T7."Standort"
	),
	(
		(
			"OPTIMA"."import"."APPORTIONED_USED_TIME" T5 LEFT JOIN "OPTIMA"."import"."VPP43" T4 ON (T4."SELLER_CODE" = T5."PROFILE_CODE")
				AND (T4."CLIENT_DB" = T5."CLIENT_DB")
			) LEFT JOIN "OPTIMA"."import"."PROFILE" T3 ON (T3."PROFILE_CODE" = T4."SELLER_CODE")
			AND (T3."CLIENT_DB" = T4."CLIENT_DB")
		),
	(
		"OPTIMA"."import"."EMPLOYEE" T9 LEFT JOIN "OPTIMA"."import"."VPP91" T6 ON (T9."WORK_LEADER_GROUP_ID" = T6."WORK_LEADER_GROUP")
			AND (T9."CLIENT_DB" = T6."CLIENT_DB")
		)
WHERE (
		(
			(T8."ORDER_NUMBER" = T5."ORDER_NUMBER")
			AND (T8."UNIQUE_IDENT" = T5."ORDER_LINE_ID")
			)
		AND (T8."CLIENT_DB" = T5."CLIENT_DB")
		)
	AND (
		(T9."PERSON_ID" = T3."PERSON_ID")
		AND (T9."CLIENT_DB" = T3."CLIENT_DB")
		)
	AND (
		(T1."INVOICE_DATE" >= convert(DATETIME, '2020-01-01 00:00:00.000'))
		AND (T1."STATUS" IN ('35', '37', '39', '47', '48', '49', '50', '51', '52', '36', '34'))
		)