| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869 |
- select c64 as "Invoice Type",
- c63 as "Invoice Number",
- c62 as "Subsidiary",
- c61 as "Invoice Date",
- c60 as "Service Date",
- c59 as "Is Canceled",
- c58 as "Vehicle Number",
- c57 as "Invoice_Type_Invoice_Number",
- '1' 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 ((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number"))))) + ' - ' + T2."Serviceberater" + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."first_name" + ' ' + T3."family_name") END)) ELSE ((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number"))))) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + 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,
- (left(((convert(varchar(50), T8."employee_number"))))) + ' - ' + T8."name" as c41,
- CASE WHEN ((day((getdate()) - (convert(datetime, T1."invoice_date")))) <= 4) THEN ((CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number"))))) + ' - ' + T2."Serviceberater" + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."first_name" + ' ' + T3."family_name") END)) ELSE ((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number"))))) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + 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 ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + 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 ((left(((convert(varchar(50), T6."type"))))) 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 ((left(((convert(varchar(50), T6."type"))))) IN ('2')) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN (T6."type" IN (42,92)) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN ((left(((convert(varchar(50), T6."type"))))) 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,
- (left((left(((convert(varchar(50), T1."vehicle_number"))))) + ' - ' + T5."description",100)) as c50,
- (left((ucase(T5."description")),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,
- (left(((convert(varchar(50), T1."invoice_type"))))) + '_' + (left(((convert(varchar(50), T1."invoice_number"))))) as c57,
- T1."vehicle_number" as c58,
- T1."is_canceled" as c59,
- T1."service_date" as c60,
- T1."invoice_date" as c61,
- T1."subsidiary" as c62,
- T1."invoice_number" as c63,
- T1."invoice_type" as c64
- 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
|
