C11/System/OPTIMA/IQD/NW/nw_gw_vk_csv.sql

SELECT "Unit Number" AS "Unit Number",
	"Basis Number" AS "Basis Number",
	"Ecc Status" AS "Ecc Status",
	"Vehicle Type" AS "Vehicle Type",
	"Vehicle Type_vpp5r" AS "Vehicle Type_vpp5r",
	"Vehicle Type Text_vpp5r" AS "Vehicle Type Text_vpp5r",
	"Car Creation Date" AS "Car Creation Date",
	"Depreciation" AS "Depreciation",
	"Book Keeping Code_2" AS "Book Keeping Code_2",
	"Book Keeping Code_vpp5m" AS "Book Keeping Code_vpp5m",
	"Specify_vpp5m" AS "Specify_vpp5m",
	"Mileage_2" AS "Mileage_2",
	"Purch Department" AS "Purch Department",
	"Purch Salsman Code" AS "Purch Salsman Code",
	"Purch Date" AS "Purch Date",
	"Reception Date" AS "Reception Date",
	"Sales Department" AS "Sales Department",
	"Department Type Id_Dep" AS "Department Type Id_Dep",
	"Description_Dep" AS "Description_Dep",
	"Sale Salesman" AS "Sale Salesman",
	"Seller Code_vpp43" AS "Seller Code_vpp43",
	"Sel Name_vpp43" AS "Sel Name_vpp43",
	"Sel Family Name_vpp43" AS "Sel Family Name_vpp43",
	"Sale Inv Number" AS "Sale Inv Number",
	"Sale Date" AS "Sale Date",
	"Bud Sale Det" AS "Bud Sale Det",
	"Bud Purch Price" AS "Bud Purch Price",
	"Bud Reg Fee" AS "Bud Reg Fee",
	"Bud Deduct Fee" AS "Bud Deduct Fee",
	"Bud Cost" AS "Bud Cost",
	"Purch Price Unit" AS "Purch Price Unit",
	"Cost Unit" AS "Cost Unit",
	"Sales Price Unit" AS "Sales Price Unit",
	"Reg Fee" AS "Reg Fee",
	"Stock Days" AS "Stock Days",
	"Buying Order No" AS "Buying Order No",
	"Buying Order Date" AS "Buying Order Date",
	"Pa Number" AS "Pa Number",
	"Location Code" AS "Location Code",
	"Owner" AS "Owner",
	"Prev Owner" AS "Prev Owner",
	"Account Purchase" AS "Account Purchase",
	"Account Sales" AS "Account Sales",
	"Customer Number_Cust" AS "Customer Number_Cust",
	"Name_Cust" AS "Name_Cust",
	"Customer Group_Cust" AS "Customer Group_Cust",
	"Customer Group_vpp48" AS "Customer Group_vpp48",
	"Cust Group Specify_vpp48" AS "Cust Group Specify_vpp48",
	"Order Arrival Date" AS "Order Arrival Date",
	"Order Confirm Date" AS "Order Confirm Date",
	"Contract Date" AS "Contract Date",
	"Register Number" AS "Register Number",
	"Chassis Number" AS "Chassis Number",
	"Owner Code" AS "Owner Code",
	"First Reg Date" AS "First Reg Date",
	"Latest Reg Date" AS "Latest Reg Date",
	"Arrival Date" AS "Arrival Date",
	"Salesman Buy" AS "Salesman Buy",
	"Salesman Sale" AS "Salesman Sale",
	"Model Text" AS "Model Text",
	"Colour Cf" AS "Colour Cf",
	"Model Line" AS "Model Line",
	"Model Line_vpp5q" AS "Model Line_vpp5q",
	"Mod Lin Specify_vpp5q" AS "Mod Lin Specify_vpp5q",
	"Workshop Model" AS "Workshop Model",
	"Mileage" AS "Mileage",
	"Motor Code" AS "Motor Code",
	"Drive Code" AS "Drive Code",
	"Colour Code" AS "Colour Code",
	"Make" AS "Make",
	"Make Cd" AS "Make Cd",
	"Global Make Cd" AS "Global Make Cd",
	"Description_Global_Make" AS "Description_Global_Make",
	"Orig Inv Date" AS "Orig Inv Date",
	"Latest Inv Date" AS "Latest Inv Date",
	"Invoice Date" AS "Invoice Date",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Standort_Verkaufskostenstelle" AS "Standort_Verkaufskostenstelle",
	"Verk�ufer" AS "Verk�ufer",
	"Fabrikat_ori" AS "Fabrikat_ori",
	"Model" AS "Model",
	"Modellbez" AS "Modellbez",
	"Fahrgestellnr" AS "Fahrgestellnr",
	"Fahrzeugart" AS "Fahrzeugart",
	"Fahrzeugtyp" AS "Fahrzeugtyp",
	"Kunde" AS "Kunde",
	"Kundenart" AS "Kundenart",
	"Gesch�ftsart" AS "Gesch�ftsart",
	"Farbe" AS "Farbe",
	"ECC_Status_Text" AS "ECC_Status_Text",
	"FZG" AS "FZG",
	"FZG_1" AS "FZG_1",
	"FZG_Liste_1" AS "FZG_Liste_1",
	"Sel Name_EK_vpp43" AS "Sel Name_EK_vpp43",
	"Eink�ufer" AS "Eink�ufer",
	"Vorbesitzer" AS "Vorbesitzer",
	"FZG_Liste_2" AS "FZG_Liste_2",
	"FZG_Liste_3" AS "FZG_Liste_3",
	"FZG_Liste_4" AS "FZG_Liste_4",
	"Unit Number_Unit_Hist" AS "Unit Number_Unit_Hist",
	"Line Type" AS "Line Type",
	"Line No" AS "Line No",
	"Transact Date" AS "Transact Date",
	"Handler" AS "Handler",
	"Program" AS "Program",
	"Function Code" AS "Function Code",
	"Document Date" AS "Document Date",
	"Document Nr" AS "Document Nr",
	"Order Date" AS "Order Date",
	"Order Number" AS "Order Number",
	"Transaction Code" AS "Transaction Code",
	"Transaction Code_vpp5a" AS "Transaction Code_vpp5a",
	"Specify_vpp5a" AS "Specify_vpp5a",
	"Reference" AS "Reference",
	"Costs" AS "Costs",
	"Orders Grossvalue" AS "Orders Grossvalue",
	"Discount" AS "Discount",
	"Classification Dte" AS "Classification Dte",
	"Book Keeping Code" AS "Book Keeping Code",
	"Department" AS "Department",
	"Destination" AS "Destination",
	"Int Voucher No" AS "Int Voucher No",
	"Erl�s" AS "Erl�s",
	"Sonst. Erl�se" AS "Sonst. Erl�se",
	"Ums. Fracht und �berf." AS "Ums. Fracht und �berf.",
	"Nachlass" AS "Nachlass",
	"Provisionen" AS "Provisionen",
	"EK Fahrzeug" AS "EK Fahrzeug",
	"FZG-Kosten" AS "FZG-Kosten",
	"Einsatz" AS "Einsatz",
	SUM("Einsatz") OVER (PARTITION BY "Unit Number") AS "Summe  (Einsatz) Nr.2",
	RSUM("Einsatz") AS "Summe  (Einsatz) Nr.1",
	"VK_Hilfen" AS "VK_Hilfen",
	"Standtage" AS "Standtage",
	"Fabrikat_manuell" AS "Fabrikat_manuell",
	"Mandant" AS "Mandant",
	"Department_unit_change_hist" AS "Department_unit_change_hist",
	"Unique Ident_unit_change_hist" AS "Unique Ident_unit_change_hist",
	"Maximum_Unique_Ident" AS "Maximum_Unique_Ident",
	"Standort" AS "Standort",
	"TR Code" AS "TR Code",
	"Zipcode" AS "Zipcode",
	"Hauptbetrieb_ID_ori" AS "Hauptbetrieb_ID_ori",
	"Hauptbetrieb_Name_ori" AS "Hauptbetrieb_Name_ori",
	"Standort_ID_ori" AS "Standort_ID_ori",
	"Standort_Name_ori" AS "Standort_Name_ori",
	"Fabrikat_GC_Marke_ori" AS "Fabrikat_GC_Marke_ori",
	"Hauptbetrieb_ID" AS "Hauptbetrieb_ID",
	"Hauptbetrieb_Name" AS "Hauptbetrieb_Name",
	"Standort_ID" AS "Standort_ID",
	"Standort_Name" AS "Standort_Name",
	"Fabrikat" AS "Fabrikat",
	"Order By_ori" AS "Order By_ori",
	"Modell_Beschreibung" AS "Modell_Beschreibung",
	"PLZ_1" AS "PLZ_1",
	"PLZ_2" AS "PLZ_2",
	"PLZ_3" AS "PLZ_3",
	"PLZ_4" AS "PLZ_4",
	"PLZ Code_Deb" AS "PLZ Code_Deb",
	"FZG_Erl�s" AS "FZG_Erl�s",
	"Einsatz_FZG" AS "Einsatz_FZG",
	"Erl�s_Prov" AS "Erl�s_Prov",
	"Erl�s_�berf." AS "Erl�s_�berf.",
	"Erl�s Sonst." AS "Erl�s Sonst.",
	"Order By" AS "Order By",
	"Eink�ufer/Vorbesitzer" AS "Eink�ufer/Vorbesitzer",
	"DB1 < 0" AS "DB1 < 0",
	"Name_Eink�ufer" AS "Name_Eink�ufer",
	"Konto" AS "Konto",
	"Buch_Text" AS "Buch_Text",
	"Name_Lieferant" AS "Name_Lieferant",
	"Erl�s Prov" AS "Erl�s Prov",
	"Erl�s_FZG" AS "Erl�s_FZG",
	"Erl�s �berf." AS "Erl�s �berf.",
	"Einsatz FZG" AS "Einsatz FZG",
	"Menge_1" AS "Menge_1",
	"Anzahl_Datens�tze" AS "Anzahl_Datens�tze",
	"Menge" AS "Menge",
	"DB1" AS "DB1",
	"Summe_DB1" AS "Summe_DB1",
	"FZG_Detail" AS "FZG_Detail"
FROM (
	SELECT "Unit Number" AS "Unit Number",
		"Basis Number" AS "Basis Number",
		"Ecc Status" AS "Ecc Status",
		"Vehicle Type" AS "Vehicle Type",
		"Vehicle Type_vpp5r" AS "Vehicle Type_vpp5r",
		"Vehicle Type Text_vpp5r" AS "Vehicle Type Text_vpp5r",
		"Car Creation Date" AS "Car Creation Date",
		"Depreciation" AS "Depreciation",
		"Book Keeping Code_2" AS "Book Keeping Code_2",
		"Book Keeping Code_vpp5m" AS "Book Keeping Code_vpp5m",
		"Specify_vpp5m" AS "Specify_vpp5m",
		"Mileage_2" AS "Mileage_2",
		"Purch Department" AS "Purch Department",
		"Purch Salsman Code" AS "Purch Salsman Code",
		"Purch Date" AS "Purch Date",
		"Reception Date" AS "Reception Date",
		"Sales Department" AS "Sales Department",
		"Department Type Id_Dep" AS "Department Type Id_Dep",
		"Description_Dep" AS "Description_Dep",
		"Sale Salesman" AS "Sale Salesman",
		"Seller Code_vpp43" AS "Seller Code_vpp43",
		"Sel Name_vpp43" AS "Sel Name_vpp43",
		"Sel Family Name_vpp43" AS "Sel Family Name_vpp43",
		"Sale Inv Number" AS "Sale Inv Number",
		"Sale Date" AS "Sale Date",
		"Bud Sale Det" AS "Bud Sale Det",
		"Bud Purch Price" AS "Bud Purch Price",
		"Bud Reg Fee" AS "Bud Reg Fee",
		"Bud Deduct Fee" AS "Bud Deduct Fee",
		"Bud Cost" AS "Bud Cost",
		"Purch Price Unit" AS "Purch Price Unit",
		"Cost Unit" AS "Cost Unit",
		"Sales Price Unit" AS "Sales Price Unit",
		"Reg Fee" AS "Reg Fee",
		"Stock Days" AS "Stock Days",
		"Buying Order No" AS "Buying Order No",
		"Buying Order Date" AS "Buying Order Date",
		"Pa Number" AS "Pa Number",
		"Location Code" AS "Location Code",
		"Owner" AS "Owner",
		"Prev Owner" AS "Prev Owner",
		"Account Purchase" AS "Account Purchase",
		"Account Sales" AS "Account Sales",
		"Customer Number_Cust" AS "Customer Number_Cust",
		"Name_Cust" AS "Name_Cust",
		"Customer Group_Cust" AS "Customer Group_Cust",
		"Customer Group_vpp48" AS "Customer Group_vpp48",
		"Cust Group Specify_vpp48" AS "Cust Group Specify_vpp48",
		"Order Arrival Date" AS "Order Arrival Date",
		"Order Confirm Date" AS "Order Confirm Date",
		"Contract Date" AS "Contract Date",
		"Register Number" AS "Register Number",
		"Chassis Number" AS "Chassis Number",
		"Owner Code" AS "Owner Code",
		"First Reg Date" AS "First Reg Date",
		"Latest Reg Date" AS "Latest Reg Date",
		"Arrival Date" AS "Arrival Date",
		"Salesman Buy" AS "Salesman Buy",
		"Salesman Sale" AS "Salesman Sale",
		"Model Text" AS "Model Text",
		"Colour Cf" AS "Colour Cf",
		"Model Line" AS "Model Line",
		"Model Line_vpp5q" AS "Model Line_vpp5q",
		"Mod Lin Specify_vpp5q" AS "Mod Lin Specify_vpp5q",
		"Workshop Model" AS "Workshop Model",
		"Mileage" AS "Mileage",
		"Motor Code" AS "Motor Code",
		"Drive Code" AS "Drive Code",
		"Colour Code" AS "Colour Code",
		"Make" AS "Make",
		"Make Cd" AS "Make Cd",
		"Global Make Cd" AS "Global Make Cd",
		"Description_Global_Make" AS "Description_Global_Make",
		"Orig Inv Date" AS "Orig Inv Date",
		"Latest Inv Date" AS "Latest Inv Date",
		"Invoice Date" AS "Invoice Date",
		"Hauptbetrieb" AS "Hauptbetrieb",
		"Standort_Verkaufskostenstelle" AS "Standort_Verkaufskostenstelle",
		"Verk�ufer" AS "Verk�ufer",
		"Fabrikat_ori" AS "Fabrikat_ori",
		"Model" AS "Model",
		"Modellbez" AS "Modellbez",
		"Fahrgestellnr" AS "Fahrgestellnr",
		"Fahrzeugart" AS "Fahrzeugart",
		"Fahrzeugtyp" AS "Fahrzeugtyp",
		"Kunde" AS "Kunde",
		"Kundenart" AS "Kundenart",
		"Gesch�ftsart" AS "Gesch�ftsart",
		"Farbe" AS "Farbe",
		"ECC_Status_Text" AS "ECC_Status_Text",
		"FZG" AS "FZG",
		"FZG_1" AS "FZG_1",
		"FZG_Liste_1" AS "FZG_Liste_1",
		"Sel Name_EK_vpp43" AS "Sel Name_EK_vpp43",
		"Eink�ufer" AS "Eink�ufer",
		"Vorbesitzer" AS "Vorbesitzer",
		"FZG_Liste_2" AS "FZG_Liste_2",
		"FZG_Liste_3" AS "FZG_Liste_3",
		"FZG_Liste_4" AS "FZG_Liste_4",
		"Unit Number_Unit_Hist" AS "Unit Number_Unit_Hist",
		"Line Type" AS "Line Type",
		"Line No" AS "Line No",
		"Transact Date" AS "Transact Date",
		"Handler" AS "Handler",
		"Program" AS "Program",
		"Function Code" AS "Function Code",
		"Document Date" AS "Document Date",
		"Document Nr" AS "Document Nr",
		"Order Date" AS "Order Date",
		"Order Number" AS "Order Number",
		"Transaction Code" AS "Transaction Code",
		"Transaction Code_vpp5a" AS "Transaction Code_vpp5a",
		"Specify_vpp5a" AS "Specify_vpp5a",
		"Reference" AS "Reference",
		"Costs" AS "Costs",
		"Orders Grossvalue" AS "Orders Grossvalue",
		"Discount" AS "Discount",
		"Classification Dte" AS "Classification Dte",
		"Book Keeping Code" AS "Book Keeping Code",
		"Department" AS "Department",
		"Destination" AS "Destination",
		"Int Voucher No" AS "Int Voucher No",
		"Erl�s" AS "Erl�s",
		"Sonst. Erl�se" AS "Sonst. Erl�se",
		"Ums. Fracht und �berf." AS "Ums. Fracht und �berf.",
		"Nachlass" AS "Nachlass",
		"Provisionen" AS "Provisionen",
		"EK Fahrzeug" AS "EK Fahrzeug",
		"FZG-Kosten" AS "FZG-Kosten",
		"Einsatz" AS "Einsatz",
		"VK_Hilfen" AS "VK_Hilfen",
		"Standtage" AS "Standtage",
		"Fabrikat_manuell" AS "Fabrikat_manuell",
		"Mandant" AS "Mandant",
		"Department_unit_change_hist" AS "Department_unit_change_hist",
		"Unique Ident_unit_change_hist" AS "Unique Ident_unit_change_hist",
		MAX("Unique Ident_unit_change_hist") OVER (PARTITION BY "Unit Number") AS "Maximum_Unique_Ident",
		"Standort" AS "Standort",
		"TR Code" AS "TR Code",
		"Zipcode" AS "Zipcode",
		"Hauptbetrieb_ID_ori" AS "Hauptbetrieb_ID_ori",
		"Hauptbetrieb_Name_ori" AS "Hauptbetrieb_Name_ori",
		"Standort_ID_ori" AS "Standort_ID_ori",
		"Standort_Name_ori" AS "Standort_Name_ori",
		"Fabrikat_GC_Marke_ori" AS "Fabrikat_GC_Marke_ori",
		"Hauptbetrieb_ID" AS "Hauptbetrieb_ID",
		"Hauptbetrieb_Name" AS "Hauptbetrieb_Name",
		"Standort_ID" AS "Standort_ID",
		"Standort_Name" AS "Standort_Name",
		"Fabrikat" AS "Fabrikat",
		"Order By_ori" AS "Order By_ori",
		"Modell_Beschreibung" AS "Modell_Beschreibung",
		"PLZ_1" AS "PLZ_1",
		"PLZ_2" AS "PLZ_2",
		"PLZ_3" AS "PLZ_3",
		"PLZ_4" AS "PLZ_4",
		"PLZ Code_Deb" AS "PLZ Code_Deb",
		"FZG_Erl�s" AS "FZG_Erl�s",
		"Einsatz_FZG" AS "Einsatz_FZG",
		"Erl�s_Prov" AS "Erl�s_Prov",
		"Erl�s_�berf." AS "Erl�s_�berf.",
		"Erl�s Sonst." AS "Erl�s Sonst.",
		"Order By" AS "Order By",
		"Eink�ufer/Vorbesitzer" AS "Eink�ufer/Vorbesitzer",
		CASE 
			WHEN ((SUM("DB1") OVER (PARTITION BY "Unit Number")) < 0)
				THEN ('DB1 < 0')
			ELSE ('DB1 > 0')
			END AS "DB1 < 0",
		"Name_Eink�ufer" AS "Name_Eink�ufer",
		"Konto" AS "Konto",
		"Buch_Text" AS "Buch_Text",
		"Name_Lieferant" AS "Name_Lieferant",
		"Erl�s Prov" AS "Erl�s Prov",
		"Erl�s_FZG" AS "Erl�s_FZG",
		"Erl�s �berf." AS "Erl�s �berf.",
		"Einsatz FZG" AS "Einsatz FZG",
		"Menge_1" AS "Menge_1",
		COUNT("Basis Number") OVER (PARTITION BY "Unit Number") AS "Anzahl_Datens�tze",
		("Menge_1" / COUNT("Basis Number") OVER (PARTITION BY "Unit Number")) AS "Menge",
		"DB1" AS "DB1",
		SUM("DB1") OVER (PARTITION BY "Unit Number") AS "Summe_DB1",
		"FZG_Detail" AS "FZG_Detail"
	FROM (
		SELECT "Unit Number",
			"Basis Number",
			"Ecc Status",
			"Vehicle Type",
			"Vehicle Type_vpp5r",
			"Vehicle Type Text_vpp5r",
			"Car Creation Date",
			"Depreciation",
			"Book Keeping Code_2",
			"Book Keeping Code_vpp5m",
			"Specify_vpp5m",
			"Mileage_2",
			"Purch Department",
			"Purch Salsman Code",
			"Purch Date",
			"Reception Date",
			"Sales Department",
			"Department Type Id_Dep",
			"Description_Dep",
			"Sale Salesman",
			"Seller Code_vpp43",
			"Sel Name_vpp43",
			"Sel Family Name_vpp43",
			"Sale Inv Number",
			"Sale Date",
			"Bud Sale Det",
			"Bud Purch Price",
			"Bud Reg Fee",
			"Bud Deduct Fee",
			"Bud Cost",
			"Purch Price Unit",
			"Cost Unit",
			"Sales Price Unit",
			"Reg Fee",
			"Stock Days",
			"Buying Order No",
			"Buying Order Date",
			"Pa Number",
			"Location Code",
			"Owner",
			"Prev Owner",
			"Account Purchase",
			"Account Sales",
			"Customer Number_Cust",
			"Name_Cust",
			"Customer Group_Cust",
			"Customer Group_vpp48",
			"Cust Group Specify_vpp48",
			"Order Arrival Date",
			"Order Confirm Date",
			"Contract Date",
			"Register Number",
			"Chassis Number",
			"Owner Code",
			"First Reg Date",
			"Latest Reg Date",
			"Arrival Date",
			"Salesman Buy",
			"Salesman Sale",
			"Model Text",
			"Colour Cf",
			"Model Line",
			"Model Line_vpp5q",
			"Mod Lin Specify_vpp5q",
			"Workshop Model",
			"Mileage",
			"Motor Code",
			"Drive Code",
			"Colour Code",
			"Make",
			"Make Cd",
			"Global Make Cd",
			"Description_Global_Make",
			"Orig Inv Date",
			"Latest Inv Date",
			"Sale Date" AS "Invoice Date",
			"Hauptbetrieb",
			"Standort_Verkaufskostenstelle",
			"Sel Name_vpp43" AS "Verk�ufer",
			"Description_Global_Make" AS "Fabrikat_ori",
			"Model",
			"Model Text" AS "Modellbez",
			"Chassis Number" AS "Fahrgestellnr",
			"Fahrzeugart",
			"Fahrzeugtyp",
			"Kunde",
			"Kundenart",
			'' AS "Gesch�ftsart",
			"Farbe",
			"ECC_Status_Text",
			"FZG",
			"FZG_1",
			"FZG_Liste_1",
			"Sel Name_EK_vpp43",
			"Sel Name_EK_vpp43" AS "Eink�ufer",
			"Prev Owner" AS "Vorbesitzer",
			"FZG_Liste_2",
			"Model Text" AS "FZG_Liste_3",
			"FZG_Liste_4",
			"Unit Number_Unit_Hist",
			"Line Type",
			"Line No",
			"Transact Date",
			"Handler",
			"Program",
			"Function Code",
			"Document Date",
			"Document Nr",
			"Order Date",
			"Order Number",
			"Transaction Code",
			"Transaction Code_vpp5a",
			"Specify_vpp5a",
			"Reference",
			"Costs",
			"Orders Grossvalue",
			"Discount",
			"Classification Dte",
			"Book Keeping Code",
			"Department",
			"Destination",
			"Int Voucher No",
			"Erl�s",
			"Sonst. Erl�se",
			"Ums. Fracht und �berf.",
			"Nachlass",
			"Provisionen",
			"EK Fahrzeug",
			"FZG-Kosten",
			"Einsatz",
			"VK_Hilfen",
			"Standtage",
			"Fabrikat_manuell",
			"Mandant",
			"Department_unit_change_hist",
			"Unique Ident_unit_change_hist",
			"Standort",
			"TR Code",
			"Zipcode",
			"Hauptbetrieb_ID_ori",
			"Hauptbetrieb_Name_ori",
			"Standort_ID_ori",
			"Standort_Name_ori",
			"Fabrikat_GC_Marke_ori",
			"Hauptbetrieb_ID_ori" AS "Hauptbetrieb_ID",
			"Hauptbetrieb_Name_ori" AS "Hauptbetrieb_Name",
			"Standort_ID_ori" AS "Standort_ID",
			"Standort_Name_ori" AS "Standort_Name",
			"Fabrikat",
			"Order By_ori",
			"Model Text" AS "Modell_Beschreibung",
			"PLZ_1",
			"PLZ_2",
			"PLZ_3",
			"PLZ_4",
			"Zipcode" AS "PLZ Code_Deb",
			"Erl�s" AS "FZG_Erl�s",
			"Einsatz" AS "Einsatz_FZG",
			"Provisionen" AS "Erl�s_Prov",
			"Ums. Fracht und �berf." AS "Erl�s_�berf.",
			"Sonst. Erl�se" AS "Erl�s Sonst.",
			"Order By",
			"Eink�ufer/Vorbesitzer",
			"Sel Name_EK_vpp43" AS "Name_Eink�ufer",
			'' AS "Konto",
			"Buch_Text",
			"Prev Owner" AS "Name_Lieferant",
			"Provisionen" AS "Erl�s Prov",
			"Erl�s" AS "Erl�s_FZG",
			"Ums. Fracht und �berf." AS "Erl�s �berf.",
			"Einsatz" AS "Einsatz FZG",
			1 AS "Menge_1",
			"DB1",
			"FZG_Detail",
			MAX("Unique Ident_unit_change_hist") OVER (PARTITION BY "Unit Number") AS c182
		FROM (
			SELECT T1."UNIT_NUMBER" AS "Unit Number",
				(rtrim(T1."UNIT_NUMBER")) + ' - ' + (rtrim(T16."Standort_Name")) + ' - ' + (
					rtrim((
							CASE 
								WHEN (T1."VEHICLE_TYPE" IS NULL)
									THEN (T1."VEHICLE_TYPE")
								ELSE (T1."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT")
								END
							))
					) + ' - ' + (rtrim(T5."SEL_NAME")) + ' - ' + (rtrim(((rtrim(T6."CUSTOMER_NUMBER")) + ' - ' + (rtrim(T6."NAME"))))) AS "FZG_Detail",
				(
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('10', '12', '24'))
							THEN (T13."ORDERS_GROSSVALUE" * - 1)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('15', '16', '18', '19', '90', '91', '93'))
							THEN (T13."ORDERS_GROSSVALUE" * - 1)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('14'))
							THEN (T13."ORDERS_GROSSVALUE" * - 1)
						ELSE (0)
						END
					) - (
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('11 '))
							THEN (T13."ORDERS_GROSSVALUE")
						ELSE (0)
						END
					) - (
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('13'))
							THEN (T13."ORDERS_GROSSVALUE")
						ELSE (0)
						END
					) - (
					(
						CASE 
							WHEN (T13."TRANSACTION_CODE" IN ('25 ', '26 ', '27 ', '28 ', '29 ', '30', '31', '32', '33', '17'))
								THEN (T13."ORDERS_GROSSVALUE")
							ELSE (0)
							END
						) + (
						CASE 
							WHEN (T13."TRANSACTION_CODE" IN ('37 ', '39 ', '40 ', '41 ', '47 ', '49 ', '50', '51', '59', '61', '63', '67', '69', '70 ', '75 '))
								THEN (T13."ORDERS_GROSSVALUE")
							ELSE (0)
							END
						)
					) AS "DB1",
				(
					(
						(
							CASE 
								WHEN (T13."TRANSACTION_CODE" IN ('25 ', '26 ', '27 ', '28 ', '29 ', '30', '31', '32', '33', '17'))
									THEN (T13."ORDERS_GROSSVALUE")
								ELSE (0)
								END
							) + (
							CASE 
								WHEN (T13."TRANSACTION_CODE" IN ('37 ', '39 ', '40 ', '41 ', '47 ', '49 ', '50', '51', '59', '61', '63', '67', '69', '70 ', '75 '))
									THEN (T13."ORDERS_GROSSVALUE")
								ELSE (0)
								END
							)
						)
					) AS "Einsatz",
				(
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('14'))
							THEN (T13."ORDERS_GROSSVALUE" * - 1)
						ELSE (0)
						END
					) AS "Ums. Fracht und �berf.",
				(
					(
						CASE 
							WHEN (T13."TRANSACTION_CODE" IN ('10', '12', '24'))
								THEN (T13."ORDERS_GROSSVALUE" * - 1)
							ELSE (0)
							END
						)
					) AS "Erl�s",
				(
					(
						CASE 
							WHEN (T13."TRANSACTION_CODE" IN ('13'))
								THEN (T13."ORDERS_GROSSVALUE")
							ELSE (0)
							END
						)
					) AS "Provisionen",
				T1."PREV_OWNER" AS "Prev Owner",
				(rtrim((((T13."DOCUMENT_NR"))))) + ' - ' + T13."TRANSACTION_CODE" + ' - ' + T13."REFERENCE" AS "Buch_Text",
				T12."SEL_NAME" AS "Sel Name_EK_vpp43",
				(rtrim(T12."SEL_NAME")) + ' / ' + T1."PREV_OWNER" AS "Eink�ufer/Vorbesitzer",
				CASE 
					WHEN (T11."Order_By" IS NULL)
						THEN (1)
					ELSE (T11."Order_By")
					END AS "Order By",
				(
					CASE 
						WHEN (T13."TRANSACTION_CODE" IN ('15', '16', '18', '19', '90', '91', '93'))
							THEN (T13."ORDERS_GROSSVALUE" * - 1)
						ELSE (0)
						END
					) AS "Sonst. Erl�se",
				T6."ZIPCODE" AS "Zipcode",
				(substring(T6."ZIPCODE", 1, 4)) AS "PLZ_4",
				(substring(T6."ZIPCODE", 1, 3)) AS "PLZ_3",
				(substring(T6."ZIPCODE", 1, 2)) AS "PLZ_2",
				(substring(T6."ZIPCODE", 1, 1)) AS "PLZ_1",
				T8."MODEL_TEXT" AS "Model Text",
				T11."Order_By" AS "Order By_ori",
				CASE 
					WHEN (T11."Fabrikat" IS NULL)
						THEN ('Fremdfabrikat')
					ELSE (T11."Fabrikat")
					END AS "Fabrikat",
				T16."Standort_Name" AS "Standort_Name_ori",
				T16."Standort_ID" AS "Standort_ID_ori",
				T16."Hauptbetrieb_Name" AS "Hauptbetrieb_Name_ori",
				T16."Hauptbetrieb_ID" AS "Hauptbetrieb_ID_ori",
				T11."Fabrikat" AS "Fabrikat_GC_Marke_ori",
				(rtrim(T13."TRANSACTION_CODE")) + ' - ' + (rtrim(T14."SPECIFY")) AS "TR Code",
				(left(T15."DEPARTMENT", 2)) AS "Standort",
				T15."UNIQUE_IDENT" AS "Unique Ident_unit_change_hist",
				T15."DEPARTMENT" AS "Department_unit_change_hist",
				(db_name()) AS "Mandant",
				CASE 
					WHEN (T10."DESCRIPTION" IN ('Opel', 'Peugeot'))
						THEN (T10."DESCRIPTION")
					ELSE ('Fremdfabrikat')
					END AS "Fabrikat_manuell",
				CASE 
					WHEN (T1."RECEPTION_DATE" IS NULL)
						THEN ((- 1 * datediff(day, T1."SALE_DATE", T1."PURCH_DATE")))
					ELSE ((- 1 * datediff(day, T1."SALE_DATE", T1."RECEPTION_DATE")))
					END AS "Standtage",
				CASE 
					WHEN (T13."TRANSACTION_CODE" IN ('26', '27', '28', '29', '31', '32', '33', '17'))
						THEN (T13."ORDERS_GROSSVALUE")
					ELSE (0)
					END AS "VK_Hilfen",
				CASE 
					WHEN (T13."TRANSACTION_CODE" IN ('37 ', '39 ', '40 ', '41 ', '47 ', '49 ', '50', '51', '59', '61', '63', '67', '69', '70 ', '75 '))
						THEN (T13."ORDERS_GROSSVALUE")
					ELSE (0)
					END AS "FZG-Kosten",
				CASE 
					WHEN (T13."TRANSACTION_CODE" IN ('25 ', '26 ', '27 ', '28 ', '29 ', '30', '31', '32', '33', '17'))
						THEN (T13."ORDERS_GROSSVALUE")
					ELSE (0)
					END AS "EK Fahrzeug",
				CASE 
					WHEN (T13."TRANSACTION_CODE" IN ('11 '))
						THEN (T13."ORDERS_GROSSVALUE")
					ELSE (0)
					END AS "Nachlass",
				T13."INT_VOUCHER_NO" AS "Int Voucher No",
				T13."DESTINATION" AS "Destination",
				T13."DEPARTMENT" AS "Department",
				T13."BOOK_KEEPING_CODE" AS "Book Keeping Code",
				T13."CLASSIFICATION_DTE" AS "Classification Dte",
				T13."DISCOUNT" AS "Discount",
				T13."ORDERS_GROSSVALUE" AS "Orders Grossvalue",
				T13."COSTS" AS "Costs",
				T13."REFERENCE" AS "Reference",
				T14."SPECIFY" AS "Specify_vpp5a",
				T14."TRANSACTION_CODE" AS "Transaction Code_vpp5a",
				T13."TRANSACTION_CODE" AS "Transaction Code",
				T13."ORDER_NUMBER" AS "Order Number",
				T13."ORDER_DATE" AS "Order Date",
				T13."DOCUMENT_NR" AS "Document Nr",
				T13."DOCUMENT_DATE" AS "Document Date",
				T13."FUNCTION_CODE" AS "Function Code",
				T13."PROGRAM" AS "Program",
				T13."HANDLER" AS "Handler",
				T13."TRANSACT_DATE" AS "Transact Date",
				T13."LINE_NO" AS "Line No",
				T13."LINE_TYPE" AS "Line Type",
				T13."UNIT_NUMBER" AS "Unit Number_Unit_Hist",
				T5."SEL_NAME" + ' / ' + ((rtrim(T6."CUSTOMER_NUMBER")) + ' - ' + (rtrim(T6."NAME"))) AS "FZG_Liste_4",
				CASE 
					WHEN (T12."SEL_NAME" IS NULL)
						THEN (' - ' + ' / ' + T1."PREV_OWNER")
					ELSE (T12."SEL_NAME" + ' / ' + T1."PREV_OWNER")
					END AS "FZG_Liste_2",
				((rtrim(T1."UNIT_NUMBER")) + ' - ' + ((rtrim(T6."CUSTOMER_NUMBER")) + ' - ' + (rtrim(T6."NAME")))) + ' - ' + T1."VEHICLE_TYPE" AS "FZG_Liste_1",
				T1."UNIT_NUMBER" + ' - ' + T1."OWNER" + ' - ' + (convert(VARCHAR(50), year(T8."ORIG_INV_DATE")) + '-' + convert(VARCHAR(50), month(T8."ORIG_INV_DATE")) + '-' + convert(VARCHAR(50), day(T8."ORIG_INV_DATE"))) AS "FZG_1",
				(rtrim(T1."UNIT_NUMBER")) + ' - ' + ((rtrim(T6."CUSTOMER_NUMBER")) + ' - ' + (rtrim(T6."NAME"))) AS "FZG",
				CASE 
					WHEN (T1."ECC_STATUS" = '21')
						THEN ('21 - Vorlauf')
					WHEN (T1."ECC_STATUS" = '25')
						THEN ('25 - f�r Kd best. FZG')
					WHEN (T1."ECC_STATUS" = '41')
						THEN ('41 - Bestand')
					WHEN (T1."ECC_STATUS" = '44')
						THEN ('44 - am Hof nicht fakt.')
					WHEN (T1."ECC_STATUS" = '64')
						THEN ('64 - verkauft')
					ELSE NULL
					END AS "ECC_Status_Text",
				T8."COLOUR_CODE" + ' - ' + T8."COLOUR_CF" AS "Farbe",
				T6."CUSTOMER_GROUP" + ' - ' + T7."CUST_GROUP_SPECIFY" AS "Kundenart",
				(rtrim(T6."CUSTOMER_NUMBER")) + ' - ' + (rtrim(T6."NAME")) AS "Kunde",
				CASE 
					WHEN (T1."VEHICLE_TYPE" IS NULL)
						THEN (T1."VEHICLE_TYPE")
					ELSE (T1."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT")
					END AS "Fahrzeugtyp",
				CASE 
					WHEN (T1."VEHICLE_TYPE" IN ('N', 'T', 'W', 'V', 'B'))
						THEN ('Neuwagen')
					WHEN (T1."VEHICLE_TYPE" IN ('D', 'G', 'K', 'R', 'M'))
						THEN ('Gebrauchtwagen')
					ELSE NULL
					END AS "Fahrzeugart",
				T8."CHASSIS_NUMBER" AS "Chassis Number",
				CASE 
					WHEN (
							(
								CASE 
									WHEN (T11."Fabrikat" IS NULL)
										THEN ('Fremdfabrikat')
									ELSE (T11."Fabrikat")
									END
								) = 'Fremdfabrikat'
							)
						THEN (T8."MODEL_TEXT")
					ELSE (T9."MOD_LIN_SPECIFY")
					END AS "Model",
				T10."DESCRIPTION" AS "Description_Global_Make",
				T5."SEL_NAME" AS "Sel Name_vpp43",
				(left(T1."SALES_DEPARTMENT", 2)) AS "Standort_Verkaufskostenstelle",
				T1."CLIENT_DB" AS "Hauptbetrieb",
				T1."SALE_DATE" AS "Sale Date",
				T8."LATEST_INV_DATE" AS "Latest Inv Date",
				T8."ORIG_INV_DATE" AS "Orig Inv Date",
				T10."GLOBAL_MAKE_CD" AS "Global Make Cd",
				T8."MAKE_CD" AS "Make Cd",
				T8."MAKE" AS "Make",
				T8."COLOUR_CODE" AS "Colour Code",
				T8."DRIVE_CODE" AS "Drive Code",
				T8."MOTOR_CODE" AS "Motor Code",
				T8."MILEAGE" AS "Mileage",
				T8."WORKSHOP_MODEL" AS "Workshop Model",
				T9."MOD_LIN_SPECIFY" AS "Mod Lin Specify_vpp5q",
				T9."MODEL_LINE" AS "Model Line_vpp5q",
				T8."MODEL_LINE" AS "Model Line",
				T8."COLOUR_CF" AS "Colour Cf",
				T8."SALESMAN_SALE" AS "Salesman Sale",
				T8."SALESMAN_BUY" AS "Salesman Buy",
				T8."ARRIVAL_DATE" AS "Arrival Date",
				T8."LATEST_REG_DATE" AS "Latest Reg Date",
				T8."FIRST_REG_DATE" AS "First Reg Date",
				T8."OWNER_CODE" AS "Owner Code",
				T8."REGISTER_NUMBER" AS "Register Number",
				T1."CONTRACT_DATE" AS "Contract Date",
				T1."ORDER_CONFIRM_DATE" AS "Order Confirm Date",
				T1."ORDER_ARRIVAL_DATE" AS "Order Arrival Date",
				T7."CUST_GROUP_SPECIFY" AS "Cust Group Specify_vpp48",
				T7."CUSTOMER_GROUP" AS "Customer Group_vpp48",
				T6."CUSTOMER_GROUP" AS "Customer Group_Cust",
				T6."NAME" AS "Name_Cust",
				T6."CUSTOMER_NUMBER" AS "Customer Number_Cust",
				T1."ACCOUNT_SALES" AS "Account Sales",
				T1."ACCOUNT_PURCHASE" AS "Account Purchase",
				T1."OWNER" AS "Owner",
				T1."LOCATION_CODE" AS "Location Code",
				T1."PA_NUMBER" AS "Pa Number",
				T1."BUYING_ORDER_DATE" AS "Buying Order Date",
				T1."BUYING_ORDER_NO" AS "Buying Order No",
				T1."STOCK_DAYS" AS "Stock Days",
				T1."REG_FEE" AS "Reg Fee",
				T1."SALES_PRICE_UNIT" AS "Sales Price Unit",
				T1."COST_UNIT" AS "Cost Unit",
				T1."PURCH_PRICE_UNIT" AS "Purch Price Unit",
				T1."BUD_COST" AS "Bud Cost",
				T1."BUD_DEDUCT_FEE" AS "Bud Deduct Fee",
				T1."BUD_REG_FEE" AS "Bud Reg Fee",
				T1."BUD_PURCH_PRICE" AS "Bud Purch Price",
				T1."BUD_SALE_DET" AS "Bud Sale Det",
				T1."SALE_INV_NUMBER" AS "Sale Inv Number",
				T5."SEL_FAMILY_NAME" AS "Sel Family Name_vpp43",
				T5."SELLER_CODE" AS "Seller Code_vpp43",
				T1."SALE_SALESMAN" AS "Sale Salesman",
				T4."DESCRIPTION" AS "Description_Dep",
				T4."DEPARTMENT_TYPE_ID" AS "Department Type Id_Dep",
				T1."SALES_DEPARTMENT" AS "Sales Department",
				T1."RECEPTION_DATE" AS "Reception Date",
				T1."PURCH_DATE" AS "Purch Date",
				T1."PURCH_SALSMAN_CODE" AS "Purch Salsman Code",
				T1."PURCH_DEPARTMENT" AS "Purch Department",
				T1."MILEAGE" AS "Mileage_2",
				T3."SPECIFY" AS "Specify_vpp5m",
				T3."BOOK_KEEPING_CODE" AS "Book Keeping Code_vpp5m",
				T1."BOOK_KEEPING_CODE" AS "Book Keeping Code_2",
				T1."DEPRECIATION" AS "Depreciation",
				T1."CAR_CREATION_DATE" AS "Car Creation Date",
				T2."VEHICLE_TYPE_TEXT" AS "Vehicle Type Text_vpp5r",
				T2."VEHICLE_TYPE" AS "Vehicle Type_vpp5r",
				T1."VEHICLE_TYPE" AS "Vehicle Type",
				T1."ECC_STATUS" AS "Ecc Status",
				T1."BASIS_NUMBER" AS "Basis Number"
			FROM (
				(
					(
						(
							(
								(
									(
										(
											(
												(
													(
														(
															(
																(
																	(
																		"OPTIMA"."import"."UNIT_FILE" T1 LEFT JOIN "OPTIMA"."import"."vPP5R" T2 ON (T1."VEHICLE_TYPE" = T2."VEHICLE_TYPE")
																			AND (T1."CLIENT_DB" = T2."CLIENT_DB")
																		) LEFT JOIN "OPTIMA"."import"."vPP5M" T3 ON (T1."BOOK_KEEPING_CODE" = T3."BOOK_KEEPING_CODE")
																		AND (T1."CLIENT_DB" = T3."CLIENT_DB")
																	) LEFT JOIN "OPTIMA"."import"."DEPARTMENT_TYPE" T4 ON (T1."SALES_DEPARTMENT" = T4."DEPARTMENT_TYPE_ID")
																	AND (T1."CLIENT_DB" = T4."CLIENT_DB")
																) LEFT JOIN "OPTIMA"."import"."VPP43" T5 ON (T1."SALE_SALESMAN" = T5."SELLER_CODE")
																AND (T1."CLIENT_DB" = T5."CLIENT_DB")
															) LEFT JOIN "OPTIMA"."import"."CUSTOMER" T6 ON (T1."ACCOUNT_SALES" = T6."CUSTOMER_NUMBER")
															AND (T1."CLIENT_DB" = T6."CLIENT_DB")
														) LEFT JOIN "OPTIMA"."import"."VPP48" T7 ON (T6."CUSTOMER_GROUP" = T7."CUSTOMER_GROUP")
														AND (T6."CLIENT_DB" = T7."CLIENT_DB")
													) LEFT JOIN "OPTIMA"."import"."VEHICLE" T8 ON (T1."BASIS_NUMBER" = T8."BASIS_NUMBER")
													AND (T1."CLIENT_DB" = T8."CLIENT_DB")
												) LEFT JOIN "OPTIMA"."import"."VPP5Q" T9 ON (
													(T8."MODEL_LINE" = T9."MODEL_LINE")
													AND (T8."MAKE_CD" = T9."MAKE_CD")
													)
												AND (T8."CLIENT_DB" = T9."CLIENT_DB")
											) LEFT JOIN "OPTIMA"."import"."GLOBAL_MAKE" T10 ON (T8."MAKE_CD" = T10."GLOBAL_MAKE_CD")
											AND (T8."CLIENT_DB" = T10."CLIENT_DB")
										) LEFT JOIN "OPTIMA"."data"."GC_Marken" T11 ON (T11."Client_DB" = T8."CLIENT_DB")
										AND (T11."Make" = T8."MAKE_CD")
									) LEFT JOIN "OPTIMA"."import"."VPP43" T12 ON (T1."PURCH_SALSMAN_CODE" = T12."SELLER_CODE")
									AND (T1."CLIENT_DB" = T12."CLIENT_DB")
								) LEFT JOIN "OPTIMA"."import"."UNIT_HISTORY" T13 ON (T1."UNIT_NUMBER" = T13."UNIT_NUMBER")
								AND (T1."CLIENT_DB" = T13."CLIENT_DB")
							) LEFT JOIN "OPTIMA"."import"."vPP5A" T14 ON (T13."TRANSACTION_CODE" = T14."TRANSACTION_CODE")
							AND (T13."CLIENT_DB" = T14."CLIENT_DB")
						) LEFT JOIN "OPTIMA"."import"."UNIT_CHANGE_HIST" T15 ON (
							(T15."UNIT_NUMBER" = T1."UNIT_NUMBER")
							AND (T15."DEPARTMENT" <> ' ')
							)
						AND (T1."CLIENT_DB" = T15."CLIENT_DB")
					) LEFT JOIN "OPTIMA"."data"."GC_Department" T16 ON (T15."CLIENT_DB" = T16."Hauptbetrieb")
					AND ((left(T15."DEPARTMENT", 1)) = T16."Standort")
				)
			WHERE (
					(
						(T1."ECC_STATUS" = '64')
						AND (NOT T13."TRANSACTION_CODE" IN ('76 ', '77 '))
						)
					AND (T1."SALE_DATE" >= convert(DATETIME, '2020-01-01 00:00:00.000'))
					)
			) D2
		) D1
	WHERE ("Unique Ident_unit_change_hist" = c182)
		-- order by "Unit Number" asc
	) D4