C11/System/CARLO/IQD/Zeit/zeit_2_aftersales.sql

select "Employee No" as "Employee No",
	   "Datum" as "Datum",
	   "Department No" as "Department No",
	   "Time Account No" as "Time Account No",
	   "Time Account Value" as "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" as "Ta Formatting",
	   "Generating Function" as "Generating Function",
	   "Record Protected" as "Record Protected",
	   "No" as "No",
	   "Description" as "Description",
	   "Description 2" as "Description 2",
	   "Zeitdauer" as "Zeitdauer",
	   RSUM("Zeitdauer") as "Summe  (Zeitdauer) Nr.1",
	   "gesamt Stunden" as "gesamt Stunden",
	   RSUM("gesamt Stunden" for "Datum") as "Summe  (gesamt Stunden) Nr.3",
	   RSUM("gesamt Stunden") as "Summe  (gesamt Stunden) Nr.2",
	   RSUM("gesamt Stunden") as "Summe  (gesamt Stunden) Nr.1",
	   "Wehr- /Zivildienst" as "Wehr- /Zivildienst",
	   "Innung Azubi" as "Innung Azubi",
	   "W-fix Stunden" as "W-fix Stunden",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori",
	   "Abteilung" as "Abteilung",
	   "Order Number" as "Order Number",
	   "Monteur_ori" as "Monteur_ori",
	   "Überstunden" as "Überstunden",
	   "Leaving Date" as "Leaving Date",
	   "Group No 1" as "Group No 1",
	   "Group No 2" as "Group No 2",
	   "Group No 3" as "Group No 3",
	   "Tage Heute Leaving Date" as "Tage Heute Leaving Date",
	   "Monteur_Gruppe" as "Monteur_Gruppe",
	   "produktiv/unproduktiv" as "produktiv/unproduktiv",
	   "Monatserster" as "Monatserster",
	   "Monatsletzter" as "Monatsletzter",
	   "Heute" as "Heute",
	   "Datum Tagesbericht" as "Datum Tagesbericht",
	   "Employment Date" as "Employment Date",
	   "Schulung extern" as "Schulung extern",
	   "Krank" as "Krank",
	   "Arzt" as "Arzt",
	   "Urlaub" as "Urlaub",
	   "Sonderurlaub" as "Sonderurlaub",
	   "Fehlstunden" as "Fehlstunden",
	   "Berufsschule" as "Berufsschule",
	   "Feiertag" as "Feiertag",
	   "Zeitausgleich" as "Zeitausgleich",
	   "Kostenstelle" as "Kostenstelle",
	   "Filialcode_Employee" as "Filialcode_Employee",
	   "Kostenstellencode_Employee" as "Kostenstellencode_Employee",
	   "Name_Kostenstelle" as "Name_Kostenstelle",
	   "Betrieb" as "Betrieb",
	   "Monteur_Gruppe_2" as "Monteur_Gruppe_2",
	   "Arbeitsvertragscode" as "Arbeitsvertragscode",
	   "Serviceberater" as "Serviceberater",
	   "Abwesenheiten Serviceberater" as "Abwesenheiten Serviceberater",
	   "Urlaubstage" as "Urlaubstage",
	   "Kranktage" as "Kranktage",
	   "Schultage" as "Schultage",
	   "Standort_1" as "Standort_1",
	   "Serviceberater_KZ_Employee" as "Serviceberater_KZ_Employee",
	   "Invoice Date" as "Invoice Date",
	   "Funktion" as "Funktion",
	   "Servicberater / sonst. MA" as "Servicberater / sonst. MA"
from 
(select T1."Employee No_" as "Employee No",
	   T1."Current Date" as "Datum",
	   T1."Department No_" as "Department No",
	   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 (Missing Day)" 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."Generate 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_" = 379) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Wehr- /Zivildienst",
	   0 as "Innung Azubi",
	   (CASE WHEN (T2."No_" = 370) 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) as "W-fix Stunden",
	   T1."Client_DB" as "Hauptbetrieb",
	   (T3."Filialcode") as "Standort",
	   T3."Vorname" as "First Name",
	   T3."Nachname" as "Last Name",
	   CASE WHEN (T3."Global Dimension 1 Code" IN ('40','62')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('41')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('42')) THEN ('Lack') ELSE null END as "Monteur_Gruppe_ori",
	   '' as "Abteilung",
	   '' as "Order Number",
	   T3."Vorname" + ' ' + T3."Nachname" as "Monteur_ori",
	   CASE WHEN (T2."No_" IN (200)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Überstunden",
	   T4."Leaving Date" as "Leaving Date",
	   T4."Group No_ 1" as "Group No 1",
	   T4."Group No_ 2" as "Group No 2",
	   T4."Group No_ 3" as "Group No 3",
	   (day((now()) - T4."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN ((((day((now()) - T4."Leaving Date"))) > 0) and (T4."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE ((CASE WHEN (T3."Global Dimension 1 Code" IN ('40','62')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('41')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('42')) THEN ('Lack') ELSE null END)) END as "Monteur_Gruppe",
	   CASE WHEN (T4."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T4."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as "produktiv/unproduktiv",
	   (convert(datetime, T1."Current Date" - cinterval(day(T1."Current Date") - 1))) as "Monatserster",
	   (convert(datetime, lastday((T1."Current Date")))) as "Monatsletzter",
	   (now()) as "Heute",
	   CASE WHEN (((now())) BETWEEN ((convert(datetime, T1."Current Date" - cinterval(day(T1."Current Date") - 1)))) AND ((convert(datetime, lastday((T1."Current Date")))))) THEN (T1."Current Date") ELSE null END as "Datum Tagesbericht",
	   T3."Anstellungsdatum" as "Employment Date",
	   CASE WHEN (T2."No_" = 355) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Schulung extern",
	   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 (302)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Arzt",
	   CASE WHEN (T2."No_" IN (350,351)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Urlaub",
	   CASE WHEN (T2."No_" IN (352,353)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Sonderurlaub",
	   CASE WHEN (T2."No_" IN (299,300,301,340)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Fehlstunden",
	   CASE WHEN (T2."No_" IN (354)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Berufsschule",
	   CASE WHEN (T2."No_" IN (450)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Feiertag",
	   CASE WHEN (T2."No_" IN (378)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END as "Zeitausgleich",
	   T3."Global Dimension 1 Code" + ' - ' + T5."Name" as "Kostenstelle",
	   T3."Filialcode" as "Filialcode_Employee",
	   T3."Global Dimension 1 Code" as "Kostenstellencode_Employee",
	   T5."Name" as "Name_Kostenstelle",
	   CASE WHEN (((T3."Filialcode")) IN ('00','01')) THEN ('Deggendorf') WHEN (((T3."Filialcode")) IN ('02')) THEN ('Landau') ELSE null END as "Betrieb",
	   CASE WHEN (T3."Arbeitsvertragscode" = 'AZUBI') THEN ('AZUBI') WHEN (T3."Arbeitsvertragscode" IN ('MECH','LACK')) THEN ('Monteur') ELSE ('andere Mitarbeiter') END as "Monteur_Gruppe_2",
	   T3."Arbeitsvertragscode" as "Arbeitsvertragscode",
	   T1."Employee No_" + ' - ' + (T3."Vorname" + ' ' + T3."Nachname") as "Serviceberater",
	   (CASE WHEN (T2."No_" = 355) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" IN (370,371,372)) 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 (352,353)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) as "Abwesenheiten Serviceberater",
	   CASE WHEN (((CASE WHEN (T2."No_" IN (350,351)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.26 AND 10) or ((CASE WHEN (T2."No_" IN (352,353)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.26 AND 10)) THEN (1) WHEN (((CASE WHEN (T2."No_" IN (350,351)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.25) or ((CASE WHEN (T2."No_" IN (352,353)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.25)) THEN (0.5) ELSE (0) END as "Urlaubstage",
	   CASE WHEN ((CASE WHEN (T2."No_" IN (370,371,372)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.26 AND 10) THEN (1) WHEN ((CASE WHEN (T2."No_" IN (370,371,372)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.25) THEN (0.5) ELSE (0) END as "Kranktage",
	   CASE WHEN ((CASE WHEN (T2."No_" = 355) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.26 AND 10) THEN (1) WHEN ((CASE WHEN (T2."No_" = 355) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.25) THEN (0.5) ELSE (0) END as "Schultage",
	   ('Serviceberater') as "Standort_1",
	   T3."Serviceberater" as "Serviceberater_KZ_Employee",
	   T1."Current Date" as "Invoice Date",
	   T3."Funktion" as "Funktion",
	   CASE WHEN (T3."Funktion" IN ('Service Berater','Serviceberater','Serviceverater')) THEN ('Serviceberater') ELSE ('sonstige MA') END as "Servicberater / sonst. MA"
from "CARLO"."import"."Employee_T" T4,
	((("CARLO"."import"."Time_Entry_T" T1 left outer join "CARLO"."import"."Time_Account_T" T2 on (T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee" T3 on (T3."Nr_" = T1."Employee No_") and (T3."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T3."Global Dimension 1 Code" = T5."Code") and (T3."Client_DB" = T5."Client_DB"))
where ((T3."Nr_" = T4."No_") and (T3."Client_DB" = T4."Client_DB"))
and ((((T1."Current Date" >= T3."Anstellungsdatum") and (T1."Current Date" >= convert(datetime, '2014-01-01 00:00:00.000'))) and (T3."Funktion" IN ('Service Berater','Serviceberater','Serviceverater'))) and (((CASE WHEN (T2."No_" = 355) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" IN (370,371,372)) 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 (352,353)) THEN (((convert(float, T1."Time Account Value")))) ELSE (0) END)) <> 0))
-- order by "Datum" asc,"Employee No" asc
) D1