select c65 as "Invoice Type", c64 as "Invoice Number", c63 as "Subsidiary", c62 as "Invoice Date", c61 as "Service Date", c60 as "Is Canceled", c59 as "Vehicle Number", c58 as "Invoice_Type_Invoice_Number", c57 as "Hauptbetrieb", c56 as "Standort", c55 as "Serviceberater", c54 as "Umsatzart", c53 as "Fabrikat", c53 as "Description_Makes", c52 as "Description_Models", c51 as "Model", c50 as "Fahrzeug", c49 as "Kostenstelle", c48 as "Marke", 'Service' as "Auftragsart", c47 as "Geschäftsart", c46 as "Kunde", c45 as "Time Units", c44 as "Time Units_Zahl", 10 as "AW/Std.", c43 as "verk. Std._", c37 as "Order Number", c42 as "Order Number_Rg_Ausg", c41 as "Kundenart", c40 as "Summe Duration Minutes", c39 as "Order Number_ben_AW", 1 as "Anzahl_Datensätze_1", COUNT(c39) OVER (partition by c37) as "Anzahl_Datensätze_2", (c38) / (COUNT(c39) OVER (partition by c37)) as "ben. Std." from (select (CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) + ' - ' + T2."Serviceberater" + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."family_name") ELSE ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name") END)) ELSE ((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."family_name") ELSE ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name") END)) END) as c37, T9."Summe_Duration_Minutes" / 60 as c38, T9."order_number" as c39, T9."Summe_Duration_Minutes" as c40, (substring((convert(varchar(50), T8."employee_number")), 1, 4)) + ' - ' + T8."name" as c41, CASE WHEN ((day((getdate()) - (convert(datetime, T1."invoice_date")))) <= 4) THEN ((CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) + ' - ' + T2."Serviceberater" + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."family_name") ELSE ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name") END)) ELSE ((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."family_name") ELSE ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name") END)) END)) ELSE null END as c42, ((convert(float, T7."time_units"))) / 10 as c43, (convert(float, T7."time_units")) as c44, T7."time_units" as c45, CASE WHEN (T3."first_name" IS NULL) THEN ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."family_name") ELSE ((substring((convert(varchar(50), T3."customer_number")), 1, 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name") END as c46, T3."zip_code" as c47, CASE WHEN (T4."description" = 'Ford') THEN ('1') WHEN (T4."description" = 'Nissan') THEN ('3') WHEN (T4."description" = 'Suzuki') THEN ('2') ELSE ('9') END as c48, CASE WHEN ((substring((convert(varchar(50), T6."type")), 1, 1)) IN ('1','6')) THEN ('41 - After Sales Kundendienst eigene Werkstatt') WHEN (T6."type" IN (40,41,44,45,46,47,48,49,88,89,50,90)) THEN ('41 - After Sales Kundendienst eigene Werkstatt') WHEN ((substring((convert(varchar(50), T6."type")), 1, 1)) IN ('2')) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN (T6."type" IN (42,92)) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN ((substring((convert(varchar(50), T6."type")), 1, 1)) IN ('3')) THEN ('45 - After Sales Kundendienst eigene Lackiererei') WHEN (T6."type" IN (43,93)) THEN ('45 - After Sales Kundendienst eigene Lackiererei') ELSE null END as c49, (substring((substring((convert(varchar(50), T1."vehicle_number")), 1, 5)) + ' - ' + T5."description", 1, 100)) as c50, (substring((upper(T5."description")), 1, 3)) as c51, T5."description" as c52, T4."description" as c53, CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T3."customer_number" = 100001)) THEN ('intern') ELSE ('extern') END as c54, T2."Serviceberater" as c55, '0' + (convert(varchar(50), T1."subsidiary")) as c56, CASE WHEN ((substring((convert(varchar(50), T1."invoice_number")), 1, 1)) = '2') THEN ('15') ELSE ('1') END as c57, (substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + '_' + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) as c58, T1."vehicle_number" as c59, T1."is_canceled" as c60, T1."service_date" as c61, T1."invoice_date" as c62, T1."subsidiary" as c63, T1."invoice_number" as c64, T1."invoice_type" as c65 from "ims"."ben_AW_Order_Number" T9, (((((((("dbo"."invoices" T1 left outer join "dbo"."customers_suppliers" T3 on T3."customer_number" = T1."paying_customer") left outer join "dbo"."vehicles" T10 on T1."vehicle_number" = T10."internal_number") left outer join "dbo"."makes" T4 on T10."make_number" = T4."make_number") left outer join "dbo"."models" T5 on (T10."make_number" = T5."make_number") and (T10."model_code" = T5."model_code")) left outer join "dbo"."labours" T7 on (T7."invoice_number" = T1."invoice_number") and (T7."invoice_type" = T1."invoice_type")) left outer join "dbo"."charge_type_descriptions" T6 on T6."type" = T7."charge_type") left outer join "dbo"."employees" T8 on T1."creating_employee" = T8."employee_number") left outer join "ims"."Serviceberater_Rechnung" T2 on T1."invtype_invnr" = T2."invtype_invnr") where (T7."order_number" = T9."order_number") and ((((T1."invoice_type" BETWEEN 2 AND 6) and (T1."is_canceled" <> 1)) and (T1."invoice_date" >= convert(date, '2017-01-01'))) and ((((convert(float, T7."time_units"))) <> 0) and (((convert(float, T7."time_units"))) IS NOT NULL))) ) D1 -- order by "Invoice_Type_Invoice_Number" asc,"Invoice Number" asc