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

select distinct "Employee No" as "Employee No",
	   "Order No" as "Order No",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "No" as "No",
	   "Last Name" as "Last Name",
	   "First Name" as "First Name",
	   "Monteur_vor_Abgr_AZUBI" as "Monteur_vor_Abgr_AZUBI",
	   "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori",
	   "Tage Heute Leaving Date" as "Tage Heute Leaving Date",
	   "Monteur_Gruppe_vor_Abgr_AZUBI" as "Monteur_Gruppe_vor_Abgr_AZUBI",
	   "produktiv/unproduktiv" as "produktiv/unproduktiv",
	   "Monatserster" as "Monatserster",
	   "Monatsletzter" as "Monatsletzter",
	   "Heute" as "Heute",
	   "Employment Date" as "Employment Date",
	   "Task Type Group" as "Task Type Group",
	   "Department No" as "Department No",
	   "Summe produktiv für Auftrag" as "Summe produktiv für Auftrag",
	   "Order Number u Monteur" as "Order Number u Monteur",
	   SUM(c58) OVER (partition by c59) as "Summe produktiv Monteur Auftrag",
	   CASE WHEN (("Summe produktiv für Auftrag") <> 0) THEN (SUM(c58) OVER (partition by c59) / ("Summe produktiv für Auftrag")) ELSE null END as "Anteil Monteur Auftrag",
	   "Pay Group No" as "Pay Group No",
	   "Begin Work Permit" as "Begin Work Permit",
	   "Monteur_Gruppe" as "Monteur_Gruppe",
	   "Monteur" as "Monteur",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name"
from 
(select "Employee No",
	   "Order No",
	   "Hauptbetrieb",
	   "Standort",
	   "No",
	   "Last Name",
	   "First Name",
	   "Monteur_vor_Abgr_AZUBI",
	   "Monteur_Gruppe_ori",
	   "Tage Heute Leaving Date",
	   "Monteur_Gruppe_vor_Abgr_AZUBI",
	   "produktiv/unproduktiv",
	   convert(date, '2022-10-18') as "Monatserster",
	   convert(date, '2022-10-18') as "Monatsletzter",
	   convert(date, '2022-10-18') as "Heute",
	   "Employment Date",
	   "Task Type Group",
	   "Department No",
	   SUM(c57) OVER (partition by "Order No") as "Summe produktiv für Auftrag",
	   "Order Number u Monteur",
	   "Monteur_Gruppe_ori" as "Pay Group No",
	   "Begin Work Permit",
	   "Monteur_Gruppe",
	   "Monteur",
	   "Hauptbetrieb" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name",
	   c57 as c58,
	   c35 as c59
from 
(select ((T1."Order No_" + ' ' + (CASE WHEN (T2."Last Name" IN ('Schmid','Wittenberg')) THEN (T2."First Name" + ' ' + T2."Last Name" + '_' + T1."Client_DB") ELSE (T2."First Name" + ' ' + T2."Last Name") END))) as c35,
	   T1."Order No_" as "Order No",
	   CASE WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name",
	   (CASE WHEN ((left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((left(T2."Department No_",2))) END) as "Standort",
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   T1."Client_DB" as "Hauptbetrieb",
	   CASE WHEN (T1."Date" < T2."Begin Work Permit") THEN ((CASE WHEN (T2."Last Name" IN ('Schmid','Wittenberg')) THEN (T2."First Name" + ' ' + T2."Last Name" + '_' + T1."Client_DB") ELSE (T2."First Name" + ' ' + T2."Last Name") END) + ' - ' + 'AZG4') ELSE ((CASE WHEN (T2."Last Name" IN ('Schmid','Wittenberg')) THEN (T2."First Name" + ' ' + T2."Last Name" + '_' + T1."Client_DB") ELSE (T2."First Name" + ' ' + T2."Last Name") END)) END as "Monteur",
	   CASE WHEN (T1."Date" < T2."Begin Work Permit") THEN ('AZG4') ELSE ((CASE WHEN ((((-1 * datediff(day, (getdate()), T2."Leaving Date"))) > 20) and (T2."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Pay Group No_") END)) END as "Monteur_Gruppe",
	   T2."Begin Work Permit" as "Begin Work Permit",
	   T2."Pay Group No_" as "Monteur_Gruppe_ori",
	   T1."Order No_" + ' ' + (CASE WHEN (T2."Last Name" IN ('Schmid','Wittenberg')) THEN (T2."First Name" + ' ' + T2."Last Name" + '_' + T1."Client_DB") ELSE (T2."First Name" + ' ' + T2."Last Name") END) as "Order Number u Monteur",
	   T2."Department No_" as "Department No",
	   T2."Task Type Group" as "Task Type Group",
	   T2."Employment Date" as "Employment Date",
	   CASE WHEN (T2."Task Type Group" IN ('MONTEURE','PROD MEIST')) THEN ('prod. Personal') WHEN (T2."Task Type Group" IN ('VERWALTUNG')) THEN ('unprod. Personal') ELSE null END as "produktiv/unproduktiv",
	   CASE WHEN ((((-1 * datediff(day, (getdate()), T2."Leaving Date"))) > 20) and (T2."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Pay Group No_") END as "Monteur_Gruppe_vor_Abgr_AZUBI",
	   (-1 * datediff(day, (getdate()), T2."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN (T2."Last Name" IN ('Schmid','Wittenberg')) THEN (T2."First Name" + ' ' + T2."Last Name" + '_' + T1."Client_DB") ELSE (T2."First Name" + ' ' + T2."Last Name") END as "Monteur_vor_Abgr_AZUBI",
	   T2."First Name" as "First Name",
	   T2."Last Name" as "Last Name",
	   T2."No_" as "No",
	   T1."Employee No_" as "Employee No",
	   ((CASE WHEN (T3."Code" IN ('111','116','117','118')) THEN (((convert(float, T1."Duration")) * (convert(float, T2."Efficiency %")) / 100)) ELSE (0) END) + 0 + (CASE WHEN (T3."Code" IN ('150','160')) THEN (((convert(float, T1."Duration")) * (convert(float, T2."Efficiency %")) / 100)) ELSE (0) END)) as c57
from (("NAVISION"."import"."Task_Acquisition_Ledger_Entry" T1 left outer join "NAVISION"."import"."Employee_T" T2 on (T1."Employee No_" = T2."No_") and (T1."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Task_Type" T3 on (T1."Task Type Code" = T3."Code") and (T1."Client_DB" = T3."Client_DB"))
where (((((((T1."Sorting" = 1) and (T1."Corrected" = 0)) and (T1."Date" >= T2."Employment Date")) and (T1."Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and ((T2."Leaving Date" >= convert(datetime, '2014-01-01 00:00:00.000')) or (T2."Leaving Date" = convert(datetime, '1753-01-01 00:00:00.000')))) and (T1."Order No_" <> '')) and ((CASE WHEN ((((-1 * datediff(day, (getdate()), T2."Leaving Date"))) > 20) and (T2."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Pay Group No_") END) <> ''))
) D1
) D4
-- order by "Order No" asc,"Employee No" asc