select "Entry No" as "Entry No", "Employee No" as "Employee No", "Date" as "Date", "Time Value" as "Time Value", "Begin End" as "Begin End", "Cause Of Absence Code" as "Cause Of Absence Code", "Terminal Code" as "Terminal Code", "Department Code" as "Department Code", "Make Code" as "Make Code", "User Id" as "User Id", "Reason Code" as "Reason Code", "Sorting" as "Sorting", "Posting Date" as "Posting Date", "Order No" as "Order No", "Service Job No" as "Service Job No", "Closed" as "Closed", "Task Type Code" as "Task Type Code", "Link No" as "Link No", "Task Ledger Entry No" as "Task Ledger Entry No", "Corrected" as "Corrected", "Starting Time" as "Starting Time", "Ending Time" as "Ending Time", "Duration" as "Duration", "Time Acquisition Posted" as "Time Acquisition Posted", "Branch Code" as "Branch Code", "Approved By User Id" as "Approved By User Id", "Approved" as "Approved", "Automatic Posting" as "Automatic Posting", "Subject To Approval" as "Subject To Approval", "Applied-to Entry No" as "Applied-to Entry No", "Ledger Entry Origin" as "Ledger Entry Origin", "Correction Mode" as "Correction Mode", "Posting Time" as "Posting Time", "Service Advisor No" as "Service Advisor No", "Resource Group No" as "Resource Group No", "Tested" as "Tested", "Location Code" as "Location Code", "Leave" as "Leave", "Zeitdauer" as "Zeitdauer", RSUM("Duration") as "Summe (Zeitdauer) Nr.1", "Code" as "Code", "Description_Task_Type" as "Description_Task_Type", "Task Statistic Group" as "Task Statistic Group", "Hauptbetrieb" as "Hauptbetrieb", "Standort" as "Standort", "No" as "No", "Last Name" as "Last Name", "First Name" as "First Name", "Monteur" as "Monteur", "W-variabel Stunden" as "W-variabel Stunden", "Datum" as "Datum", "Order Number" as "Order Number", "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", "Monteur_Gruppe_ori_alt" as "Monteur_Gruppe_ori_alt", "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", "W-fix Stunden" as "W-fix Stunden", "Employment Date" as "Employment Date", "Anwesenheit" as "Anwesenheit", "Nacharbeit" as "Nacharbeit", "Leerlauf" as "Leerlauf", "Anwesend unprod." as "Anwesend unprod.", "Bardusch" as "Bardusch", "Schulung intern" as "Schulung intern", "Schulung extern" as "Schulung extern", "Betriebsrat" as "Betriebsrat", "Krank" as "Krank", "Arzt" as "Arzt", "Urlaub" as "Urlaub", "Sonderurlaub" as "Sonderurlaub", "Fehlstunden" as "Fehlstunden", "Meistervertretung" as "Meistervertretung", "Fahrdienst" as "Fahrdienst", "Zeitausgleich" as "Zeitausgleich", "Abschleppen" as "Abschleppen", "produktiv" as "produktiv", "Verwaltung" as "Verwaltung", "Wartezeit" as "Wartezeit", "Reifen" as "Reifen", "Werkstatt" as "Werkstatt", "Werkstatt Samstag" as "Werkstatt Samstag", "Kostenstelle" as "Kostenstelle", "unproduktiv" as "unproduktiv", "abwesend_ori" as "abwesend_ori", "Extern" as "Extern", "GWL" as "GWL", "Intern" as "Intern", "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", "TC" as "TC", "Task Type Group" as "Task Type Group", "ORGA" as "ORGA", "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori", "Make Code_Employee_T" as "Make Code_Employee_T", "Company No_Employee_T" as "Company No_Employee_T", "Anwesenheit Mech/Karo/Lack_ori" as "Anwesenheit Mech/Karo/Lack_ori", "Anwesenheit Meister" as "Anwesenheit Meister", "Produktbuchungsgruppe" as "Produktbuchungsgruppe", "Anwesenheit Mech/Karo/Lack" as "Anwesenheit Mech/Karo/Lack" from (select T1."Entry No_" as "Entry No", T1."Employee No_" as "Employee No", T1."Date" as "Date", T1."Time Value" as "Time Value", T1."Begin_End" as "Begin End", T1."Cause of Absence Code" as "Cause Of Absence Code", T1."Terminal Code" as "Terminal Code", T1."Department Code" as "Department Code", T1."Make Code" as "Make Code", T1."User ID" as "User Id", T1."Reason Code" as "Reason Code", T1."Sorting" as "Sorting", T1."Posting Date" as "Posting Date", T1."Order No_" as "Order No", T1."Service Job No_" as "Service Job No", T1."Closed" as "Closed", T1."Task Type Code" as "Task Type Code", T1."Link No_" as "Link No", T1."Task Ledger Entry No_" as "Task Ledger Entry No", T1."Corrected" as "Corrected", T1."Starting Time" as "Starting Time", T1."Ending Time" as "Ending Time", T1."Duration" as "Duration", T1."Time Acquisition Posted" as "Time Acquisition Posted", T1."Branch Code" as "Branch Code", T1."Approved by User ID" as "Approved By User Id", T1."Approved" as "Approved", T1."Automatic Posting" as "Automatic Posting", T1."Subject to Approval" as "Subject To Approval", T1."Applied-to Entry No_" as "Applied-to Entry No", T1."Ledger Entry Origin" as "Ledger Entry Origin", T1."Correction Mode" as "Correction Mode", T1."Posting Time" as "Posting Time", T1."Service Advisor No_" as "Service Advisor No", T1."Resource Group No_" as "Resource Group No", T1."Tested" as "Tested", T1."Location Code" as "Location Code", T1."Leave" as "Leave", T1."Duration" as "Zeitdauer", T2."Code" as "Code", T2."Description" as "Description_Task_Type", T2."Task Statistic Group" as "Task Statistic Group", '1' as "Hauptbetrieb", T3."Filialcode" as "Standort", T4."No_" as "No", T4."Last Name" as "Last Name", T4."First Name" as "First Name", T4."First Name" + ' ' + T4."Last Name" as "Monteur", 0 + (CASE WHEN (T2."Code" IN ('211','212')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('242','244')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('112','113','114')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('216')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('331')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('336')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('245')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('243')) THEN (T1."Duration") ELSE (0) END) + 0 as "W-variabel Stunden", T1."Date" as "Datum", T1."Order No_" as "Order Number", 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", CASE WHEN (T3."Global Dimension 1 Code" IN ('40','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END as "Monteur_Gruppe_ori_alt", (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','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END)) END as "Monteur_Gruppe", CASE WHEN (T3."Global Dimension 1 Code" IN ('40','41','44','45')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as "produktiv/unproduktiv", (convert(datetime, T1."Date" - cinterval(day(T1."Date") - 1))) as "Monatserster", (convert(datetime, lastday((T1."Date")))) as "Monatsletzter", (now()) as "Heute", CASE WHEN (((now())) BETWEEN ((convert(datetime, T1."Date" - cinterval(day(T1."Date") - 1)))) AND ((convert(datetime, lastday((T1."Date")))))) THEN (T1."Date") ELSE null END as "Datum Tagesbericht", (CASE WHEN (T2."Code" IN ('332')) THEN (T1."Duration") ELSE (0) END) as "W-fix Stunden", T4."Employment Date" as "Employment Date", CASE WHEN (not T2."Code" IN ('ARZT','PAUSE')) THEN (T1."Duration") ELSE (0) END as "Anwesenheit", CASE WHEN (T2."Code" IN ('NACHARBEIT')) THEN (T1."Duration") ELSE (0) END as "Nacharbeit", CASE WHEN (T2."Code" IN ('LEERLAUF')) THEN (T1."Duration") ELSE (0) END as "Leerlauf", CASE WHEN (T2."Code" IN ('ANWESEND','DA')) THEN (T1."Duration") ELSE (0) END as "Anwesend unprod.", CASE WHEN (T2."Code" IN ('BARDUSCH')) THEN (T1."Duration") ELSE (0) END as "Bardusch", CASE WHEN (T2."Code" IN ('SCHUL-INT')) THEN (T1."Duration") ELSE (0) END as "Schulung intern", CASE WHEN (T2."Code" IN ('LEHRGANG')) THEN (T1."Duration") ELSE (0) END as "Schulung extern", CASE WHEN (T2."Code" IN ('BETRIEBSR')) THEN (T1."Duration") ELSE (0) END as "Betriebsrat", CASE WHEN (T2."Code" IN ('K')) THEN (T1."Duration") ELSE (0) END as "Krank", CASE WHEN (T2."Code" IN ('ARZT')) THEN (T1."Duration") ELSE (0) END as "Arzt", CASE WHEN (T2."Code" IN ('URLAUB','U-HALBN','U-HALBV')) THEN (T1."Duration") ELSE (0) END as "Urlaub", CASE WHEN (T2."Code" IN ('URL-SONDER')) THEN (T1."Duration") ELSE (0) END as "Sonderurlaub", 0 as "Fehlstunden", CASE WHEN (T2."Code" IN ('MEISTERV')) THEN (T1."Duration") ELSE (0) END as "Meistervertretung", CASE WHEN (T2."Code" IN ('FAHR')) THEN (T1."Duration") ELSE (0) END as "Fahrdienst", CASE WHEN (T2."Code" IN ('ZA')) THEN (T1."Duration") ELSE (0) END as "Zeitausgleich", CASE WHEN (T2."Code" IN ('ABSCHLEPP')) THEN (T1."Duration") ELSE (0) END as "Abschleppen", CASE WHEN (T2."Code" IN ('AELEK','AKARO','ALACK','AMECH','WERT')) THEN (T1."Duration") ELSE (0) END as "produktiv", CASE WHEN (T2."Code" IN ('VERWALTUNG')) THEN (T1."Duration") ELSE (0) END as "Verwaltung", CASE WHEN (T2."Code" IN ('WARTEZEIT')) THEN (T1."Duration") ELSE (0) END as "Wartezeit", CASE WHEN (T2."Code" IN ('REIFEN')) THEN (T1."Duration") ELSE (0) END as "Reifen", CASE WHEN (T2."Code" IN ('WERKSTATT')) THEN (T1."Duration") ELSE (0) END as "Werkstatt", CASE WHEN (T2."Code" IN ('WERK-SAMST')) THEN (T1."Duration") ELSE (0) END as "Werkstatt Samstag", T3."Global Dimension 1 Code" + ' - ' + T5."Name" as "Kostenstelle", (CASE WHEN (T2."Code" IN ('ABSCHLEPP')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ANWESEND','DA')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('BARDUSCH')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('BETRIEBSR')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('FAHR')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('LEERLAUF')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('MEISTERV')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('NACHARBEIT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('REIFEN')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('SCHUL-INT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('TC')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('VERWALTUNG')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('WARTEZEIT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('WERK-SAMST')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('WERKSTATT')) THEN (T1."Duration") ELSE (0) END) as "unproduktiv", (CASE WHEN (T2."Code" IN ('LEHRGANG')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('K')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ARZT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('URLAUB','U-HALBN','U-HALBV')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('URL-SONDER')) THEN (T1."Duration") ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('ZA')) THEN (T1."Duration") ELSE (0) END) as "abwesend_ori", 0 as "Extern", 0 as "GWL", 0 as "Intern", T3."Filialcode" as "Filialcode_Employee", T3."Global Dimension 1 Code" as "Kostenstellencode_Employee", T5."Name" as "Name_Kostenstelle", CASE WHEN (T3."Filialcode" IN ('00','05')) THEN ('Königsberger Str.') WHEN (T3."Filialcode" IN ('10','15')) THEN ('Kevelaer') WHEN (T3."Filialcode" IN ('20','25')) THEN ('Brunnenstr.') WHEN (T3."Filialcode" IN ('30','35')) THEN ('Weilerswist') WHEN (T3."Filialcode" IN ('50','55')) THEN ('Benrath') WHEN (T3."Filialcode" IN ('40','45')) THEN ('Ratingen') ELSE null END as "Betrieb", CASE WHEN (T3."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T3."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') ELSE ('Monteur') END as "Monteur_Gruppe_2", T3."Arbeitsvertragscode" as "Arbeitsvertragscode", CASE WHEN (T2."Code" IN ('TC')) THEN (T1."Duration") ELSE (0) END as "TC", T4."Task Type Group" as "Task Type Group", CASE WHEN (T2."Code" IN ('ORGA')) THEN (T1."Duration") ELSE (0) END as "ORGA", T4."Task Type Group" as "Monteur_Gruppe_ori", T4."Make Code" as "Make Code_Employee_T", T4."Company No_" as "Company No_Employee_T", CASE WHEN (((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','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END)) END) IN ('Mechanik','Lack','Karosserie')) and (T3."Arbeitsvertragscode" <> 'MEIST')) THEN ((CASE WHEN (not T2."Code" IN ('ARZT','PAUSE')) THEN (T1."Duration") ELSE (0) END)) ELSE (0) END as "Anwesenheit Mech/Karo/Lack_ori", CASE WHEN (T3."Arbeitsvertragscode" IN ('MEIST')) THEN ((CASE WHEN (not T2."Code" IN ('ARZT','PAUSE')) THEN (T1."Duration") ELSE (0) END)) ELSE (0) END as "Anwesenheit Meister", CASE WHEN ((CASE WHEN (T3."Global Dimension 1 Code" IN ('40','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END) LIKE '%MECH%') THEN ('W_MECH') WHEN ((CASE WHEN (T3."Global Dimension 1 Code" IN ('40','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END) LIKE '%KAR%') THEN ('W_KARO') WHEN ((CASE WHEN (T3."Global Dimension 1 Code" IN ('40','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END) LIKE '%LACK%') THEN ('W_LACK') ELSE ('Zeit Meister') END as "Produktbuchungsgruppe", ((CASE WHEN (((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','41')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('44')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('45')) THEN ('Lack') ELSE null END)) END) IN ('Mechanik','Lack','Karosserie')) and (T3."Arbeitsvertragscode" <> 'MEIST')) THEN ((CASE WHEN (not T2."Code" IN ('ARZT','PAUSE')) THEN (T1."Duration") ELSE (0) END)) ELSE (0) END)) as "Anwesenheit Mech/Karo/Lack" from (("CARLO"."import"."Task_Acquisition_Ledger_Entry" T1 left outer join "CARLO"."import"."Task_Type" T2 on (T1."Task Type Code" = T2."Code") and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee_T" T4 on (T4."No_" = T1."Employee No_") and (T4."Client_DB" = T1."Client_DB")), ("CARLO"."import"."Employee" T3 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."Sorting" = 1) and (T1."Corrected" = 0)) and (T1."Date" >= T4."Employment Date")) and ((od_year(T1."Date")) >= (od_year((now()))) - 2)) and (T3."Arbeitsvertragscode" IN ('MECH'))) and (T3."Global Dimension 1 Code" IN ('40','41','44','45'))) and (T4."Leaving Date" = convert(datetime, '1753-01-01 00:00:00.000'))) or (((T1."Sorting" = 1) and (T1."Corrected" = 0)) and (T4."Leaving Date" = convert(datetime, '1753-01-01 00:00:00.000')))) -- order by "Employee No" asc,"Date" asc ) D1