C11/System/ARI/IQD/Zeit/Time_Clock_Entry.sql

SELECT T1."Entry No_" AS "Entry No",
	T1."Employee No_" AS "Employee No",
	T1."Resource No_" AS "Resource No",
	T1."Address No_" AS "Address No",
	T1."Date" AS "Date",
	T1."Time" AS "Time",
	T1."Sorting" AS "Sorting",
	(convert(FLOAT, T1."Duration")) AS "Duration",
	T1."Task Type Code" AS "Task Type Code",
	T1."Type" AS "Type",
	T1."Linked to Entry No_" AS "Linked To Entry No",
	T1."Leaving" AS "Leaving",
	T1."Service Order No_" AS "Service Order No",
	T1."Service Job No_" AS "Service Job No",
	T1."Service Line No_" AS "Service Line No",
	T1."Labor Standard Time Type" AS "Labor Standard Time Type",
	T1."Approved" AS "Approved",
	T1."Approved by Employee No_" AS "Approved By Employee No",
	T1."Approval Timestamp" AS "Approval Timestamp",
	T1."Canceled" AS "Canceled",
	T1."Canceled by User ID" AS "Canceled By User Id",
	T1."Cancelation Timestamp" AS "Cancelation Timestamp",
	T1."Created by User ID" AS "Created By User Id",
	T1."Creation Timestamp" AS "Creation Timestamp",
	T1."Productive" AS "Productive",
	T1."Statistics Group" AS "Statistics Group",
	T1."Considered as Working" AS "Considered As Working",
	T1."Order Location Code" AS "Order Location Code",
	T1."Resource Location Code" AS "Resource Location Code",
	T1."Make Code" AS "Make Code",
	T1."Branch Code" AS "Branch Code",
	T1."Labor No_" AS "Labor No",
	T1."Description" AS "Description",
	T1."Source Code" AS "Source Code",
	CASE 
		WHEN (
				((convert(FLOAT, T1."Efficiency _")) = 0)
				AND (T2."Efficiency" <> 0)
				)
			THEN (T2."Efficiency")
		ELSE ((convert(FLOAT, T1."Efficiency _")))
		END AS "Efficiency",
	T1."Automatic" AS "Automatic",
	T1."Passed" AS "Passed",
	T3."CODE" AS "Code_Task_Type",
	T3."DESCRIPTION" AS "Description_Task_Type",
	T4."No_" AS "No_Employee_T",
	T4."Name" AS "Name_Employee_T",
	T4."Last Name" AS "Last Name_Employee_T",
	T4."First Name" AS "First Name_Employee_T",
	T4."Group No_ 1" AS "Group No 1_Employee_T",
	T4."Group No_ 2" AS "Group No 2_Employee_T",
	T4."Group No_ 3" AS "Group No 3_Employee_T",
	T4."Leaving Date" AS "Leaving Date_Employee_T",
	CASE 
		WHEN (T5."Function Code" IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI'))
			THEN ('prod. Personal')
		ELSE ('unprod. Personal')
		END AS "produktiv/unproduktiv",
	CASE 
		WHEN (T4."Last Name" = 'Silberbauer')
			THEN ('Leonhard Silberbauer')
		WHEN (T4."Last Name" = 'Liebick')
			THEN ('Lukas Jonathan Josef Liebick')
		ELSE (T4."First Name" + ' ' + T4."Last Name")
		END AS "Monteur",
	T5."Function Code" AS "Monteur_Gruppe_ori",
	(- 1 * datediff(day, (getdate()), T4."Leaving Date")) AS "Tage Heute Leaving Date",
	CASE 
		WHEN (
				(T4."Leaving Date" < (getdate()))
				AND (T4."Leaving Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
				)
			THEN ('ausgetretene Mitarbeiter')
		ELSE (T5."Function Code")
		END AS "Monteur_Gruppe_2",
	(
		(
			CASE 
				WHEN (
						(T1."Employee No_" IN ('0382', '0378'))
						AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
						)
					THEN (convert(DATE, '1900-01-01'))
				ELSE (T1."Date")
				END
			)
		) AS "Datum",
	(
		convert(DATETIME, (
				(
					(
						CASE 
							WHEN (
									(T1."Employee No_" IN ('0382', '0378'))
									AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
									)
								THEN (convert(DATE, '1900-01-01'))
							ELSE (T1."Date")
							END
						)
					)
				) - cinterval(day((
						(
							(
								CASE 
									WHEN (
											(T1."Employee No_" IN ('0382', '0378'))
											AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
											)
										THEN (convert(DATE, '1900-01-01'))
									ELSE (T1."Date")
									END
								)
							)
						)) - 1))
		) AS "Monatserster",
	(
		convert(DATETIME, eomonth((
					(
						(
							(
								CASE 
									WHEN (
											(T1."Employee No_" IN ('0382', '0378'))
											AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
											)
										THEN (convert(DATE, '1900-01-01'))
									ELSE (T1."Date")
									END
								)
							)
						)
					)))
		) AS "Monatsletzter",
	(getdate()) - 1 AS "Heute",
	CASE 
		WHEN (
				((getdate()) - 1) BETWEEN (
							(
								convert(DATETIME, (
										(
											(
												CASE 
													WHEN (
															(T1."Employee No_" IN ('0382', '0378'))
															AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
															)
														THEN (convert(DATE, '1900-01-01'))
													ELSE (T1."Date")
													END
												)
											)
										) - cinterval(day((
												(
													(
														CASE 
															WHEN (
																	(T1."Employee No_" IN ('0382', '0378'))
																	AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
																	)
																THEN (convert(DATE, '1900-01-01'))
															ELSE (T1."Date")
															END
														)
													)
												)) - 1))
								)
							) AND (
							(
								convert(DATETIME, eomonth((
											(
												(
													(
														CASE 
															WHEN (
																	(T1."Employee No_" IN ('0382', '0378'))
																	AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
																	)
																THEN (convert(DATE, '1900-01-01'))
															ELSE (T1."Date")
															END
														)
													)
												)
											)))
								)
							)
				)
			THEN (
					(
						(
							(
								CASE 
									WHEN (
											(T1."Employee No_" IN ('0382', '0378'))
											AND (T1."Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
											)
										THEN (convert(DATE, '1900-01-01'))
									ELSE (T1."Date")
									END
								)
							)
						)
					)
		ELSE NULL
		END AS "Datum Tagesbericht",
	'1' AS "Hauptbetrieb_ID",
	(left(T4."Department No_", 2)) AS "Standort",
	CASE 
		WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.')
			THEN (((convert(FLOAT, T1."Duration"))))
		ELSE (0)
		END AS "prod.",
	CASE 
		WHEN (T3."ACTIVITY_CODES_GROUP1" = 'W-var. Std.')
			THEN (((convert(FLOAT, T1."Duration"))))
		ELSE (0)
		END AS "unprod.",
	CASE 
		WHEN (T3."ACTIVITY_CODES_GROUP1" = 'W-fix Std.')
			THEN (((convert(FLOAT, T1."Duration"))))
		ELSE (0)
		END AS "Abw.",
	(
		CASE 
			WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.')
				THEN (((convert(FLOAT, T1."Duration"))))
			ELSE (0)
			END
		) * (
		(
			CASE 
				WHEN (
						((convert(FLOAT, T1."Efficiency _")) = 0)
						AND (T2."Efficiency" <> 0)
						)
					THEN (T2."Efficiency")
				ELSE ((convert(FLOAT, T1."Efficiency _")))
				END
			) / 100
		) AS "produktiv_f�r_Berechnung_LG",
	CASE 
		WHEN (T3."ACTIVITY_DESC" IN ('243 - Servicemobil'))
			THEN (((convert(FLOAT, T1."Duration"))))
		ELSE (0)
		END AS "Servicemobil",
	CASE 
		WHEN (T3."ACTIVITY_DESC" = '319 - Krankheit')
			THEN (((convert(FLOAT, T1."Duration"))))
		ELSE (0)
		END AS "krank",
	CASE 
		WHEN (T3."ACTIVITY_DESC" IN ('242 - Meistervertretung Werkstatt', '244 - Meistervertretung Serviceberater'))
			THEN (((convert(FLOAT, T1."Duration"))))
		ELSE (0)
		END AS "Meistervertr.",
	T3."ACTIVITY_DESC" AS "Activity_Desc",
	(left(T4."Department No_", 2)) AS "Standort_ID",
	(
		CASE 
			WHEN (T5."Function Code" IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI'))
				THEN ('prod. Personal')
			ELSE ('unprod. Personal')
			END
		) AS "Monteur_Gruppe",
	T4."Department No_" AS "Department No",
	T1."Service Order No_" AS "Order Number",
	T5."Task Type Group" AS "Task Type Group",
	CASE 
		WHEN (T3."ACTIVITY_DESC" = '111 - Auftrag extern AW')
			THEN (
					(
						CASE 
							WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.')
								THEN (((convert(FLOAT, T1."Duration"))))
							ELSE (0)
							END
						)
					)
		ELSE (0)
		END AS "Extern",
	CASE 
		WHEN (T3."ACTIVITY_DESC" = '150 - Auftrag intern AW')
			THEN (
					(
						CASE 
							WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.')
								THEN (((convert(FLOAT, T1."Duration"))))
							ELSE (0)
							END
						)
					)
		ELSE (0)
		END AS "Intern",
	T5."Home Page 2" AS "Home Page 2",
	(convert(VARCHAR(50), year(T5."Employment Date")) + '-' + convert(VARCHAR(50), month(T5."Employment Date")) + '-' + convert(VARCHAR(50), day(T5."Employment Date"))) AS "Employment Date"
FROM "ims"."Resource_ims" T2,
	(
		(
			(
				"Gottstein7x"."dbo"."AH Gottstein$Time Clock Entry" T1 LEFT JOIN "ims"."Zuordnung_Task_Type" T3 ON T3."CODE" = T1."Task Type Code"
				) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Employee_T" T4 ON T1."Employee No_" = T4."No_"
			) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Employee" T5 ON T1."Resource No_" = T5."No_"
		)
WHERE (T1."Resource No_" = T2."No_")
	AND (
		(
			(
				(
					(T1."Sorting" = 0)
					AND (T1."Canceled" = 0)
					)
				AND (T1."Date" >= convert(DATETIME, '2022-01-01 00:00:00.000'))
				)
			AND (
				(
					CASE 
						WHEN (T5."Function Code" IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI'))
							THEN ('prod. Personal')
						ELSE ('unprod. Personal')
						END
					) IN ('prod. Personal', 'unprod. Personal')
				)
			)
		AND (NOT T3."CODE" IN ('312'))
		)
	-- order by "Resource No" asc,"Date" asc