C11/System/ARI/IQD/Service/Service_offene_Auftraege_neu_mitMont_Anzeige.sql

SELECT "Document Type_2",
	"No_2",
	"Sell-to Customer No",
	"Bill-to Customer No",
	"Bill-to Name",
	"Order Date_2",
	"Posting Date_2",
	"Posting Description",
	"Due Date",
	"Location Code",
	"Department Code_2",
	"Make Code",
	"Salesperson Code",
	"On Hold",
	"Transaction Type",
	"Transport Method",
	"Correction",
	"Document Date",
	"Area",
	"Reserve",
	"Option Code",
	"Service Posting Group",
	"Vin",
	"Model Code",
	"Model No",
	"Model",
	"Type_2",
	"Initial Registration",
	"Order Limit",
	"Time Of Order",
	"Pickup Date",
	"Pickup Time",
	"Completion Date",
	"Completion Time",
	"Labor Type",
	"Labor Charging Code",
	"Book No",
	"Branch Book No",
	"Sales Department Code",
	"Fixed Date",
	"Fixed Instruction",
	"Current Instruction Type",
	"Work Completed",
	"Status Code",
	"Service Advisor No",
	'1' AS "Hauptbetrieb",
	"Standort_Department",
	"Standort",
	"No_Employee",
	"First Name_Employee",
	"Last Name_Employee",
	"Serviceberater",
	"Umsatzart",
	"Kunde",
	"Order Number_mit Monteur",
	"Entry No",
	"Order No",
	"Posting Date",
	"Document No",
	"Type",
	"No",
	"Description",
	"Quantity",
	"Direct Unit Cost",
	"Unit Cost",
	"Total Cost",
	"Unit Price",
	"Total Price",
	"Department Code",
	"Work Type Code",
	"User Id",
	"Amt To Post To G L",
	"Amt Posted To G L",
	"Amt To Recognize",
	"Amt Recognized",
	"Entry Type",
	"Positive",
	"Qty Evaluated",
	"Corrected",
	"Variant Code",
	"Qty Per Unit Of Measure",
	"Quantity (base)" AS "Quantity (base)",
	"Time Type",
	"Service Job No",
	"Assoc Entry",
	"Closed",
	"Qty Per Hour",
	"Qty (hour)" AS "Qty (hour)",
	"Item Group Code",
	"Document Type",
	"Customer Group Code",
	"Charging Group No",
	"Standard Time",
	"Standard Time Type",
	"Source Type",
	"Main Customer",
	"Order Line No",
	"Time From",
	"Time Until",
	"Internal Charged",
	"Order Completed",
	"Open",
	"Closed By Entry No",
	"Closed At Date",
	"Closed By Quantity",
	"Open Quantity",
	"Labor Standard Time Type",
	"Menge",
	"Einstandsbetrag",
	"Verkaufsbetrag",
	"Umsatz Lohn",
	"Umsatz Teile Service",
	"Umsatz Sonstiges",
	"Einsatz Teile Service",
	"Einsatz Sonstiges",
	"Menge pro Stunde",
	"verk.Stunden",
	"verk. AW",
	"benutzte Zeit",
	"benutzte AW",
	"Name_Employee",
	"Function Code",
	"Name_Employee" AS "Monteur",
	"Function Code" AS "Funktion Monteur",
	"ben. AW ohne AZUBI",
	"Order Date_2" AS "Auftragsdatum",
	1 AS "DG_1",
	COUNT("No_2") OVER (PARTITION BY "Order Number_mit Monteur") AS "DG_2",
	1 / (COUNT("No_2") OVER (PARTITION BY "Order Number_mit Monteur")) AS "Durchg�nge",
	"Anzahl_Tage",
	("Anzahl_Tage") / (COUNT("No_2") OVER (PARTITION BY "Order Number_mit Monteur")) AS "Tage offen",
	"gebuchte Teile",
	"Monteur_Anzeige",
	"Serviceberater" AS "Serviceberater_Monteur",
	"Order Number_2",
	"Order Date_2" AS "Invoice Date",
	0 AS "Minimum Letzte Stempelung",
	"Tage letzte Stemp. bereinigt",
	("Tage letzte Stemp. bereinigt") / (COUNT("No_2") OVER (PARTITION BY "Order Number_mit Monteur")) AS "Anz. Tage letzte Stemp.",
	"Order Number_intern",
	"Anzahl Tage �ber Abholtermin",
	"Intern/Extern",
	("Anzahl Tage �ber Abholtermin") / (COUNT("No_2") OVER (PARTITION BY "Order Number_mit Monteur")) AS "Tage �ber Abholtermin",
	"Kundenname_verkauft_an",
	"Bill-to Contact",
	"Sell-to Customer Name",
	"Order Number_intern_neu",
	"Order No" AS "Order Number",
	"Anzahl_Tage" AS "Anzahl Tage_ori",
	(1 / (COUNT("No_2") OVER (PARTITION BY "Order Number_mit Monteur"))) AS "Durchg�nge (Auftrag)",
	"Anzahl_Tage" AS "Anzahl Tage",
	"Order Date_2" AS "Order Date",
	'' AS "Kostenstelle",
	"Vin" AS "Fahrzeug",
	"Umsatz Teile Service" AS "Teile",
	"verk. AW" AS "Arbeitswerte"
FROM (
	SELECT (
			CASE 
				WHEN ((((T2."First Name" + ' ' + T2."Last Name"))) IS NOT NULL)
					THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (((T2."First Name" + ' ' + T2."Last Name"))) + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))))
				WHEN ((((T2."First Name" + ' ' + T2."Last Name"))) IS NULL)
					THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))))
				ELSE NULL
				END
			) AS "Order Number_mit Monteur",
		(
			(
				CASE 
					WHEN (T3."Type" = 4)
						THEN ((convert(FLOAT, T3."Qty_ (Hour)")))
					ELSE (0)
					END
				) * ((convert(FLOAT, T3."Qty_ per Hour")))
			) AS "verk. AW",
		(
			CASE 
				WHEN (T3."Type" = 1)
					THEN (((convert(FLOAT, T3."Total Price"))))
				ELSE (0)
				END
			) AS "Umsatz Teile Service",
		T1."VIN" AS "Vin",
		T1."Order Date" AS "Order Date_2",
		((- 1 * datediff(day, (getdate()), T1."Order Date"))) AS "Anzahl_Tage",
		T3."Order No_" AS "Order No",
		CASE 
			WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NOT NULL)
				THEN (T1."No_" + ' - ' + T1."VIN" + ' / ' + T1."Model" + ' - ' + T1."Sell-to Customer Name" + ' / ' + T1."Bill-to Customer No_" + ' - ' + (T2."First Name" + ' ' + T2."Last Name") + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))) + ' / ' + 'Abh. Termin:' + ' ' + (convert(VARCHAR(50), year(T1."Pickup Date")) + '-' + convert(VARCHAR(50), month(T1."Pickup Date")) + '-' + convert(VARCHAR(50), day(T1."Pickup Date"))))
			WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NULL)
				THEN (T1."No_" + ' - ' + T1."VIN" + ' / ' + T1."Model" + ' - ' + T1."Sell-to Customer Name" + ' / ' + T1."Bill-to Customer No_" + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))) + ' / ' + 'Abh. Termin:' + ' ' + (convert(VARCHAR(50), year(T1."Pickup Date")) + '-' + convert(VARCHAR(50), month(T1."Pickup Date")) + '-' + convert(VARCHAR(50), day(T1."Pickup Date"))))
			ELSE NULL
			END AS "Order Number_intern_neu",
		T1."Sell-to Customer Name" AS "Sell-to Customer Name",
		T1."Bill-to Contact" AS "Bill-to Contact",
		T6."Name" AS "Kundenname_verkauft_an",
		(- 1 * datediff(day, (getdate()), T1."Pickup Date")) AS "Anzahl Tage �ber Abholtermin",
		CASE 
			WHEN (T1."Sell-to Customer No_" IN ('INTERN1', 'INTERN2'))
				THEN ('Intern')
			ELSE ('Extern')
			END AS "Intern/Extern",
		CASE 
			WHEN (T1."Sell-to Customer No_" IN ('INTERN1', 'INTERN2'))
				THEN (
						(
							CASE 
								WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NOT NULL)
									THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (T2."First Name" + ' ' + T2."Last Name") + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))))
								WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NULL)
									THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))))
								ELSE NULL
								END
							) + ' / ' + T1."VIN" + ' / ' + T1."Model" + ' - ' + 'Abh.Termin:' + ' ' + (convert(VARCHAR(50), year(T1."Pickup Date")) + '-' + convert(VARCHAR(50), month(T1."Pickup Date")) + '-' + convert(VARCHAR(50), day(T1."Pickup Date"))) + ' - ' + T1."Sell-to Customer Name"
						)
			ELSE NULL
			END AS "Order Number_intern",
		CASE 
			WHEN (0 IS NULL)
				THEN (((- 1 * datediff(day, (getdate()), T1."Order Date"))))
			ELSE (0)
			END AS "Tage letzte Stemp. bereinigt",
		CASE 
			WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NOT NULL)
				THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (T2."First Name" + ' ' + T2."Last Name") + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))))
			WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NULL)
				THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (convert(VARCHAR(50), year(T1."Order Date")) + '-' + convert(VARCHAR(50), month(T1."Order Date")) + '-' + convert(VARCHAR(50), day(T1."Order Date"))))
			ELSE NULL
			END AS "Order Number_2",
		((T2."First Name" + ' ' + T2."Last Name")) AS "Serviceberater",
		T5."Last Name" AS "Monteur_Anzeige",
		CASE 
			WHEN (
					(T3."Type" = 1)
					AND (T3."Document No_" IS NOT NULL)
					)
				THEN (((convert(FLOAT, T3."Total Price"))))
			ELSE (0)
			END AS "gebuchte Teile",
		CASE 
			WHEN (NOT T4."Function Code" IN ('AZUBI', 'AZUBIG'))
				THEN (
						(
							(
								CASE 
									WHEN (T3."Type" = 2)
										THEN ((convert(FLOAT, T3."Qty_ Evaluated")))
									ELSE (0)
									END
								) * 12
							)
						)
			ELSE (0)
			END AS "ben. AW ohne AZUBI",
		T4."Function Code" AS "Function Code",
		T4."Name" AS "Name_Employee",
		(
			CASE 
				WHEN (T3."Type" = 2)
					THEN ((convert(FLOAT, T3."Qty_ Evaluated")))
				ELSE (0)
				END
			) * 12 AS "benutzte AW",
		CASE 
			WHEN (T3."Type" = 2)
				THEN ((convert(FLOAT, T3."Qty_ Evaluated")))
			ELSE (0)
			END AS "benutzte Zeit",
		CASE 
			WHEN (T3."Type" = 4)
				THEN ((convert(FLOAT, T3."Qty_ (Hour)")))
			ELSE (0)
			END AS "verk.Stunden",
		(convert(FLOAT, T3."Qty_ per Hour")) AS "Menge pro Stunde",
		CASE 
			WHEN (T3."Type" IN (0, 3))
				THEN (((convert(FLOAT, T3."Total Cost"))))
			ELSE (0)
			END AS "Einsatz Sonstiges",
		CASE 
			WHEN (T3."Type" = 1)
				THEN (((convert(FLOAT, T3."Total Cost"))))
			ELSE (0)
			END AS "Einsatz Teile Service",
		CASE 
			WHEN (T3."Type" IN (0, 3))
				THEN (((convert(FLOAT, T3."Total Price"))))
			ELSE (0)
			END AS "Umsatz Sonstiges",
		CASE 
			WHEN (T3."Type" = 4)
				THEN (((convert(FLOAT, T3."Total Price"))))
			ELSE (0)
			END AS "Umsatz Lohn",
		(convert(FLOAT, T3."Total Price")) AS "Verkaufsbetrag",
		(convert(FLOAT, T3."Total Cost")) AS "Einstandsbetrag",
		(convert(FLOAT, T3."Quantity")) AS "Menge",
		T3."Labor Standard Time Type" AS "Labor Standard Time Type",
		T3."Open Quantity" AS "Open Quantity",
		T3."Closed by Quantity" AS "Closed By Quantity",
		T3."Closed at Date" AS "Closed At Date",
		T3."Closed by Entry No_" AS "Closed By Entry No",
		T3."Open" AS "Open",
		T3."Order Completed" AS "Order Completed",
		T3."Internal Charged" AS "Internal Charged",
		T3."Time Until" AS "Time Until",
		T3."Time From" AS "Time From",
		T3."Order Line No_" AS "Order Line No",
		T3."Main Customer" AS "Main Customer",
		T3."Source Type" AS "Source Type",
		T3."Standard Time Type" AS "Standard Time Type",
		T3."Standard Time" AS "Standard Time",
		T3."Charging Group No_" AS "Charging Group No",
		T3."Customer Group Code" AS "Customer Group Code",
		T3."Document Type" AS "Document Type",
		T3."Item Group Code" AS "Item Group Code",
		T3."Qty_ (Hour)" AS "Qty (hour)",
		T3."Qty_ per Hour" AS "Qty Per Hour",
		T3."Closed" AS "Closed",
		T3."Assoc_ Entry" AS "Assoc Entry",
		T3."Service Job No_" AS "Service Job No",
		T3."Time Type" AS "Time Type",
		T3."Quantity (Base)" AS "Quantity (base)",
		T3."Qty_ per Unit of Measure" AS "Qty Per Unit Of Measure",
		T3."Variant Code" AS "Variant Code",
		T3."Corrected" AS "Corrected",
		T3."Qty_ Evaluated" AS "Qty Evaluated",
		T3."Positive" AS "Positive",
		T3."Entry Type" AS "Entry Type",
		T3."Amt_ Recognized" AS "Amt Recognized",
		T3."Amt_ to Recognize" AS "Amt To Recognize",
		T3."Amt_ Posted to G_L" AS "Amt Posted To G L",
		T3."Amt_ to Post to G_L" AS "Amt To Post To G L",
		T3."User ID" AS "User Id",
		T3."Work Type Code" AS "Work Type Code",
		T3."Department Code" AS "Department Code",
		T3."Total Price" AS "Total Price",
		T3."Unit Price" AS "Unit Price",
		T3."Total Cost" AS "Total Cost",
		T3."Unit Cost" AS "Unit Cost",
		T3."Direct Unit Cost" AS "Direct Unit Cost",
		T3."Quantity" AS "Quantity",
		T3."Description" AS "Description",
		T3."No_" AS "No",
		T3."Type" AS "Type",
		T3."Document No_" AS "Document No",
		T3."Posting Date" AS "Posting Date",
		T3."Entry No_" AS "Entry No",
		T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name" AS "Kunde",
		CASE 
			WHEN (T1."Bill-to Customer No_" LIKE 'INT%')
				THEN ('Intern')
			ELSE ('Extern')
			END AS "Umsatzart",
		T2."Last Name" AS "Last Name_Employee",
		T2."First Name" AS "First Name_Employee",
		T2."No_" AS "No_Employee",
		CASE 
			WHEN (T1."Location Code" IN ('01BSPKW'))
				THEN ('10')
			WHEN (T1."Location Code" IN ('02BSMOT'))
				THEN ('20')
			WHEN (T1."Location Code" IN ('03RHF'))
				THEN ('30')
			WHEN (T1."Location Code" IN ('04SFH'))
				THEN ('40')
			WHEN (T1."Location Code" IN ('05WT'))
				THEN ('50')
			WHEN (T1."Location Code" IN ('06BI'))
				THEN ('60')
			WHEN (T1."Location Code" IN ('07TR'))
				THEN ('70')
			ELSE ((left(T1."Department Code", 2)))
			END AS "Standort",
		(left(T1."Department Code", 2)) AS "Standort_Department",
		T1."Service Advisor No_" AS "Service Advisor No",
		T1."Status Code" AS "Status Code",
		T1."Work Completed" AS "Work Completed",
		T1."Current Instruction Type" AS "Current Instruction Type",
		T1."Fixed Instruction" AS "Fixed Instruction",
		T1."Fixed Date" AS "Fixed Date",
		T1."Sales Department Code" AS "Sales Department Code",
		T1."Branch Book No_" AS "Branch Book No",
		T1."Book No_" AS "Book No",
		T1."Labor Charging Code" AS "Labor Charging Code",
		T1."Labor Type" AS "Labor Type",
		T1."Completion Time" AS "Completion Time",
		T1."Completion Date" AS "Completion Date",
		T1."Pickup Time" AS "Pickup Time",
		T1."Pickup Date" AS "Pickup Date",
		T1."Time of Order" AS "Time Of Order",
		T1."Order Limit" AS "Order Limit",
		T1."Initial Registration" AS "Initial Registration",
		T1."Type" AS "Type_2",
		T1."Model" AS "Model",
		T1."Model No_" AS "Model No",
		T1."Model Code" AS "Model Code",
		T1."Service Posting Group" AS "Service Posting Group",
		T1."Option Code" AS "Option Code",
		T1."Reserve" AS "Reserve",
		T1."Area" AS "Area",
		T1."Document Date" AS "Document Date",
		T1."Correction" AS "Correction",
		T1."Transport Method" AS "Transport Method",
		T1."Transaction Type" AS "Transaction Type",
		T1."On Hold" AS "On Hold",
		T1."Salesperson Code" AS "Salesperson Code",
		T1."Make Code" AS "Make Code",
		T1."Department Code" AS "Department Code_2",
		T1."Location Code" AS "Location Code",
		T1."Due Date" AS "Due Date",
		T1."Posting Description" AS "Posting Description",
		T1."Posting Date" AS "Posting Date_2",
		T1."Bill-to Name" AS "Bill-to Name",
		T1."Bill-to Customer No_" AS "Bill-to Customer No",
		T1."Sell-to Customer No_" AS "Sell-to Customer No",
		T1."No_" AS "No_2",
		T1."Document Type" AS "Document Type_2"
	FROM (
		(
			(
				(
					(
						"ARI"."import"."Service Header" T1 LEFT JOIN "ARI"."import"."Employee" T2 ON T2."No_" = T1."Service Advisor No_"
						) LEFT JOIN "ARI"."import"."Service Ledger Entry" T3 ON T1."No_" = T3."Order No_"
					) LEFT JOIN "ARI"."import"."Employee" T4 ON T4."No_" = T3."No_"
				) LEFT JOIN "ims"."Monteure_fuer_Anzeige_offene_Auftraege" T5 ON T5."Order No_" = T1."No_"
			) LEFT JOIN "ARI"."import"."Customer" T6 ON T1."Sell-to Customer No_" = T6."No_"
		)
	WHERE (
			(T1."No_" LIKE 'W%')
			AND (T1."Document Type" = 1)
			)
	) D1
	-- order by "No_2" asc