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

select "No_2" as "No_2",
	   "Id No" as "Id No",
	   "Name" as "Name",
	   "Last Name" as "Last Name",
	   "First Name" as "First Name",
	   "Employment Date" as "Employment Date",
	   "Leaving Date" as "Leaving Date",
	   "Department No" as "Department No",
	   "Group No 1" as "Group No 1",
	   "Group No 2" as "Group No 2",
	   "Group No 3" as "Group No 3",
	   "Time Object No" as "Time Object No",
	   "Std Vac Entitlem Days" as "Std Vac Entitlem Days",
	   "Handic Vac Entitlem Days" as "Handic Vac Entitlem Days",
	   "Spec Vac 2 Entitlem Days" as "Spec Vac 2 Entitlem Days",
	   "Spec Vac 3 Entitlem Days" as "Spec Vac 3 Entitlem Days",
	   "Std Vac Days Car Fwd Prev Y" as "Std Vac Days Car Fwd Prev Y",
	   "Handic Vac 1 Days Car Fwd" as "Handic Vac 1 Days Car Fwd",
	   "Special Vac 2 Days Car Fwd" as "Special Vac 2 Days Car Fwd",
	   "Special Vac 3 Days Car Fwd" as "Special Vac 3 Days Car Fwd",
	   "Rem Vac Days Taken" as "Rem Vac Days Taken",
	   "Rem Vac Days Planned" as "Rem Vac Days Planned",
	   "Standard Vac Entitlement Hrs" as "Standard Vac Entitlement Hrs",
	   "Handic Vac 1 Entitlement Hrs" as "Handic Vac 1 Entitlement Hrs",
	   "Special Vac 2 Entitlement (h)" as "Special Vac 2 Entitlement (h)",
	   "Std Vac Car Fwd Prev Y (h)" as "Std Vac Car Fwd Prev Y (h)",
	   "Handic Vac 1 Hours Car Fwd" as "Handic Vac 1 Hours Car Fwd",
	   "Spec Vac 2 Hours Car Fwd" as "Spec Vac 2 Hours Car Fwd",
	   "Rem Vac Hours Taken" as "Rem Vac Hours Taken",
	   "Rem Vac Hrs Planned" as "Rem Vac Hrs Planned",
	   "Rem Handic Vac Days Taken" as "Rem Handic Vac Days Taken",
	   "Rem Handic Vac Days Planned" as "Rem Handic Vac Days Planned",
	   "Values Car Fwd Inactive" as "Values Car Fwd Inactive",
	   "Present" as "Present",
	   "Absent" as "Absent",
	   "Department Code" as "Department Code",
	   "Task Type Group" as "Task Type Group",
	   "Efficiency %" as "Efficiency %",
	   "Resource No" as "Resource No",
	   "Rating Pereas" as "Rating Pereas",
	   "Urlaubsanspruch" as "Urlaubsanspruch",
	   "Urlaubsübertrag Vorjahr" as "Urlaubsübertrag Vorjahr",
	   "Gesamturlaubsanspruch" as "Gesamturlaubsanspruch",
	   "Resturlaub" as "Resturlaub",
	   "bisher genommener Urlaub" as "bisher genommener Urlaub",
	   "Resturlaub bis Jahresende" as "Resturlaub bis Jahresende",
	   "geplanter Urlaub" as "geplanter Urlaub",
	   "Employee No" as "Employee No",
	   "Datum" as "Datum",
	   "Department No_" as "Department No_",
	   "Order No" as "Order 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",
	   "Symbol" as "Symbol",
	   "Character" as "Character",
	   "Pay Type 1" as "Pay Type 1",
	   "Sollzeit" as "Sollzeit",
	   "Anwesenheit" as "Anwesenheit",
	   "Mehrarbeit genehmigt" as "Mehrarbeit genehmigt",
	   "Fehlzeit ungenehmigt" as "Fehlzeit ungenehmigt",
	   "Überstunden Vortrag" as "Überstunden Vortrag",
	   "Überstunden Saldo" as "Überstunden Saldo",
	   "Summe  (Überstunden Saldo) Nr.2" as "Summe  (Überstunden Saldo) Nr.2",
	   "Summe  (Überstunden Saldo) Nr.1" as "Summe  (Überstunden Saldo) Nr.1",
	   "Krank" as "Krank",
	   "Krank ohne AUB" as "Krank ohne AUB",
	   "Krank ohne LFZ" as "Krank ohne LFZ",
	   "Krank Kur" as "Krank Kur",
	   "Krank gesamt" as "Krank gesamt",
	   "Urlaub" as "Urlaub",
	   "Sonderurlaub" as "Sonderurlaub",
	   "Erziehungsurlaub" as "Erziehungsurlaub",
	   "Urlaub unbezahlt" as "Urlaub unbezahlt",
	   "Urlaub gesamt" as "Urlaub gesamt",
	   "Berufsschule" as "Berufsschule",
	   "Schulung extern" as "Schulung extern",
	   "Dienstreise" as "Dienstreise",
	   "Prüfung" as "Prüfung",
	   "Messe" as "Messe",
	   "Feiertag" as "Feiertag",
	   "Zeitausgleich" as "Zeitausgleich",
	   "Mutterschutz" as "Mutterschutz",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori",
	   "Heute" as "Heute",
	   "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",
	   "Datum Tagesbericht" as "Datum Tagesbericht",
	   "Order Number" as "Order Number",
	   "Monteur" as "Monteur",
	   "Krank Tage" as "Krank Tage",
	   "Krank Tage ohne AUB" as "Krank Tage ohne AUB",
	   "Krank Tage ohne LFZ" as "Krank Tage ohne LFZ",
	   "Krank Tage Kur" as "Krank Tage Kur",
	   "ausbezahlte Überstunden" as "ausbezahlte Überstunden",
	   "LG_Soll" as "LG_Soll",
	   "Effizienz" as "Effizienz",
	   "fakt. Zeit Soll in AW" as "fakt. Zeit Soll in AW",
	   "fakt. Zeit Soll in Std." as "fakt. Zeit Soll in Std.",
	   "AW-Listenpreis" as "AW-Listenpreis",
	   "Ums. Lohn Plan Monteur" as "Ums. Lohn Plan Monteur",
	   "Summe Sollzeit für Tag" as "Summe Sollzeit für Tag",
	   "Summe Urlaub für Tag" as "Summe Urlaub für Tag",
	   "Summe Krank für Tag" as "Summe Krank für Tag",
	   "Umsatz Lohn Plan Monteur ohne Krank" as "Umsatz Lohn Plan Monteur ohne Krank",
	   "Schulung extern Tage" as "Schulung extern Tage",
	   "Sollzeit in Tagen" as "Sollzeit in Tagen",
	   SUM("Sollzeit in Tagen") OVER (partition by "Datum","No_2") as "Summe_Sollzeit_in_Tagen",
	   CASE WHEN (SUM("Sollzeit in Tagen") OVER (partition by "Datum","No_2") <> 0) THEN "Krank Tage" ELSE (0) END as "Krank Tage bereinigt",
	   CASE WHEN (SUM("Sollzeit in Tagen") OVER (partition by "Datum","No_2") <> 0) THEN "Krank Tage ohne AUB" ELSE (0) END as "Krank Tage ohne AUB bereinigt",
	   CASE WHEN (SUM("Sollzeit in Tagen") OVER (partition by "Datum","No_2") <> 0) THEN "Krank Tage ohne LFZ" ELSE (0) END as "Krank Tage ohne LFZ bereinigt",
	   CASE WHEN (SUM("Sollzeit in Tagen") OVER (partition by "Datum","No_2") <> 0) THEN "Krank Tage Kur" ELSE (0) END as "Krank Tage Kur bereinigt",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name"
from 
(select "No_2" as "No_2",
	   "Id No" as "Id No",
	   "Name" as "Name",
	   "Last Name" as "Last Name",
	   "First Name" as "First Name",
	   "Employment Date" as "Employment Date",
	   "Leaving Date" as "Leaving Date",
	   "Department No" as "Department No",
	   "Group No 1" as "Group No 1",
	   "Group No 2" as "Group No 2",
	   "Group No 3" as "Group No 3",
	   "Time Object No" as "Time Object No",
	   "Std Vac Entitlem Days" as "Std Vac Entitlem Days",
	   "Handic Vac Entitlem Days" as "Handic Vac Entitlem Days",
	   "Spec Vac 2 Entitlem Days" as "Spec Vac 2 Entitlem Days",
	   "Spec Vac 3 Entitlem Days" as "Spec Vac 3 Entitlem Days",
	   "Std Vac Days Car Fwd Prev Y" as "Std Vac Days Car Fwd Prev Y",
	   "Handic Vac 1 Days Car Fwd" as "Handic Vac 1 Days Car Fwd",
	   "Special Vac 2 Days Car Fwd" as "Special Vac 2 Days Car Fwd",
	   "Special Vac 3 Days Car Fwd" as "Special Vac 3 Days Car Fwd",
	   "Rem Vac Days Taken" as "Rem Vac Days Taken",
	   "Rem Vac Days Planned" as "Rem Vac Days Planned",
	   "Standard Vac Entitlement Hrs" as "Standard Vac Entitlement Hrs",
	   "Handic Vac 1 Entitlement Hrs" as "Handic Vac 1 Entitlement Hrs",
	   "Special Vac 2 Entitlement (h)" as "Special Vac 2 Entitlement (h)",
	   "Std Vac Car Fwd Prev Y (h)" as "Std Vac Car Fwd Prev Y (h)",
	   "Handic Vac 1 Hours Car Fwd" as "Handic Vac 1 Hours Car Fwd",
	   "Spec Vac 2 Hours Car Fwd" as "Spec Vac 2 Hours Car Fwd",
	   "Rem Vac Hours Taken" as "Rem Vac Hours Taken",
	   "Rem Vac Hrs Planned" as "Rem Vac Hrs Planned",
	   "Rem Handic Vac Days Taken" as "Rem Handic Vac Days Taken",
	   "Rem Handic Vac Days Planned" as "Rem Handic Vac Days Planned",
	   "Values Car Fwd Inactive" as "Values Car Fwd Inactive",
	   "Present" as "Present",
	   "Absent" as "Absent",
	   "Department Code" as "Department Code",
	   "Task Type Group" as "Task Type Group",
	   "Efficiency %" as "Efficiency %",
	   "Resource No" as "Resource No",
	   "Rating Pereas" as "Rating Pereas",
	   "Urlaubsanspruch" as "Urlaubsanspruch",
	   "Urlaubsübertrag Vorjahr" as "Urlaubsübertrag Vorjahr",
	   "Gesamturlaubsanspruch" as "Gesamturlaubsanspruch",
	   "Resturlaub" as "Resturlaub",
	   "bisher genommener Urlaub" as "bisher genommener Urlaub",
	   "Resturlaub bis Jahresende" as "Resturlaub bis Jahresende",
	   "geplanter Urlaub" as "geplanter Urlaub",
	   "Employee No" as "Employee No",
	   "Datum" as "Datum",
	   "Department No_" as "Department No_",
	   "Order No" as "Order 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",
	   "Symbol" as "Symbol",
	   "Character" as "Character",
	   "Pay Type 1" as "Pay Type 1",
	   "Sollzeit" as "Sollzeit",
	   "Anwesenheit" as "Anwesenheit",
	   "Mehrarbeit genehmigt" as "Mehrarbeit genehmigt",
	   "Fehlzeit ungenehmigt" as "Fehlzeit ungenehmigt",
	   "Überstunden Vortrag" as "Überstunden Vortrag",
	   "Überstunden Saldo" as "Überstunden Saldo",
	   SUM("Überstunden Saldo") OVER (partition by "Datum") as "Summe  (Überstunden Saldo) Nr.2",
	   RSUM("Überstunden Saldo") as "Summe  (Überstunden Saldo) Nr.1",
	   "Krank" as "Krank",
	   "Krank ohne AUB" as "Krank ohne AUB",
	   "Krank ohne LFZ" as "Krank ohne LFZ",
	   "Krank Kur" as "Krank Kur",
	   "Krank gesamt" as "Krank gesamt",
	   "Urlaub" as "Urlaub",
	   "Sonderurlaub" as "Sonderurlaub",
	   "Erziehungsurlaub" as "Erziehungsurlaub",
	   "Urlaub unbezahlt" as "Urlaub unbezahlt",
	   "Urlaub gesamt" as "Urlaub gesamt",
	   "Berufsschule" as "Berufsschule",
	   "Schulung extern" as "Schulung extern",
	   "Dienstreise" as "Dienstreise",
	   "Prüfung" as "Prüfung",
	   "Messe" as "Messe",
	   "Feiertag" as "Feiertag",
	   "Zeitausgleich" as "Zeitausgleich",
	   "Mutterschutz" as "Mutterschutz",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori",
	   "Heute" as "Heute",
	   "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",
	   "Datum Tagesbericht" as "Datum Tagesbericht",
	   "Order Number" as "Order Number",
	   "Monteur" as "Monteur",
	   "Krank Tage" as "Krank Tage",
	   "Krank Tage ohne AUB" as "Krank Tage ohne AUB",
	   "Krank Tage ohne LFZ" as "Krank Tage ohne LFZ",
	   "Krank Tage Kur" as "Krank Tage Kur",
	   "ausbezahlte Überstunden" as "ausbezahlte Überstunden",
	   "LG_Soll" as "LG_Soll",
	   "Effizienz" as "Effizienz",
	   "fakt. Zeit Soll in AW" as "fakt. Zeit Soll in AW",
	   "fakt. Zeit Soll in Std." as "fakt. Zeit Soll in Std.",
	   "AW-Listenpreis" as "AW-Listenpreis",
	   "Ums. Lohn Plan Monteur" as "Ums. Lohn Plan Monteur",
	   "Summe Sollzeit für Tag" as "Summe Sollzeit für Tag",
	   "Summe Urlaub für Tag" as "Summe Urlaub für Tag",
	   "Summe Krank für Tag" as "Summe Krank für Tag",
	   "Umsatz Lohn Plan Monteur ohne Krank" as "Umsatz Lohn Plan Monteur ohne Krank",
	   "Schulung extern Tage" as "Schulung extern Tage",
	   "Sollzeit in Tagen" as "Sollzeit in Tagen",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name"
from 
(select "No_2",
	   "Id No",
	   "Name",
	   "Last Name",
	   "First Name",
	   "Employment Date",
	   "Leaving Date",
	   "Department No",
	   "Group No 1",
	   "Group No 2",
	   "Group No 3",
	   "Time Object No",
	   "Std Vac Entitlem Days",
	   "Handic Vac Entitlem Days",
	   "Spec Vac 2 Entitlem Days",
	   "Spec Vac 3 Entitlem Days",
	   "Std Vac Days Car Fwd Prev Y",
	   "Handic Vac 1 Days Car Fwd",
	   "Special Vac 2 Days Car Fwd",
	   "Special Vac 3 Days Car Fwd",
	   "Rem Vac Days Taken",
	   "Rem Vac Days Planned",
	   "Standard Vac Entitlement Hrs",
	   "Handic Vac 1 Entitlement Hrs",
	   "Special Vac 2 Entitlement (h)" as "Special Vac 2 Entitlement (h)",
	   "Std Vac Car Fwd Prev Y (h)" as "Std Vac Car Fwd Prev Y (h)",
	   "Handic Vac 1 Hours Car Fwd",
	   "Spec Vac 2 Hours Car Fwd",
	   "Rem Vac Hours Taken",
	   "Rem Vac Hrs Planned",
	   "Rem Handic Vac Days Taken",
	   "Rem Handic Vac Days Planned",
	   "Values Car Fwd Inactive",
	   "Present",
	   "Absent",
	   "Department Code",
	   "Task Type Group",
	   "Efficiency %",
	   "Resource No",
	   "Rating Pereas",
	   "Std Vac Entitlem Days" as "Urlaubsanspruch",
	   "Std Vac Days Car Fwd Prev Y" as "Urlaubsübertrag Vorjahr",
	   "Gesamturlaubsanspruch",
	   "Rem Vac Days Taken" as "Resturlaub",
	   "bisher genommener Urlaub",
	   "Rem Vac Days Planned" as "Resturlaub bis Jahresende",
	   "geplanter Urlaub",
	   "Employee No",
	   "Datum",
	   "Department No_",
	   "Order No",
	   "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",
	   "Symbol",
	   "Character",
	   "Pay Type 1",
	   "Sollzeit",
	   "Anwesenheit",
	   "Mehrarbeit genehmigt",
	   "Fehlzeit ungenehmigt",
	   "Überstunden Vortrag",
	   "Überstunden Saldo",
	   "Krank",
	   "Krank ohne AUB",
	   "Krank ohne LFZ",
	   "Krank Kur",
	   "Krank gesamt",
	   "Urlaub",
	   "Sonderurlaub",
	   "Erziehungsurlaub",
	   "Urlaub unbezahlt",
	   "Urlaub gesamt",
	   "Berufsschule",
	   "Schulung extern",
	   "Dienstreise",
	   "Prüfung",
	   "Messe",
	   "Feiertag",
	   "Zeitausgleich",
	   "Mutterschutz",
	   "Hauptbetrieb",
	   "Standort",
	   "Monteur_Gruppe_ori",
	   "Heute",
	   "Tage Heute Leaving Date",
	   "Monteur_Gruppe",
	   "produktiv/unproduktiv",
	   "Monatserster",
	   "Monatsletzter",
	   "Datum Tagesbericht",
	   '' as "Order Number",
	   "Monteur",
	   "Krank Tage",
	   "Krank Tage ohne AUB",
	   "Krank Tage ohne LFZ",
	   "Krank Tage Kur",
	   "ausbezahlte Überstunden",
	   "LG_Soll",
	   "Effizienz",
	   "fakt. Zeit Soll in AW",
	   "fakt. Zeit Soll in Std.",
	   "AW-Listenpreis",
	   CASE WHEN ((SUM("Sollzeit") OVER (partition by c143)) <> (SUM("Urlaub") OVER (partition by c143))) THEN (c154) ELSE (0) END as "Ums. Lohn Plan Monteur",
	   SUM("Sollzeit") OVER (partition by c143) as "Summe Sollzeit für Tag",
	   SUM("Urlaub") OVER (partition by c143) as "Summe Urlaub für Tag",
	   SUM("Krank") OVER (partition by c143) as "Summe Krank für Tag",
	   CASE WHEN (((SUM("Sollzeit") OVER (partition by c143)) <> (SUM("Urlaub") OVER (partition by c143))) and ((SUM("Sollzeit") OVER (partition by c143)) <> (SUM("Krank") OVER (partition by c143)))) THEN (c154) ELSE (0) END as "Umsatz Lohn Plan Monteur ohne Krank",
	   "Schulung extern Tage",
	   "Sollzeit in Tagen",
	   "Hauptbetrieb" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name"
from 
(select T1."No_" as "No_2",
	   T2."Current Date" as "Datum",
	   ((convert(varchar(50), year(T2."Current Date")) + '-' + convert(varchar(50), month(T2."Current Date")) + '-' + convert(varchar(50), day(T2."Current Date"))) + (T1."First Name" + ' ' + T1."Last Name")) as c143,
	   CASE WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name",
	   (CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) as "Standort",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   T2."Client_DB" as "Hauptbetrieb",
	   CASE WHEN (T2."Time Account No_" IN ('673','674')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank Tage Kur",
	   CASE WHEN (T2."Time Account No_" IN ('672')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank Tage ohne LFZ",
	   CASE WHEN (T2."Time Account No_" IN ('671')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank Tage ohne AUB",
	   CASE WHEN (T2."Time Account No_" IN ('670')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank Tage",
	   CASE WHEN ((CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) <> 0) THEN ((CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) / (CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END)) ELSE (0) END as "Sollzeit in Tagen",
	   CASE WHEN (T2."Time Account No_" IN ('655')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Schulung extern Tage",
	   (((CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) * 12 * (((convert(float, T1."Efficiency %"))) / 100)) * 0.8) * (CASE WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('40')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOM','WMOE','AZG4','AZG3','AZG2','AZG1'))) THEN (8.99) WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('40')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOK','WMOL'))) THEN (9.95) WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('10','20','30','50')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOM','WMOE','AZG4','AZG3','AZG2','AZG1'))) THEN (8.30) WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('10','20','30','50')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOK','WMOL'))) THEN (9.75) ELSE (0) END) as c154,
	   CASE WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('40')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOM','WMOE','AZG4','AZG3','AZG2','AZG1'))) THEN (8.99) WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('40')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOK','WMOL'))) THEN (9.95) WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('10','20','30','50')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOM','WMOE','AZG4','AZG3','AZG2','AZG1'))) THEN (8.30) WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) IN ('10','20','30','50')) 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 ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) IN ('WMOK','WMOL'))) THEN (9.75) ELSE (0) END as "AW-Listenpreis",
	   ((CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) * (((convert(float, T1."Efficiency %"))) / 100)) * 0.75 as "fakt. Zeit Soll in Std.",
	   ((CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) * 12 * (((convert(float, T1."Efficiency %"))) / 100)) * 0.8 as "fakt. Zeit Soll in AW",
	   ((convert(float, T1."Efficiency %"))) / 100 as "Effizienz",
	   CASE WHEN ((CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 0) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) = 'WMOM') THEN (110) WHEN ((CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 0) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) = 'WMOE') THEN (100) WHEN ((CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 0) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END) = 'WMOK') THEN (120) ELSE (0) END as "LG_Soll",
	   CASE WHEN (T2."Time Account No_" IN ('3500')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "ausbezahlte Überstunden",
	   T1."First Name" + ' ' + T1."Last Name" as "Monteur",
	   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",
	   (convert(datetime, eomonth((T2."Current Date")))) as "Monatsletzter",
	   (convert(datetime, T2."Current Date" - cinterval(-1 * datediff(day, T2."Current Date"), 1))) as "Monatserster",
	   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",
	   CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 0) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T1."Pay Group No_") END as "Monteur_Gruppe",
	   (-1 * datediff(day, (getdate()), T1."Leaving Date")) as "Tage Heute Leaving Date",
	   (getdate()) - 1 as "Heute",
	   T1."Pay Group No_" as "Monteur_Gruppe_ori",
	   CASE WHEN (T2."Time Account No_" IN ('380')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Mutterschutz",
	   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 ('450')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Feiertag",
	   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 ('357')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Prüfung",
	   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 ('355')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Schulung extern",
	   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 ('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 ('351')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Urlaub unbezahlt",
	   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 ('352')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Sonderurlaub",
	   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 ('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 ('373','374')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank Kur",
	   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 ('371')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Krank ohne AUB",
	   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 ('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",
	   T2."Time Account No_" as "Time Account No",
	   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 ('300')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Fehlzeit ungenehmigt",
	   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 ('100')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Anwesenheit",
	   CASE WHEN (T2."Time Account No_" IN ('1200')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Sollzeit",
	   T3."Pay Type 1" as "Pay Type 1",
	   T3."Character" as "Character",
	   T3."Symbol" as "Symbol",
	   T3."Description 2" as "Description 2",
	   T3."Description" as "Description",
	   T3."No_" as "No",
	   T2."Record protected" as "Record Protected",
	   T2."Generating Function" as "Generating Function",
	   T2."TA Formatting" as "Ta Formatting",
	   T2."TA Class (Vacation Reduction)" as "Ta Class (vacation Reduction)",
	   T2."TA Class (Employee Info)" as "Ta Class (employee Info)",
	   T2."TA Class (Individual)" as "Ta Class (individual)",
	   T2."TA Class (Time Processing)" as "Ta Class (time Processing)",
	   T2."TA Class (Absent Days)" as "Ta Class (absent Days)",
	   T2."TA Class (Statistics)" as "Ta Class (statistics)",
	   T2."TA Class (General)" as "Ta Class (general)",
	   (convert(float, T2."Time Account Value")) as "Time Account Value",
	   T2."Order No_" as "Order No",
	   T2."Department No_" as "Department No_",
	   T2."Employee No_" as "Employee No",
	   (((convert(float, T1."Rem_ Vac_ Days taken")))) - (((convert(float, T1."Rem_ Vac_ Days planned")))) as "geplanter Urlaub",
	   ((convert(float, T1."Rem_ Vac_ Days planned"))) as "Rem Vac Days Planned",
	   ((((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 taken"))) as "Rem Vac Days Taken",
	   (((convert(float, T1."Std_ Vac_ Entitlem_ Days")))) + (((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_")))) as "Gesamturlaubsanspruch",
	   ((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_"))) as "Std Vac Days Car Fwd Prev Y",
	   ((convert(float, T1."Std_ Vac_ Entitlem_ Days"))) as "Std Vac Entitlem Days",
	   T1."Rating PEREAS" as "Rating Pereas",
	   T1."Resource No_" as "Resource No",
	   (convert(float, T1."Efficiency %")) as "Efficiency %",
	   T1."Task Type Group" as "Task Type Group",
	   T1."Department Code" as "Department Code",
	   T1."Absent" as "Absent",
	   T1."Present" as "Present",
	   T1."Values Car_ Fwd_ Inactive" as "Values Car Fwd Inactive",
	   (convert(float, T1."Rem_ Handic_ Vac_ Days planned")) as "Rem Handic Vac Days Planned",
	   (convert(float, T1."Rem_ Handic_ Vac_ Days taken")) as "Rem Handic Vac Days Taken",
	   (convert(float, T1."Rem_ Vac_ Hrs_ Planned")) as "Rem Vac Hrs Planned",
	   (convert(float, T1."Rem_ Vac_ Hours taken")) as "Rem Vac Hours Taken",
	   (convert(float, T1."Spec_ Vac_2 Hours Car_ Fwd_")) as "Spec Vac 2 Hours Car Fwd",
	   (convert(float, T1."Handic_ Vac_1 Hours Car_ Fwd_")) as "Handic Vac 1 Hours Car Fwd",
	   (convert(float, T1."Std_ Vac_Car_Fwd_Prev_Y_(H)")) as "Std Vac Car Fwd Prev Y (h)",
	   (convert(float, T1."Special Vac_2 Entitlement (H)")) as "Special Vac 2 Entitlement (h)",
	   (convert(float, T1."Handic_ Vac_1 Entitlement Hrs_")) as "Handic Vac 1 Entitlement Hrs",
	   (convert(float, T1."Standard Vac_ Entitlement Hrs_")) as "Standard Vac Entitlement Hrs",
	   (convert(float, T1."Special Vac_3 Days Car_ Fwd_")) as "Special Vac 3 Days Car Fwd",
	   (convert(float, T1."Special Vac_2 Days Car_ Fwd_")) as "Special Vac 2 Days Car Fwd",
	   (convert(float, T1."Handic_ Vac_1 Days Car_ Fwd_")) as "Handic Vac 1 Days Car Fwd",
	   (convert(float, T1."Spec_ Vac_3 Entitlem_ Days")) as "Spec Vac 3 Entitlem Days",
	   (convert(float, T1."Spec_ Vac_2 Entitlem_ Days")) as "Spec Vac 2 Entitlem Days",
	   (convert(float, T1."Handic_ Vac_ Entitlem_ Days")) as "Handic Vac Entitlem Days",
	   T1."Time Object No_" as "Time Object No",
	   T1."Group No_ 3" as "Group No 3",
	   T1."Group No_ 2" as "Group No 2",
	   T1."Group No_ 1" as "Group No 1",
	   T1."Department No_" as "Department No",
	   T1."Leaving Date" as "Leaving Date",
	   T1."Employment Date" as "Employment Date",
	   T1."First Name" as "First Name",
	   T1."Last Name" as "Last Name",
	   T1."Name" as "Name",
	   T1."ID No_" as "Id No"
from "NAVISION"."import"."Employee_T" T1,
	"NAVISION"."import"."Time_Entry_T" T2,
	"NAVISION"."import"."Time_Account_T" T3
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 (T1."No_" <> '11724'))
) D1
-- order by "Datum" asc,"No_2" asc,"Time Account No" asc
) D5
) D4