SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_imr].[offene_auftraege_dg_anz_tage]
AS
SELECT c51 AS "Order Number_ori",
	c91 AS "Order Position",
	c90 AS "Labour Type",
	c89 AS "Subsidiary",
	c88 AS "Is Invoiced",
	c87 AS "Invoice Type",
	c86 AS "Invoice Number",
	c85 AS "Order Date",
	c84 AS "Estimated Inbound Time",
	c83 AS "Estimated Outbound Time",
	c82 AS "Order Print Date",
	c81 AS "Order Taking Employee No",
	c80 AS "Order Delivery Employee No",
	c79 AS "Vehicle Number",
	c78 AS "Order Mileage",
	c77 AS "Order Customer",
	c76 AS "Paying Customer",
	c75 AS "Holder Number",
	c74 AS "Parts Rebate Group Sell",
	c73 AS "Clearing Delay Type",
	c72 AS "Code_Labour_Type",
	c71 AS "Description_Labour_Type",
	c70 AS "Hauptbetrieb",
	c69 AS "Standort",
	c68 AS "Name_Serviceberater",
	c67 AS "Serviceberater",
	c66 AS "Umsatzart",
	c65 AS "Customer Number_Cust",
	c64 AS "First Name_Cust",
	c63 AS "Family Name_Cust",
	c62 AS "Kunde",
	c61 AS "Order Number",
	c60 AS "Internal Number_Vehicle",
	c59 AS "Vin_Vehicle",
	c58 AS "License Plate_Vehicle",
	c57 AS "Make Number_Vehicle",
	'Service' AS "Auftragsart",
	c56 AS "Make Number_Makes",
	c55 AS "Description_Makes",
	c55 AS "Fabrikat",
	c54 AS "Description_Models",
	c53 AS "Model",
	1 AS "DG_1",
	COUNT(c89) OVER (PARTITION BY c51) AS "DG_2",
	1.0 / (COUNT(c89) OVER (PARTITION BY c51)) AS "DG",
	c52 AS "Anzahl Tage_1",
	(c52) / (COUNT(c89) OVER (PARTITION BY c51)) AS "Anzahl Tage"
FROM (
	SELECT T1."order_number" AS c51,
		1.0 * datediff(day,T1."order_date",getdate()) AS c52,
		(substring((upper(T8."description")), 1, 3)) AS c53,
		T8."description" AS c54,
		T7."description" AS c55,
		T7."make_number" AS c56,
		T6."make_number" AS c57,
		T6."license_plate" AS c58,
		T6."vin" AS c59,
		T6."internal_number" AS c60,
		CASE 
			WHEN (T6."license_plate" IS NOT NULL)
				THEN (
						(substring((convert(VARCHAR(50), T1."order_number")), 1, 6)) + ' - ' + (
							CASE 
								WHEN (T1."order_taking_employee_no" = 0)
									THEN ('SB fehlt')
								ELSE ((substring((convert(VARCHAR(50), T1."order_taking_employee_no")), 1, 4)) + ' - ' + T4."name")
								END
							) + ' - ' + T5."family_name" + ' - ' + (convert(VARCHAR(50), year(T1."order_date")) + '-' + convert(VARCHAR(50), month(T1."order_date")) + '-' + convert(VARCHAR(50), day(T1."order_date"))) + ' - ' + T6."license_plate"
						)
			ELSE (
					(substring((convert(VARCHAR(50), T1."order_number")), 1, 6)) + ' - ' + (
						CASE 
							WHEN (T1."order_taking_employee_no" = 0)
								THEN ('SB fehlt')
							ELSE ((substring((convert(VARCHAR(50), T1."order_taking_employee_no")), 1, 4)) + ' - ' + T4."name")
							END
						) + ' - ' + T5."family_name" + ' - ' + (convert(VARCHAR(50), year(T1."order_date")) + '-' + convert(VARCHAR(50), month(T1."order_date")) + '-' + convert(VARCHAR(50), day(T1."order_date")))
					)
			END AS c61,
		(substring((convert(VARCHAR(50), T5."customer_number")), 1, 7)) + ' - ' + T5."first_name" + ' ' + T5."family_name" AS c62,
		T5."family_name" AS c63,
		T5."first_name" AS c64,
		T5."customer_number" AS c65,
		T3."code" + ' - ' + T3."description" AS c66,
		CASE 
			WHEN (T1."order_taking_employee_no" = 0)
				THEN ('SB fehlt')
			ELSE ((substring((convert(VARCHAR(50), T1."order_taking_employee_no")), 1, 4)) + ' - ' + T4."name")
			END AS c67,
		T4."name" AS c68,
		'' + (convert(VARCHAR(50), T1."subsidiary")) AS c69,
		CASE 
			WHEN (('0' + (convert(VARCHAR(50), T1."subsidiary"))) IN ('015                                                                                                                                                                                                                                                            '))
				THEN ('15')
			ELSE ('1')
			END AS c70,
		T3."description" AS c71,
		T3."code" AS c72,
		T1."clearing_delay_type" AS c73,
		T1."parts_rebate_group_sell" AS c74,
		T1."holder_number" AS c75,
		T1."paying_customer" AS c76,
		T1."order_customer" AS c77,
		T1."order_mileage" AS c78,
		T1."vehicle_number" AS c79,
		T1."order_delivery_employee_no" AS c80,
		T1."order_taking_employee_no" AS c81,
		T1."order_print_date" AS c82,
		T1."estimated_outbound_time" AS c83,
		T1."estimated_inbound_time" AS c84,
		T1."order_date" AS c85,
		T2."invoice_number" AS c86,
		T2."invoice_type" AS c87,
		T2."is_invoiced" AS c88,
		T1."subsidiary" AS c89,
		T2."labour_type" AS c90,
		T1."order_position" AS c91
	FROM (
		(
			(
				(
					(
						(
							(
								LOCOSOFT."dbo"."order_positions" T1 LEFT OUTER JOIN LOCOSOFT."dbo"."labours" T2 ON T1."order_number" = T2."order_number"
								) LEFT OUTER JOIN LOCOSOFT."dbo"."labour_types" T3 ON T3."code" = T2."labour_type"
							) LEFT OUTER JOIN LOCOSOFT."dbo"."employees" T4 ON T1."order_taking_employee_no" = T4."employee_number"
						) LEFT OUTER JOIN LOCOSOFT."dbo"."customers_suppliers" T5 ON T1."order_customer" = T5."customer_number"
					) LEFT OUTER JOIN LOCOSOFT."dbo"."vehicles" T6 ON T1."vehicle_number" = T6."internal_number"
				) LEFT OUTER JOIN LOCOSOFT."dbo"."makes" T7 ON T6."make_number" = T7."make_number"
			) LEFT OUTER JOIN LOCOSOFT."dbo"."models" T8 ON (T6."make_number" = T8."make_number")
			AND (T6."model_code" = T8."model_code")
		)
	WHERE (
			(
				(T2."invoice_number" IS NULL)
				AND (T1."order_date" >= convert(DATETIME, '2020-01-01 00:00:00.000'))
				)
			AND (T2."labour_type" <> 'F')
			)
		-- order by c51 asc
	) D1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
