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

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",
	(left((((T1."invoice_type"))), 1)) + '_' + (left((((T1."invoice_number"))), 9)) AS "Invoice_Type_Invoice_Number",
	'1' AS "Hauptbetrieb",
	(((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",
	(left((ucase(T5."description")), 3)) AS "Model",
	(left((left((((T1."vehicle_number"))), 5)) + ' - ' + T5."description", 100)) AS "Fahrzeug",
	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",
	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 ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
		ELSE ((left((((T3."customer_number"))), 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 (
					(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",
	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",
	(left((((T8."employee_number"))), 4)) + ' - ' + T8."name" AS "Kundenart"
FROM (
	(
		(
			(
				(
					(
						(
							(
								"dbo"."invoices" T1 LEFT JOIN "dbo"."customers_suppliers" T3 ON T3."customer_number" = T1."paying_customer"
								) LEFT JOIN "dbo"."vehicles" T9 ON T1."vehicle_number" = T9."internal_number"
							) LEFT JOIN "dbo"."makes" T4 ON T9."make_number" = T4."make_number"
						) LEFT JOIN "dbo"."models" T5 ON (T9."make_number" = T5."make_number")
						AND (T9."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 (
		(
			(
				(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)
			)
		)
	-- order by "Invoice_Type_Invoice_Number" asc,"Invoice Number" asc