C11/System/NAVISION/IQD/Zeiten/Zeit_Mitarbeiter_Time_Entry_Aftersales.sql

select T1."No_" as "No_2",
	   T1."ID No_" as "Id No",
	   T1."Name" as "Name",
	   T1."Last Name" as "Last Name",
	   T1."First Name" as "First Name",
	   T1."Employment Date" as "Employment Date",
	   T1."Leaving Date" as "Leaving Date",
	   T1."Department No_" as "Department No_2",
	   T1."Group No_ 1" as "Group No 1",
	   T1."Group No_ 2" as "Group No 2",
	   T1."Group No_ 3" as "Group No 3",
	   T1."Time Object No_" as "Time Object No",
	   (convert(float, T1."Std_ Vac_ Entitlem_ Days")) as "Std Vac Entitlem Days",
	   (convert(float, T1."Handic_ Vac_ Entitlem_ Days")) as "Handic Vac Entitlem Days",
	   (convert(float, T1."Spec_ Vac_2 Entitlem_ Days")) as "Spec Vac 2 Entitlem Days",
	   (convert(float, T1."Spec_ Vac_3 Entitlem_ Days")) as "Spec Vac 3 Entitlem Days",
	   (convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_")) as "Std Vac Days Car Fwd Prev Y",
	   (convert(float, T1."Handic_ Vac_1 Days Car_ Fwd_")) as "Handic Vac 1 Days Car Fwd",
	   (convert(float, T1."Special Vac_2 Days Car_ Fwd_")) as "Special Vac 2 Days Car Fwd",
	   (convert(float, T1."Special Vac_3 Days Car_ Fwd_")) as "Special Vac 3 Days Car Fwd",
	   (convert(float, T1."Rem_ Vac_ Days taken")) as "Rem Vac Days Taken",
	   (convert(float, T1."Rem_ Vac_ Days planned")) as "Rem Vac Days Planned",
	   (convert(float, T1."Standard Vac_ Entitlement Hrs_")) as "Standard Vac Entitlement Hrs",
	   (convert(float, T1."Handic_ Vac_1 Entitlement Hrs_")) as "Handic Vac 1 Entitlement Hrs",
	   (convert(float, T1."Special Vac_2 Entitlement (H)")) as "Special Vac 2 Entitlement (h)",
	   (convert(float, T1."Std_ Vac_Car_Fwd_Prev_Y_(H)")) as "Std Vac Car Fwd Prev Y (h)",
	   (convert(float, T1."Handic_ Vac_1 Hours Car_ Fwd_")) as "Handic Vac 1 Hours Car Fwd",
	   (convert(float, T1."Spec_ Vac_2 Hours Car_ Fwd_")) as "Spec Vac 2 Hours Car Fwd",
	   (convert(float, T1."Rem_ Vac_ Hours taken")) as "Rem Vac Hours Taken",
	   (convert(float, T1."Rem_ Vac_ Hrs_ Planned")) as "Rem Vac Hrs Planned",
	   (convert(float, T1."Rem_ Handic_ Vac_ Days taken")) as "Rem Handic Vac Days Taken",
	   (convert(float, T1."Rem_ Handic_ Vac_ Days planned")) as "Rem Handic Vac Days Planned",
	   T1."Values Car_ Fwd_ Inactive" as "Values Car Fwd Inactive",
	   T1."Present" as "Present",
	   T1."Absent" as "Absent",
	   T1."Department Code" as "Department Code",
	   T1."Task Type Group" as "Task Type Group",
	   T1."Efficiency %" as "Efficiency %",
	   T1."Resource No_" as "Resource No",
	   T1."Rating PEREAS" as "Rating Pereas",
	   ((convert(float, T1."Std_ Vac_ Entitlem_ Days"))) as "Urlaubsanspruch",
	   ((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_"))) as "Urlaubsübertrag Vorjahr",
	   (((convert(float, T1."Std_ Vac_ Entitlem_ Days")))) + (((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_")))) as "Gesamturlaubsanspruch",
	   ((convert(float, T1."Rem_ Vac_ Days taken"))) as "Resturlaub",
	   ((((convert(float, T1."Std_ Vac_ Entitlem_ Days")))) + (((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_"))))) - (((convert(float, T1."Rem_ Vac_ Days taken")))) as "bisher genommener Urlaub",
	   ((convert(float, T1."Rem_ Vac_ Days planned"))) as "Resturlaub bis Jahresende",
	   (((convert(float, T1."Rem_ Vac_ Days taken")))) - (((convert(float, T1."Rem_ Vac_ Days planned")))) as "geplanter Urlaub",
	   T2."Employee No_" as "Employee No",
	   T2."Current Date" as "Datum",
	   T2."Department No_" as "Department No",
	   T2."Order No_" as "Order No",
	   T2."Time Account No_" as "Time Account No",
	   (convert(float, T2."Time Account Value")) as "Time Account Value",
	   T2."TA Class (General)" as "Ta Class (general)",
	   T2."TA Class (Statistics)" as "Ta Class (statistics)",
	   T2."TA Class (Absent Days)" as "Ta Class (absent Days)",
	   T2."TA Class (Time Processing)" as "Ta Class (time Processing)",
	   T2."TA Class (Individual)" as "Ta Class (individual)",
	   T2."TA Class (Employee Info)" as "Ta Class (employee Info)",
	   T2."TA Class (Vacation Reduction)" as "Ta Class (vacation Reduction)",
	   T2."TA Formatting" as "Ta Formatting",
	   T2."Generating Function" as "Generating Function",
	   T2."Record protected" as "Record Protected",
	   T3."No_" as "No",
	   T3."Description" as "Description",
	   T3."Description 2" as "Description 2",
	   T3."Symbol" as "Symbol",
	   T3."Character" as "Character",
	   T3."Pay Type 1" as "Pay Type 1",
	   CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Sollzeit",
	   CASE WHEN (T2."Time Account No_" IN ('100')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Anwesenheit",
	   CASE WHEN (T2."Time Account No_" IN ('200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Mehrarbeit genehmigt",
	   CASE WHEN (T2."Time Account No_" IN ('300')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Fehlzeit ungenehmigt",
	   CASE WHEN ((((year(T2."Current Date")) = (year((getdate())))) and ((month(T2."Current Date")) = 1)) and (T2."Time Account No_" IN ('1101'))) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Überstunden Vortrag",
	   (CASE WHEN (T2."Time Account No_" IN ('200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) - (CASE WHEN (T2."Time Account No_" IN ('300')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN ((((year(T2."Current Date")) = (year((getdate())))) and ((month(T2."Current Date")) = 1)) and (T2."Time Account No_" IN ('1101'))) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) as "Überstunden Saldo",
	   CASE WHEN (T2."Time Account No_" IN ('370')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank",
	   CASE WHEN (T2."Time Account No_" IN ('371')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank ohne AUB",
	   CASE WHEN (T2."Time Account No_" IN ('372')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank ohne LFZ",
	   CASE WHEN (T2."Time Account No_" IN ('373','374')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank Kur",
	   (CASE WHEN (T2."Time Account No_" IN ('370')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."Time Account No_" IN ('371')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."Time Account No_" IN ('372')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."Time Account No_" IN ('373','374')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) as "Krank gesamt",
	   CASE WHEN (T2."Time Account No_" IN ('350')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Urlaub",
	   CASE WHEN (T2."Time Account No_" IN ('352')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Sonderurlaub",
	   CASE WHEN (T2."Time Account No_" IN ('353')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Erziehungsurlaub",
	   CASE WHEN (T2."Time Account No_" IN ('351')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Urlaub unbezahlt",
	   (CASE WHEN (T2."Time Account No_" IN ('350')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."Time Account No_" IN ('352')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."Time Account No_" IN ('353')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."Time Account No_" IN ('351')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) as "Urlaub gesamt",
	   CASE WHEN (T2."Time Account No_" IN ('354')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Berufsschule",
	   CASE WHEN (T2."Time Account No_" IN ('355')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Schulung extern",
	   CASE WHEN (T2."Time Account No_" IN ('356')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Dienstreise",
	   CASE WHEN (T2."Time Account No_" IN ('357')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Prüfung",
	   CASE WHEN (T2."Time Account No_" IN ('359')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Messe",
	   CASE WHEN (T2."Time Account No_" IN ('450')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Feiertag",
	   CASE WHEN (T2."Time Account No_" IN ('378')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Zeitausgleich",
	   CASE WHEN (T2."Time Account No_" IN ('380')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Mutterschutz",
	   '1' as "Hauptbetrieb",
	   CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END as "Standort",
	   T1."Pay Group No_" as "Monteur_Gruppe_ori",
	   (getdate()) - 1 as "Heute",
	   (-1 * datediff(day, (getdate()), T1."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 0) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('augetretene Mitarbeiter') ELSE (T1."Pay Group No_") END as "Monteur_Gruppe",
	   CASE WHEN (T1."Task Type Group" IN ('MONTEURE','PROD MEIST')) THEN ('prod. Personal') WHEN (T1."Task Type Group" IN ('VERWALTUNG')) THEN ('unprod. Personal') ELSE null END as "produktiv/unproduktiv",
	   (convert(datetime, T2."Current Date" - cinterval(-1 * datediff(day, T2."Current Date"), 1))) as "Monatserster",
	   (convert(datetime, eomonth((T2."Current Date")))) as "Monatsletzter",
	   CASE WHEN (((getdate()) - 1) BETWEEN ((convert(datetime, T2."Current Date" - cinterval(-1 * datediff(day, T2."Current Date"), 1)))) AND ((convert(datetime, eomonth((T2."Current Date")))))) THEN (T2."Current Date") ELSE null END as "Datum Tagesbericht",
	   '' as "Order Number",
	   CASE WHEN (T4."Function Code" IN ('SCBI','SCBK')) THEN (T1."First Name" + ' ' + T1."Last Name" + ' - ' + T4."Function Code") ELSE (T1."First Name" + ' ' + T1."Last Name") END as "Serviceberater",
	   CASE WHEN (T2."Time Account No_" IN ('650','651','652','653','678')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Urlaubstage",
	   CASE WHEN (T2."Time Account No_" IN ('670','671','672','673')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Kranktage",
	   CASE WHEN (T2."Time Account No_" IN ('655')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Schultage",
	   CASE WHEN ((T1."Name" = 'Yusuf Baylan') and (T2."Current Date" <= convert(datetime, '2012-02-29 00:00:00.000'))) THEN ('raus') ELSE ('rein') END as "Datumsfilter",
	   T4."Function Code" as "Function Code_Employee",
	   CASE WHEN ((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('70','60','80')) THEN ('2') ELSE ('1') END as "Hauptbetrieb_ID",
	   (CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) as "Standort_ID"
from "NAVISION"."import"."Time_Entry_T" T2,
	"NAVISION"."import"."Time_Account_T" T3,
	("NAVISION"."import"."Employee_T" T1 left outer join "NAVISION"."import"."Employee" T4 on (T4."No_" = T1."No_") and (T4."Client_DB" = T1."Client_DB"))
where ((T1."No_" = T2."Employee No_") and (T1."Client_DB" = T2."Client_DB")) and ((T2."Time Account No_" = (((T3."No_")))) and (T3."Client_DB" = T2."Client_DB"))
and (((((((T2."Current Date" >= T1."Employment Date") and ((T1."Leaving Date" >= convert(datetime, '2012-01-01 00:00:00.000')) or (T1."Leaving Date" = convert(datetime, '1753-01-01 00:00:00.000')))) and (T2."Current Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and ((CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 0) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('augetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) LIKE 'SCB%')) and (T2."Time Account No_" IN ('650','651','652','653','670','671','672','673','655','678'))) and ((CASE WHEN ((T1."Name" = 'Yusuf Baylan') and (T2."Current Date" <= convert(datetime, '2012-02-29 00:00:00.000'))) THEN ('raus') ELSE ('rein') END) = 'rein')) and (T2."Current Date" <= (getdate())))
-- order by "Datum" asc,"Time Account No" asc