C11/System/OPTIMA/IQD/Zeiten_Vorlage/Monteur_neu_Zeiterf_neu.sql

SELECT "Unique Ident" AS "Unique Ident",
	"Activity Code" AS "Activity Code",
	"Profile Code" AS "Profile Code",
	"Handler" AS "Handler",
	"Function Code" AS "Function Code",
	"Program" AS "Program",
	"Transact Date Long" AS "Transact Date Long",
	"Start Punch Function" AS "Start Punch Function",
	"Start Punch Program" AS "Start Punch Program",
	"End Punch Function" AS "End Punch Function",
	"End Punch Program" AS "End Punch Program",
	"Done For Department" AS "Done For Department",
	"Done For Work Leader" AS "Done For Work Leader",
	"Ended Punch" AS "Ended Punch",
	"Order Number" AS "Order Number",
	"Line Number" AS "Line Number",
	"Start Date Time" AS "Start Date Time",
	"End Date Time" AS "End Date Time",
	"Tmcs Idle Punch" AS "Tmcs Idle Punch",
	"Duration Int" AS "Duration Int",
	"Used Time Int" AS "Used Time Int",
	"Wage Extracted" AS "Wage Extracted",
	"Punch Remark Code" AS "Punch Remark Code",
	"Remark Accepted" AS "Remark Accepted",
	"Punch Period Start Id" AS "Punch Period Start Id",
	"Conv Flag" AS "Conv Flag",
	"Seller Code" AS "Seller Code",
	"Sel Name" AS "Sel Name",
	"Sel Department" AS "Sel Department",
	"Sel First Name" AS "Sel First Name",
	"Sel Family Name" AS "Sel Family Name",
	"Work Leader Group" AS "Work Leader Group",
	"Workleader Text" AS "Workleader Text",
	"Activity Code_Activity" AS "Activity Code_Activity",
	"Activity Description" AS "Activity Description",
	"Present_Activity" AS "Present_Activity",
	"Anwesenheit" AS "Anwesenheit",
	"Nacharbeit" AS "Nacharbeit",
	"Leerlauf/Wartezeit" AS "Leerlauf/Wartezeit",
	"Prob.fahrt/Endkontr." AS "Prob.fahrt/Endkontr.",
	"GW-Bewertung" AS "GW-Bewertung",
	"Unprod. Anwes." AS "Unprod. Anwes.",
	"Hilfslohn_" AS "Hilfslohn_",
	"Schulung intern" AS "Schulung intern",
	"Schulung extern" AS "Schulung extern",
	"Betriebsrat_" AS "Betriebsrat_",
	"Krank" AS "Krank",
	"Arzt_" AS "Arzt_",
	"Urlaub" AS "Urlaub",
	"Sonderurlaub" AS "Sonderurlaub",
	"Feiertag" AS "Feiertag",
	"zu sp�t" AS "zu sp�t",
	"�berstunden" AS "�berstunden",
	"Order Number_Auftrag" AS "Order Number_Auftrag",
	"Customer Group" AS "Customer Group",
	"Umsatzart" AS "Umsatzart",
	"Extern" AS "Extern",
	"GWL" AS "GWL",
	"Intern" AS "Intern",
	"produktiv" AS "produktiv",
	RSUM("produktiv") AS "Summe  (produktiv) Nr.1",
	"unproduktiv" AS "unproduktiv",
	RSUM("unproduktiv") AS "Summe  (unproduktiv) Nr.1",
	"abwesend" AS "abwesend",
	"Monteur_Gruppe_ori" AS "Monteur_Gruppe_ori",
	"Datum" AS "Datum",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Department Type Id" AS "Department Type Id",
	"Description" AS "Description",
	"Standort" AS "Standort",
	"Kostenstelle" AS "Kostenstelle",
	"Monteur_ori" AS "Monteur_ori",
	"Tag beenden_" AS "Tag beenden_",
	"Berufsschule" AS "Berufsschule",
	"unbez. Abwes._" AS "unbez. Abwes._",
	"Work Leader Group Id" AS "Work Leader Group Id",
	"Reifenlager_" AS "Reifenlager_",
	"Waschanlage_" AS "Waschanlage_",
	"Sondereinsatz lt. WL_" AS "Sondereinsatz lt. WL_",
	"Pause" AS "Pause",
	"Abzug T390" AS "Abzug T390",
	"Monteur_Gruppe" AS "Monteur_Gruppe",
	"Monteur" AS "Monteur"
FROM (
	SELECT T1."UNIQUE_IDENT" AS "Unique Ident",
		T1."ACTIVITY_CODE" AS "Activity Code",
		T1."PROFILE_CODE" AS "Profile Code",
		T1."HANDLER" AS "Handler",
		T1."FUNCTION_CODE" AS "Function Code",
		T1."PROGRAM" AS "Program",
		T1."TRANSACT_DATE_LONG" AS "Transact Date Long",
		T1."START_PUNCH_FUNCTION" AS "Start Punch Function",
		T1."START_PUNCH_PROGRAM" AS "Start Punch Program",
		T1."END_PUNCH_FUNCTION" AS "End Punch Function",
		T1."END_PUNCH_PROGRAM" AS "End Punch Program",
		T1."DONE_FOR_DEPARTMENT" AS "Done For Department",
		T1."DONE_FOR_WORK_LEADER" AS "Done For Work Leader",
		T1."ENDED_PUNCH" AS "Ended Punch",
		T1."ORDER_NUMBER" AS "Order Number",
		T1."LINE_NUMBER" AS "Line Number",
		T1."START_DATE_TIME" AS "Start Date Time",
		T1."END_DATE_TIME" AS "End Date Time",
		T1."TMCS_IDLE_PUNCH" AS "Tmcs Idle Punch",
		T1."DURATION_INT" AS "Duration Int",
		T1."USED_TIME_INT" AS "Used Time Int",
		T1."WAGE_EXTRACTED" AS "Wage Extracted",
		T1."PUNCH_REMARK_CODE" AS "Punch Remark Code",
		T1."REMARK_ACCEPTED" AS "Remark Accepted",
		T1."PUNCH_PERIOD_START_ID" AS "Punch Period Start Id",
		T1."CONV_FLAG" AS "Conv Flag",
		T2."SELLER_CODE" AS "Seller Code",
		T2."SEL_NAME" AS "Sel Name",
		T2."SEL_DEPARTMENT" AS "Sel Department",
		T2."SEL_FIRST_NAME" AS "Sel First Name",
		T2."SEL_FAMILY_NAME" AS "Sel Family Name",
		T3."WORK_LEADER_GROUP" AS "Work Leader Group",
		T3."WORKLEADER_TEXT" AS "Workleader Text",
		T4."ACTIVITY_CODE" AS "Activity Code_Activity",
		T4."ACTIVITY_DESCRIPTION" AS "Activity Description",
		T4."PRESENT" AS "Present_Activity",
		CASE 
			WHEN (T4."PRESENT" = 1)
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Anwesenheit",
		CASE 
			WHEN (T1."ACTIVITY_CODE" = '2110')
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Nacharbeit",
		CASE 
			WHEN (T1."ACTIVITY_CODE" = '2000')
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Leerlauf/Wartezeit",
		CASE 
			WHEN (T1."ACTIVITY_CODE" = '1130')
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Prob.fahrt/Endkontr.",
		CASE 
			WHEN (T1."ACTIVITY_CODE" = '2140')
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "GW-Bewertung",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('1000', '2999', '4999', '2998', '1999', '2210', '2220', '2230', '2240'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Unprod. Anwes.",
		0 AS "Hilfslohn_",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('2910'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Schulung intern",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('3910'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Schulung extern",
		0 AS "Betriebsrat_",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('5300', '3020'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Krank",
		0 AS "Arzt_",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('5100'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Urlaub",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('5200'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Sonderurlaub",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('5500'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Feiertag",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('4010'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "zu sp�t",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('4200'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "�berstunden",
		T5."ORDER_NUMBER" AS "Order Number_Auftrag",
		T5."CUSTOMER_GROUP" AS "Customer Group",
		CASE 
			WHEN (
					(T5."CUSTOMER_GROUP" BETWEEN '10' AND '59')
					OR (T5."CUSTOMER_GROUP" LIKE '7%')
					)
				THEN ('extern')
			WHEN (T5."CUSTOMER_GROUP" LIKE '6%')
				THEN ('GWL')
			WHEN (
					(
						(T5."CUSTOMER_GROUP" LIKE '9%')
						OR (T5."PMT_TERM" = 'IN')
						)
					OR ((left(T5."CUSTOMER_GROUP", 1)) BETWEEN 'A' AND 'Z')
					)
				THEN ('intern')
			ELSE NULL
			END AS "Umsatzart",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('1010', '1020', '1030'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Extern",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('1050'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "GWL",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('1040'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Intern",
		(
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('1010', '1020', '1030'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('1050'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('1040'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) AS "produktiv",
		(
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('1000', '2999', '4999', '2998', '1999', '2210', '2220', '2230', '2240'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" = '2110')
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" = '2000')
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('2910'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" = '2140')
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" = '1130')
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) AS "unproduktiv",
		(
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('3910'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('5300', '3020'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('5100'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('5200'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('3920'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('5500'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('996'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('4010'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T1."ACTIVITY_CODE" IN ('4090'))
					THEN (T1."USED_TIME_INT")
				ELSE (0)
				END
			) AS "abwesend",
		T3."WORK_LEADER_GROUP" + ' - ' + T3."WORKLEADER_TEXT" AS "Monteur_Gruppe_ori",
		((T1."START_DATE_TIME")) AS "Datum",
		'1' AS "Hauptbetrieb",
		T6."DEPARTMENT_TYPE_ID" AS "Department Type Id",
		T6."DESCRIPTION" AS "Description",
		(left(T6."DEPARTMENT_TYPE_ID", 2)) AS "Standort",
		(substring(T6."DEPARTMENT_TYPE_ID", 4, 1)) AS "Kostenstelle",
		T2."SEL_NAME" AS "Monteur_ori",
		0 AS "Tag beenden_",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('3920'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Berufsschule",
		0 AS "unbez. Abwes._",
		T7."WORK_LEADER_GROUP_ID" AS "Work Leader Group Id",
		0 AS "Reifenlager_",
		0 AS "Waschanlage_",
		0 AS "Sondereinsatz lt. WL_",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('4090'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Pause",
		CASE 
			WHEN (T1."ACTIVITY_CODE" IN ('996'))
				THEN (T1."USED_TIME_INT")
			ELSE (0)
			END AS "Abzug T390",
		(T3."WORK_LEADER_GROUP" + ' - ' + T3."WORKLEADER_TEXT") AS "Monteur_Gruppe",
		T1."PROFILE_CODE" + ' - ' + T2."SEL_NAME" AS "Monteur"
	FROM "deop06"."dbo"."EMPLOYEE" T7,
		"deop06"."dbo"."vPP91" T3,
		(
			(
				(
					(
						(
							"deop06"."dbo"."PUNCH" T1 LEFT JOIN "deop06"."dbo"."vPP43" T2 ON T1."PROFILE_CODE" = T2."SELLER_CODE"
							) LEFT JOIN "deop06"."dbo"."PROFILE" T8 ON T2."SELLER_CODE" = T8."PROFILE_CODE"
						) LEFT JOIN "deop06"."dbo"."ACTIVITY" T4 ON T1."ACTIVITY_CODE" = T4."ACTIVITY_CODE"
					) LEFT JOIN "deop06"."dbo"."ORDER_HEADER" T5 ON T5."ORDER_NUMBER" = T1."ORDER_NUMBER"
				) LEFT JOIN "deop06"."dbo"."DEPARTMENT_TYPE" T6 ON T2."SEL_DEPARTMENT" = T6."DEPARTMENT_TYPE_ID"
			)
	WHERE (T7."PERSON_ID" = T8."PERSON_ID")
		AND (T3."WORK_LEADER_GROUP" = T7."WORK_LEADER_GROUP_ID")
		AND (
			(T1."TRANSACT_DATE_LONG" >= convert(DATETIME, '2014-01-01 00:00:00.000'))
			AND (((T3."WORK_LEADER_GROUP" + ' - ' + T3."WORKLEADER_TEXT")) IN ('1000 - Kundendienst Meister', '1100 - Kundendienst                  ', '1110 - Kundendienst Azubi            ', '1200 - Karosserie                    ', '1210 - Karosserie Azubi              ', '1300 - Lack                          ', '1310 - Lack Azubi                    ', '2000 - Kundendienst Meister', '2100 - Kundendienst Freystadt        ', '2110 - Kundendienst Azubi Freystadt  ', '2200 - Karosserie Freystadt          ', '2210 - Karosserie Azubi Freystadt    ', '2300 - Lackiererei Freystadt         ', '3000 - Kundendienst Meister', '3100 - Kundendienst Dietfurt         ', '3110 - Kundendienst Azubi Dietfurt   ', '3200 - Karosserie Dietfurt           ', '3210 - Karosserie Azubi Dietfurt     ', '3300 - Lackiererei Dietfurt          '))
			)
		-- order by "Transact Date Long" asc
	) D1