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

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",
	T1."Department No_" AS "Department No_ori",
	T1."Time Account No_" AS "Time Account No",
	T1."Time Account Value" AS "Time Account Value",
	T1."TA Class (General)" AS "Ta Class (general)",
	T1."TA Class (Statistics)" AS "Ta Class (statistics)",
	T1."TA Class (Absent Days)" AS "Ta Class (absent Days)",
	T1."TA Class (Time Processing)" AS "Ta Class (time Processing)",
	T1."TA Class (Individual)" AS "Ta Class (individual)",
	T1."TA Class (Employee Info)" AS "Ta Class (employee Info)",
	T1."TA Class (Vacation Reduction)" AS "Ta Class (vacation Reduction)",
	T1."TA Formatting" AS "Ta Formatting",
	T1."Generating Function" AS "Generating Function",
	T1."Record protected" AS "Record Protected",
	T2."No_" AS "No",
	T2."Description" AS "Description",
	T2."Description 2" AS "Description 2",
	(convert(FLOAT, T1."Time Account Value")) AS "Zeitdauer",
	CASE 
		WHEN (T2."No_" IN (100))
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "gesamt Stunden",
	CASE 
		WHEN (T2."No_" IN (370, 371, 372))
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "krank",
	CASE 
		WHEN (T2."No_" = 379)
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Wehr- /Zivildienst",
	CASE 
		WHEN (T2."No_" = 352)
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Sonderurlaub",
	CASE 
		WHEN (T2."No_" = 354)
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Berufsschule",
	0 AS "Innung Azubi",
	CASE 
		WHEN (T2."No_" = 450)
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Feiertag",
	CASE 
		WHEN (T2."No_" = 355)
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Schulung extern",
	CASE 
		WHEN (T2."No_" IN (350, 351))
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Urlaub",
	(
		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",
	'1' AS "Hauptbetrieb_ID",
	CASE 
		WHEN ((left(T3."Department No_", 2)) = '10')
			THEN ('LBS')
		WHEN ((left(T3."Department No_", 2)) = '20')
			THEN ('WLS')
		ELSE NULL
		END AS "Standort",
	T4."First Name" AS "First Name",
	T4."Last Name" AS "Last Name",
	T3."Group No_ 2" AS "Monteur_Gruppe_ori",
	'' AS "Abteilung",
	'' AS "Order Number",
	T4."First Name" + ' ' + T4."Last Name" AS "Monteur",
	CASE 
		WHEN (T2."No_" IN (378))
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Zeitausgleich",
	CASE 
		WHEN (T2."No_" IN (200))
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "�berstunden",
	T3."Leaving Date" AS "Leaving Date",
	T3."Group No_ 1" AS "Group No 1",
	T3."Group No_ 2" AS "Group No 2",
	T3."Group No_ 3" AS "Group No 3",
	(- 1 * datediff(day, (getdate()), T3."Leaving Date")) AS "Tage Heute Leaving Date",
	CASE 
		WHEN (
				(T3."Leaving Date" < (getdate()))
				AND (T3."Leaving Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
				)
			THEN ('ausgetretene Mitarbeiter')
		ELSE (
				(
					CASE 
						WHEN (T4."Task Type Group" IN ('LEHRLINGE', 'MEISTER', 'MONTEURE'))
							THEN ('prod. Personal')
						ELSE ('unprod. Personal')
						END
					)
				)
		END AS "Monteur_Gruppe",
	CASE 
		WHEN (T4."Task Type Group" IN ('LEHRLINGE', 'MEISTER', 'MONTEURE'))
			THEN ('prod. Personal')
		ELSE ('unprod. Personal')
		END AS "produktiv/unproduktiv",
	(
		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",
	(
		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",
	(getdate()) - 1 AS "Heute",
	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",
	T4."Employment Date" AS "Employment Date",
	T3."Department No_" AS "Department No_2",
	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",
	CASE 
		WHEN (T2."No_" IN (300, 301))
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Fehlzeiten genehm./ungenem.",
	(rtrim((((T2."No_"))))) + ' - ' + T2."Description" AS "Activity Desc",
	T4."Function Code" AS "Monteur_Gruppe_2",
	CASE 
		WHEN ((left(T3."Department No_", 2)) = '10')
			THEN ('10')
		WHEN ((left(T3."Department No_", 2)) = '20')
			THEN ('20')
		ELSE NULL
		END AS "Standort_ID",
	T3."Department No_" AS "Department No",
	T4."Task Type Group" AS "Task Type Group",
	CASE 
		WHEN (T1."Time Account No_" = '1200')
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "Sollzeit",
	CASE 
		WHEN (T1."Time Account No_" BETWEEN '299' AND '450')
			THEN (((convert(FLOAT, T1."Time Account Value"))))
		ELSE (0)
		END AS "abwesend"
FROM "Vogl7x"."dbo"."BMW AH Vogl$Employee_T" T3,
	(
		(
			"Vogl7x"."dbo"."BMW AH Vogl$Time Entry_T" T1 LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Time Account_T" T2 ON T1."Time Account No_" = (((T2."No_")))
			) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$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, '2020-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."Task Type Group" IN ('LEHRLINGE', 'MEISTER', 'MONTEURE'))
							THEN ('prod. Personal')
						ELSE ('unprod. Personal')
						END
					) = 'prod. Personal'
				)
			)
		AND (
			(T1."Time Account No_" BETWEEN '299' AND '450')
			OR (T1."Time Account No_" = '1200')
			)
		)
	-- order by "Datum" asc,"Employee No" asc