select distinct "Service Order No" as "Service Order No",
	   SUM(c6 at "Service Order No") OVER (partition by "Service Order No") as "Summe verk.Stunden",
	   "Datum" as "Datum",
	   "Summe Umsatz Lohn" as "Summe Umsatz Lohn",
	   "Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan"
from 
(select "Service Order No" as "Service Order No",
	   "Datum" as "Datum",
	   "Summe Umsatz Lohn" as "Summe Umsatz Lohn",
	   "Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan",
	   SUM(c7) OVER (partition by "Service Order No") as c6
from 
(select "Service Order No" as "Service Order No",
	   "Datum" as "Datum",
	   "Summe Umsatz Lohn" as "Summe Umsatz Lohn",
	   "Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan",
	   ((c20) / COUNT(c21) OVER (partition by c22)) as c7
from 
(select "Service Order No",
	   "Datum",
	   SUM(c18) OVER (partition by "Service Order No") as "Summe Umsatz Lohn",
	   SUM(c17) OVER (partition by "Service Order No") as "Summe Umsatz Lohn Plan",
	   c15 as c20,
	   c19 as c21,
	   c13 as c22
from 
(select (T1."Service Order No_" + T1."Document No_" + (((T1."Service Order Line No_")))) as c13,
	   T1."Service Order No_" as "Service Order No",
	   convert(float, T1."Qty_ (Hour)") as c15,
	   T2."Posting Date" as "Datum",
	   (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) * CASE WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(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 ((convert(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" >= convert(datetime, '2021-01-01 00:00:00.000')))
) D3
) D6
) D2
) D1
-- order by "Service Order No" asc