COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\zeiten\stempelzeiten_monteur_auftrag_export.imr
TITLE,stempelzeiten_monteur_auftrag_export.imr
BEGIN SQL
select distinct c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   XSUM(c58 for c59) as c21,
	   CASE WHEN ((c19) <> 0) THEN (XSUM(c58 for c59) / (c19)) ELSE null END as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30
from 
(select c56 as c1,
	   c36 as c2,
	   c40 as c3,
	   c38 as c4,
	   c55 as c5,
	   c54 as c6,
	   c53 as c7,
	   c52 as c8,
	   c44 as c9,
	   c51 as c10,
	   c50 as c11,
	   c49 as c12,
	   DATE '2022-10-18' as c13,
	   DATE '2022-10-18' as c14,
	   DATE '2022-10-18' as c15,
	   c48 as c16,
	   c47 as c17,
	   c46 as c18,
	   XSUM(c57 for c36) as c19,
	   c45 as c20,
	   c44 as c23,
	   c43 as c24,
	   c42 as c25,
	   c41 as c26,
	   c40 as c27,
	   c39 as c28,
	   c38 as c29,
	   c37 as c30,
	   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 c36,
	   CASE WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as c37,
	   (CASE WHEN ((od_left(T2."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T2."Department No_",2))) END) as c38,
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as c39,
	   T1."Client_DB" as c40,
	   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 c41,
	   CASE WHEN (T1."Date" < T2."Begin Work Permit") THEN ('AZG4') ELSE ((CASE WHEN ((((extract(DAY FROM (now()) - T2."Leaving Date"))) > 20) and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Pay Group No_") END)) END as c42,
	   T2."Begin Work Permit" as c43,
	   T2."Pay Group No_" as c44,
	   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 c45,
	   T2."Department No_" as c46,
	   T2."Task Type Group" as c47,
	   T2."Employment Date" as c48,
	   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 c49,
	   CASE WHEN ((((extract(DAY FROM (now()) - T2."Leaving Date"))) > 20) and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Pay Group No_") END as c50,
	   (extract(DAY FROM (now()) - T2."Leaving Date")) as c51,
	   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 c52,
	   T2."First Name" as c53,
	   T2."Last Name" as c54,
	   T2."No_" as c55,
	   T1."Employee No_" as c56,
	   ((CASE WHEN (T3."Code" IN ('111','116','117','118')) THEN (((cast_float(T1."Duration")) * (cast_float(T2."Efficiency %")) / 100)) ELSE (0) END) + 0 + (CASE WHEN (T3."Code" IN ('150','160')) THEN (((cast_float(T1."Duration")) * (cast_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" >= TIMESTAMP '2021-01-01 00:00:00.000')) and ((T2."Leaving Date" >= TIMESTAMP '2014-01-01 00:00:00.000') or (T2."Leaving Date" = TIMESTAMP '1753-01-01 00:00:00.000'))) and (T1."Order No_" <> '')) and ((CASE WHEN ((((extract(DAY FROM (now()) - T2."Leaving Date"))) > 20) and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Pay Group No_") END) <> ''))
) D1
) D4
order by c2 asc,c1 asc

END SQL
COLUMN,0,Employee No
COLUMN,1,Order No
COLUMN,2,Hauptbetrieb
COLUMN,3,Standort
COLUMN,4,No
COLUMN,5,Last Name
COLUMN,6,First Name
COLUMN,7,Monteur_vor_Abgr_AZUBI
COLUMN,8,Monteur_Gruppe_ori
COLUMN,9,Tage Heute Leaving Date
COLUMN,10,Monteur_Gruppe_vor_Abgr_AZUBI
COLUMN,11,produktiv/unproduktiv
COLUMN,12,Monatserster
COLUMN,13,Monatsletzter
COLUMN,14,Heute
COLUMN,15,Employment Date
COLUMN,16,Task Type Group
COLUMN,17,Department No
COLUMN,18,Summe produktiv für Auftrag
COLUMN,19,Order Number u Monteur
COLUMN,20,Summe produktiv Monteur Auftrag
COLUMN,21,Anteil Monteur Auftrag
COLUMN,22,Pay Group No
COLUMN,23,Begin Work Permit
COLUMN,24,Monteur_Gruppe
COLUMN,25,Monteur
COLUMN,26,Hauptbetrieb_ID
COLUMN,27,Hauptbetrieb_Name
COLUMN,28,Standort_ID
COLUMN,29,Standort_Name