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",
	   (day((now()) - T3."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN ((T3."Leaving Date" < (now())) 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, lastday(((((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",
	   (now()) - INTERVAL '001 10:00:00.000' as "Heute",
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') 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, lastday(((((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",
	   CASE WHEN ((day((now()) - (((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((cast_numberToString(cast_integer(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 outer join "Vogl7x"."dbo"."BMW AH Vogl$Time Account_T" T2 on T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) left outer 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