SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_ims].[offene_Auftraege_Ums_ben_AW_fuer_Monteure]
AS
SELECT DISTINCT c6 AS "Order Number_ori",
	c7 AS "Order Number",
	c5 AS "verk. Std. aus o.A."
FROM (
	SELECT T1."order_number" AS c4,
		SUM((convert(FLOAT, T5."time_units")) / 10) AS c5
	FROM (
		(
			(
				(
					LOCOSOFT."dbo"."order_positions" T1 LEFT OUTER JOIN LOCOSOFT."dbo"."vehicles" T2 ON T1."vehicle_number" = T2."internal_number"
					) LEFT OUTER JOIN LOCOSOFT."dbo"."employees" T3 ON T1."order_taking_employee_no" = T3."employee_number"
				) LEFT OUTER JOIN LOCOSOFT."dbo"."customers_suppliers" T4 ON T1."order_customer" = T4."customer_number"
			) LEFT OUTER JOIN LOCOSOFT."dbo"."labours" T5 ON T1."order_number" = T5."order_number"
		)
	WHERE (
			(
				(
					(T5."invoice_number" IS NULL)
					AND (T1."order_date" >= convert(DATETIME, '2017-01-01 00:00:00.000'))
					)
				AND (T5."net_price_in_order" IS NOT NULL)
				)
			AND (T5."labour_type" <> 'F')
			)
	GROUP BY T1."order_number"
	) D2,
	(
		SELECT DISTINCT T1."order_number" AS c6,
			CASE 
				WHEN (T2."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)) + ' - ' + T3."name")
								END + ' - ' + T4."family_name" + ' - ' + (convert(VARCHAR(50), year(T1."order_date")) + '-' + convert(VARCHAR(50), month(T1."order_date")) + '-' + convert(VARCHAR(50), day(T1."order_date"))) + ' - ' + T2."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)) + ' - ' + T3."name")
							END + ' - ' + T4."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 c7
		FROM (
			(
				(
					(
						LOCOSOFT."dbo"."order_positions" T1 LEFT OUTER JOIN LOCOSOFT."dbo"."vehicles" T2 ON T1."vehicle_number" = T2."internal_number"
						) LEFT OUTER JOIN LOCOSOFT."dbo"."employees" T3 ON T1."order_taking_employee_no" = T3."employee_number"
					) LEFT OUTER JOIN LOCOSOFT."dbo"."customers_suppliers" T4 ON T1."order_customer" = T4."customer_number"
				) LEFT OUTER JOIN LOCOSOFT."dbo"."labours" T5 ON T1."order_number" = T5."order_number"
			)
		WHERE (
				(
					(
						(T5."invoice_number" IS NULL)
						AND (T1."order_date" >= convert(DATETIME, '2017-01-01 00:00:00.000'))
						)
					AND (T5."net_price_in_order" IS NOT NULL)
					)
				AND (T5."labour_type" <> 'F')
				)
		) D1
WHERE (
		(c6 = c4)
		OR (
			(c6 IS NULL)
			AND (c4 IS NULL)
			)
		)
	-- order by "Order Number_ori" asc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
