select T1."invoice_type" as "Invoice Type", T1."invoice_number" as "Invoice Number", T1."subsidiary" as "Subsidiary", T1."invoice_date" as "Invoice Date", T1."service_date" as "Service Date", T1."is_canceled" as "Is Canceled", T1."vehicle_number" as "Vehicle Number", (substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + '_' + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) as "Invoice_Type_Invoice_Number", CASE WHEN ((substring((convert(varchar(50), T1."invoice_number")), 1, 1)) = '2') THEN ('15') ELSE ('1') END as "Hauptbetrieb", '0' + (convert(varchar(50), T1."subsidiary")) as "Standort", T2."Serviceberater" as "Serviceberater", CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T3."customer_number" = 100001)) THEN ('intern') ELSE ('extern') END as "Umsatzart", T4."description" as "Fabrikat", T4."description" as "Description_Makes", T5."description" as "Description_Models", (substring((upper(T5."description")), 1, 3)) as "Model", (substring((substring((convert(varchar(50), T1."vehicle_number")), 1, 5)) + ' - ' + T5."description", 1, 100)) as "Fahrzeug", 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 "Kostenstelle", CASE WHEN (T4."description" = 'Ford') THEN ('1') WHEN (T4."description" = 'Nissan') THEN ('3') WHEN (T4."description" = 'Suzuki') THEN ('2') ELSE ('9') END as "Marke", 'Service' as "Auftragsart", T3."zip_code" as "Geschäftsart", 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 "Kunde", T7."time_units" as "Time Units", (convert(float, T7."time_units")) as "Time Units_Zahl", 10 as "AW/Std.", ((convert(float, T7."time_units"))) / 10 as "verk. Std.", 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 "Order Number", 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 "Order Number_Rg_Ausg", (substring((convert(varchar(50), T8."employee_number")), 1, 4)) + ' - ' + T8."name" as "Kundenart" from (((((((("dbo"."invoices" T1 left outer join "dbo"."customers_suppliers" T3 on T3."customer_number" = T1."paying_customer") left outer join "dbo"."vehicles" T9 on T1."vehicle_number" = T9."internal_number") left outer join "dbo"."makes" T4 on T9."make_number" = T4."make_number") left outer join "dbo"."models" T5 on (T9."make_number" = T5."make_number") and (T9."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 (((((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))) and (T7."mechanic_no" IS NOT NULL)) -- order by "Invoice_Type_Invoice_Number" asc,"Invoice Number" asc