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

SELECT "Employee No",
	"Datum",
	"Department No_ori",
	"Time Account No",
	"Time Account Value",
	"Ta Class (general)" AS "Ta Class (general)",
	"Ta Class (statistics)" AS "Ta Class (statistics)",
	"Ta Class (absent Days)" AS "Ta Class (absent Days)",
	"Ta Class (time Processing)" AS "Ta Class (time Processing)",
	"Ta Class (individual)" AS "Ta Class (individual)",
	"Ta Class (employee Info)" AS "Ta Class (employee Info)",
	"Ta Class (vacation Reduction)" AS "Ta Class (vacation Reduction)",
	"Ta Formatting",
	"Generating Function",
	"Record Protected",
	"No",
	"Description",
	"Description 2",
	"Zeitdauer_ori",
	"gesamt Stunden",
	"krank",
	"Wehr- /Zivildienst",
	"Sonderurlaub",
	"Berufsschule",
	0 AS "Innung Azubi",
	"Feiertag",
	"Schulung extern",
	"Urlaub",
	"W-fix Stunden",
	'1' AS "Hauptbetrieb_ID",
	"Standort",
	"First Name",
	"Last Name",
	"Monteur_Gruppe_ori",
	'' AS "Abteilung",
	'' AS "Order Number",
	"Monteur",
	"Zeitausgleich",
	"�berstunden",
	"Leaving Date",
	"Group No 1",
	"Group No 2",
	"Group No 3",
	"Tage Heute Leaving Date",
	"Monteur_Gruppe",
	"produktiv/unproduktiv",
	"Monatserster",
	"Monatsletzter",
	"Heute",
	"Datum Tagesbericht",
	"Employment Date_2",
	"Department No_2",
	"Datum_Monteurlisten",
	"Fehlzeiten genehm./ungenem.",
	"Activity Desc",
	"Monteur_Gruppe_2",
	"Standort" AS "Standort_ID",
	"Department No_2" AS "Department No",
	"Task Type Group",
	"Sollzeit",
	"abwesend",
	"Home Page 2",
	"Employment Date",
	SUM("Sollzeit") OVER (
		PARTITION BY "Datum",
		"Employee No"
		) AS "Summe Sollzeit",
	CASE 
		WHEN (
				("Time Account No" IN ('670', '671', '672', '675', '676'))
				AND (
					(
						SUM("Sollzeit") OVER (
							PARTITION BY "Datum",
							"Employee No"
							)
						) = 0
					)
				)
			THEN (0)
		ELSE (("Zeitdauer_ori"))
		END AS "Zeitdauer"
FROM (
	SELECT T1."Employee No_" AS "Employee No",
		(
			(
				CASE 
					WHEN (
							(T1."Employee No_" IN ('0382', '0378'))
							AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
							)
						THEN (convert(DATE, '1900-01-01'))
					ELSE (T1."Current Date")
					END
				)
			) AS "Datum",
		(convert(FLOAT, T1."Time Account Value")) AS "Zeitdauer_ori",
		T1."Time Account No_" AS "Time Account No",
		(convert(VARCHAR(50), year(T4."Employment Date")) + '-' + convert(VARCHAR(50), month(T4."Employment Date")) + '-' + convert(VARCHAR(50), day(T4."Employment Date"))) AS "Employment Date",
		T4."Home Page 2" AS "Home Page 2",
		CASE 
			WHEN (T1."Time Account No_" BETWEEN '299' AND '450')
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "abwesend",
		CASE 
			WHEN (T1."Time Account No_" = '1200')
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Sollzeit",
		T4."Task Type Group" AS "Task Type Group",
		T3."Department No_" AS "Department No_2",
		(left(T3."Department No_", 2)) AS "Standort",
		CASE 
			WHEN (
					(T3."Leaving Date" < (getdate()))
					AND (T3."Leaving Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
					)
				THEN ('ausgetretene Mitarbeiter')
			ELSE (T4."Function Code")
			END AS "Monteur_Gruppe_2",
		(rtrim((((T2."No_"))))) + ' - ' + T2."Description" AS "Activity Desc",
		CASE 
			WHEN (T2."No_" IN (300, 301))
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Fehlzeiten genehm./ungenem.",
		CASE 
			WHEN (
					(
						- 1 * datediff(day, (getdate()), (
								(
									(
										CASE 
											WHEN (
													(T1."Employee No_" IN ('0382', '0378'))
													AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
													)
												THEN (convert(DATE, '1900-01-01'))
											ELSE (T1."Current Date")
											END
										)
									)
								))
						) <= 93
					)
				THEN (
						(
							(
								(
									CASE 
										WHEN (
												(T1."Employee No_" IN ('0382', '0378'))
												AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
												)
											THEN (convert(DATE, '1900-01-01'))
										ELSE (T1."Current Date")
										END
									)
								)
							)
						)
			ELSE NULL
			END AS "Datum_Monteurlisten",
		T4."Employment Date" AS "Employment Date_2",
		CASE 
			WHEN (
					((getdate()) - 1) BETWEEN (
								(
									convert(DATETIME, (
											(
												(
													CASE 
														WHEN (
																(T1."Employee No_" IN ('0382', '0378'))
																AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
																)
															THEN (convert(DATE, '1900-01-01'))
														ELSE (T1."Current Date")
														END
													)
												)
											) - cinterval(day((
													(
														(
															CASE 
																WHEN (
																		(T1."Employee No_" IN ('0382', '0378'))
																		AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
																		)
																	THEN (convert(DATE, '1900-01-01'))
																ELSE (T1."Current Date")
																END
															)
														)
													)) - 1))
									)
								) AND (
								(
									convert(DATETIME, eomonth((
												(
													(
														(
															CASE 
																WHEN (
																		(T1."Employee No_" IN ('0382', '0378'))
																		AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
																		)
																	THEN (convert(DATE, '1900-01-01'))
																ELSE (T1."Current Date")
																END
															)
														)
													)
												)))
									)
								)
					)
				THEN (
						(
							(
								(
									CASE 
										WHEN (
												(T1."Employee No_" IN ('0382', '0378'))
												AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
												)
											THEN (convert(DATE, '1900-01-01'))
										ELSE (T1."Current Date")
										END
									)
								)
							)
						)
			ELSE NULL
			END AS "Datum Tagesbericht",
		(getdate()) - 1 AS "Heute",
		(
			convert(DATETIME, eomonth((
						(
							(
								(
									CASE 
										WHEN (
												(T1."Employee No_" IN ('0382', '0378'))
												AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
												)
											THEN (convert(DATE, '1900-01-01'))
										ELSE (T1."Current Date")
										END
									)
								)
							)
						)))
			) AS "Monatsletzter",
		(
			convert(DATETIME, (
					(
						(
							CASE 
								WHEN (
										(T1."Employee No_" IN ('0382', '0378'))
										AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
										)
									THEN (convert(DATE, '1900-01-01'))
								ELSE (T1."Current Date")
								END
							)
						)
					) - cinterval(day((
							(
								(
									CASE 
										WHEN (
												(T1."Employee No_" IN ('0382', '0378'))
												AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
												)
											THEN (convert(DATE, '1900-01-01'))
										ELSE (T1."Current Date")
										END
									)
								)
							)) - 1))
			) AS "Monatserster",
		CASE 
			WHEN (T4."Function Code" IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI'))
				THEN ('prod. Personal')
			ELSE ('unprod. Personal')
			END AS "produktiv/unproduktiv",
		(
			(
				CASE 
					WHEN (T4."Function Code" IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI'))
						THEN ('prod. Personal')
					ELSE ('unprod. Personal')
					END
				)
			) AS "Monteur_Gruppe",
		(- 1 * datediff(day, (getdate()), T3."Leaving Date")) AS "Tage Heute Leaving Date",
		T3."Group No_ 3" AS "Group No 3",
		T3."Group No_ 2" AS "Group No 2",
		T3."Group No_ 1" AS "Group No 1",
		T3."Leaving Date" AS "Leaving Date",
		CASE 
			WHEN (T2."No_" IN (200))
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "�berstunden",
		CASE 
			WHEN (T2."No_" IN (378))
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Zeitausgleich",
		T4."First Name" + ' ' + T4."Last Name" AS "Monteur",
		T4."Function Code" AS "Monteur_Gruppe_ori",
		T4."Last Name" AS "Last Name",
		T4."First Name" AS "First Name",
		(
			CASE 
				WHEN (T2."No_" IN (370, 371, 372))
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T2."No_" = 379)
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T2."No_" = 352)
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T2."No_" = 354)
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + 0 + (
			CASE 
				WHEN (T2."No_" = 450)
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T2."No_" = 355)
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T2."No_" IN (350, 351))
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) + (
			CASE 
				WHEN (T2."No_" IN (300, 301))
					THEN (((convert(FLOAT, T1."Time Account Value"))))
				ELSE (0)
				END
			) AS "W-fix Stunden",
		CASE 
			WHEN (T2."No_" IN (350, 351))
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Urlaub",
		CASE 
			WHEN (T2."No_" = 355)
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Schulung extern",
		CASE 
			WHEN (T2."No_" = 450)
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Feiertag",
		CASE 
			WHEN (T2."No_" = 354)
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Berufsschule",
		CASE 
			WHEN (T2."No_" = 352)
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Sonderurlaub",
		CASE 
			WHEN (T2."No_" = 379)
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "Wehr- /Zivildienst",
		CASE 
			WHEN (T2."No_" IN (370, 371, 372))
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "krank",
		CASE 
			WHEN (T2."No_" IN (100))
				THEN (((convert(FLOAT, T1."Time Account Value"))))
			ELSE (0)
			END AS "gesamt Stunden",
		T2."Description 2" AS "Description 2",
		T2."Description" AS "Description",
		T2."No_" AS "No",
		T1."Record protected" AS "Record Protected",
		T1."Generating Function" AS "Generating Function",
		T1."TA Formatting" AS "Ta Formatting",
		T1."TA Class (Vacation Reduction)" AS "Ta Class (vacation Reduction)",
		T1."TA Class (Employee Info)" AS "Ta Class (employee Info)",
		T1."TA Class (Individual)" AS "Ta Class (individual)",
		T1."TA Class (Time Processing)" AS "Ta Class (time Processing)",
		T1."TA Class (Absent Days)" AS "Ta Class (absent Days)",
		T1."TA Class (Statistics)" AS "Ta Class (statistics)",
		T1."TA Class (General)" AS "Ta Class (general)",
		T1."Time Account Value" AS "Time Account Value",
		T1."Department No_" AS "Department No_ori"
	FROM "Gottstein7x"."dbo"."AH Gottstein$Employee_T" T3,
		(
			(
				"Gottstein7x"."dbo"."AH Gottstein$Time Entry_T" T1 LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Time Account_T" T2 ON T1."Time Account No_" = (((T2."No_")))
				) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Employee" T4 ON T1."Employee No_" = T4."No_"
			)
	WHERE (T4."No_" = T3."No_")
		AND (
			(
				(
					(
						(
							(
								(
									(
										CASE 
											WHEN (
													(T1."Employee No_" IN ('0382', '0378'))
													AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
													)
												THEN (convert(DATE, '1900-01-01'))
											ELSE (T1."Current Date")
											END
										)
									)
								) >= T4."Employment Date"
							)
						AND (
							(
								(
									(
										CASE 
											WHEN (
													(T1."Employee No_" IN ('0382', '0378'))
													AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
													)
												THEN (convert(DATE, '1900-01-01'))
											ELSE (T1."Current Date")
											END
										)
									)
								) >= convert(DATE, '2022-01-01')
							)
						)
					AND (
						(
							(
								(
									CASE 
										WHEN (
												(T1."Employee No_" IN ('0382', '0378'))
												AND (T1."Current Date" <= convert(DATETIME, '2019-02-22 00:00:00.000'))
												)
											THEN (convert(DATE, '1900-01-01'))
										ELSE (T1."Current Date")
										END
									)
								)
							) <= (getdate())
						)
					)
				AND (
					(
						CASE 
							WHEN (T4."Function Code" IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI'))
								THEN ('prod. Personal')
							ELSE ('unprod. Personal')
							END
						) IN ('prod. Personal', 'unprod. Personal')
					)
				)
			AND (
				(
					(
						(T1."Time Account No_" BETWEEN '299' AND '450')
						OR (T1."Time Account No_" = '1200')
						)
					OR (T1."Time Account No_" = '1410')
					)
				OR (T1."Time Account No_" IN ('670', '671', '672', '675', '676'))
				)
			)
		-- order by "Datum" asc,"Employee No" asc
	) D1