C11/System/LOCOSOFT/IQD/Serv_Teile/Aftersales_Rechnungen_ben_AW_final.sql

SELECT "Invoice Type",
	"Invoice Number",
	"Subsidiary",
	"Invoice Date",
	"Service Date",
	"Is Canceled",
	"Vehicle Number",
	"Invoice_Type_Invoice_Number",
	'1' AS "Hauptbetrieb",
	"Standort",
	"Serviceberater",
	"Umsatzart",
	"Fabrikat",
	"Fabrikat" AS "Description_Makes",
	"Description_Models",
	"Model",
	"Fahrzeug",
	"Kostenstelle",
	"Marke",
	'Service' AS "Auftragsart",
	"Gesch�ftsart",
	"Kunde",
	"Time Units",
	"Time Units_Zahl",
	10 AS "AW/Std.",
	"verk. Std._",
	"Order Number",
	"Order Number_Rg_Ausg",
	"Kundenart",
	"Summe Duration Minutes",
	"Order Number_ben_AW",
	1 AS "Anzahl_Datens�tze_1",
	COUNT("Order Number_ben_AW") OVER (PARTITION BY "Order Number") AS "Anzahl_Datens�tze_2",
	(c38) / (COUNT("Order Number_ben_AW") OVER (PARTITION BY "Order Number")) AS "ben. Std."
FROM (
	SELECT (
			CASE 
				WHEN (T2."Serviceberater" IS NOT NULL)
					THEN (
							(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9)) + ' - ' + T2."Serviceberater" + ' - ' + (
								CASE 
									WHEN (T3."first_name" IS NULL)
										THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
									ELSE ((left((((T3."customer_number"))), 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name")
									END
								)
							)
				ELSE (
						(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9)) + ' - ' + (
							CASE 
								WHEN (T3."first_name" IS NULL)
									THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
								ELSE ((left((((T3."customer_number"))), 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name")
								END
							)
						)
				END
			) AS "Order Number",
		T9."Summe_Duration_Minutes" / 60 AS c38,
		T9."order_number" AS "Order Number_ben_AW",
		T9."Summe_Duration_Minutes" AS "Summe Duration Minutes",
		(left((((T8."employee_number"))), 4)) + ' - ' + T8."name" AS "Kundenart",
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."invoice_date")))) <= 4)
				THEN (
						(
							CASE 
								WHEN (T2."Serviceberater" IS NOT NULL)
									THEN (
											(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9)) + ' - ' + T2."Serviceberater" + ' - ' + (
												CASE 
													WHEN (T3."first_name" IS NULL)
														THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
													ELSE ((left((((T3."customer_number"))), 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name")
													END
												)
											)
								ELSE (
										(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9)) + ' - ' + (
											CASE 
												WHEN (T3."first_name" IS NULL)
													THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
												ELSE ((left((((T3."customer_number"))), 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name")
												END
											)
										)
								END
							)
						)
			ELSE NULL
			END AS "Order Number_Rg_Ausg",
		((convert(FLOAT, T7."time_units"))) / 10 AS "verk. Std._",
		(convert(FLOAT, T7."time_units")) AS "Time Units_Zahl",
		T7."time_units" AS "Time Units",
		CASE 
			WHEN (T3."first_name" IS NULL)
				THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
			ELSE ((left((((T3."customer_number"))), 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name")
			END AS "Kunde",
		T3."zip_code" AS "Gesch�ftsart",
		CASE 
			WHEN (T4."description" = 'Ford')
				THEN ('1')
			WHEN (T4."description" = 'Nissan')
				THEN ('3')
			WHEN (T4."description" = 'Suzuki')
				THEN ('2')
			ELSE ('9')
			END AS "Marke",
		CASE 
			WHEN ((left((((T6."type"))), 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 ((left((((T6."type"))), 1)) IN ('2'))
				THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
			WHEN (T6."type" IN (42, 92))
				THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
			WHEN ((left((((T6."type"))), 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",
		(left((left((((T1."vehicle_number"))), 5)) + ' - ' + T5."description", 100)) AS "Fahrzeug",
		(left((ucase(T5."description")), 3)) AS "Model",
		T5."description" AS "Description_Models",
		T4."description" AS "Fabrikat",
		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",
		T2."Serviceberater" AS "Serviceberater",
		(((T1."subsidiary"))) AS "Standort",
		(left((((T1."invoice_type"))), 1)) + '_' + (left((((T1."invoice_number"))), 9)) AS "Invoice_Type_Invoice_Number",
		T1."vehicle_number" AS "Vehicle Number",
		T1."is_canceled" AS "Is Canceled",
		T1."service_date" AS "Service Date",
		T1."invoice_date" AS "Invoice Date",
		T1."subsidiary" AS "Subsidiary",
		T1."invoice_number" AS "Invoice Number",
		T1."invoice_type" AS "Invoice Type"
	FROM "ims"."ben_AW_Order_Number" T9,
		(
			(
				(
					(
						(
							(
								(
									(
										"dbo"."invoices" T1 LEFT JOIN "dbo"."customers_suppliers" T3 ON T3."customer_number" = T1."paying_customer"
										) LEFT JOIN "dbo"."vehicles" T10 ON T1."vehicle_number" = T10."internal_number"
									) LEFT JOIN "dbo"."makes" T4 ON T10."make_number" = T4."make_number"
								) LEFT JOIN "dbo"."models" T5 ON (T10."make_number" = T5."make_number")
								AND (T10."model_code" = T5."model_code")
							) LEFT JOIN "dbo"."labours" T7 ON (T7."invoice_number" = T1."invoice_number")
							AND (T7."invoice_type" = T1."invoice_type")
						) LEFT JOIN "dbo"."charge_type_descriptions" T6 ON T6."type" = T7."charge_type"
					) LEFT JOIN "dbo"."employees" T8 ON T1."creating_employee" = T8."employee_number"
				) LEFT 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