C11/System/NAVISION/IQD/Zeiten/Labor_Ledger_entry_Export.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\zeiten\Labor_Ledger_entry_Export.imr
TITLE,Labor_Ledger_entry_Export.imr
BEGIN SQL
select distinct c1 as c1,
	   XSUM(c6 at c1 for c1) as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5
from 
(select c1 as c1,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   XSUM(c7 for c1) as c6
from 
(select c1 as c1,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   ((c20) / XCOUNT(c21 for c22)) as c7
from 
(select c14 as c1,
	   c16 as c3,
	   XSUM(c18 for c14) as c4,
	   XSUM(c17 for c14) as c5,
	   c15 as c20,
	   c19 as c21,
	   c13 as c22
from 
(select (T1."Service Order No_" || T1."Document No_" || (cast_numberToString(cast_integer(T1."Service Order Line No_")))) as c13,
	   T1."Service Order No_" as c14,
	   cast_float(T1."Qty_ (Hour)") as c15,
	   T2."Posting Date" as c16,
	   (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) * CASE WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.15) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (7.67) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (9.41) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (9.07) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (7.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (8.20) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (8.20) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (8.20) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Qty_ (Hour)"))) ELSE (0) END * (cast_float(T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.70) ELSE (0) END) as c17,
	   (CASE WHEN (T3."Type" = 4) THEN ((cast_float(T3."Total Price"))) ELSE (0) END) as c18,
	   T1."Entry No_" as c19
from "NAVISION"."import"."Archived_Service_Header" T2,
	("NAVISION"."import"."Service_Ledger_Entry" T3 left outer join "NAVISION"."import"."Labor_Ledger_Entry" T1 on (((T3."Order No_" = T1."Service Order No_") and (T3."Order Line No_" = T1."Service Order Line No_")) and (T3."Assoc_ Entry" = T1."Entry No_")) and (T3."Client_DB" = T1."Client_DB"))
where ((T2."No_" = T1."Service Order No_") and (T2."Client_DB" = T1."Client_DB"))
and (((T1."Entry Type" = 0) and (T3."Entry Type" = 0)) and (T2."Posting Date" >= TIMESTAMP '2021-01-01 00:00:00.000'))
) D3
) D6
) D2
) D1
order by c1 asc

END SQL
COLUMN,0,Service Order No
COLUMN,1,Summe verk.Stunden
COLUMN,2,Datum
COLUMN,3,Summe Umsatz Lohn
COLUMN,4,Summe Umsatz Lohn Plan