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')) THEN (T10."DESCRIPTION") ELSE ('Fremdfabrikat') END as "Fabrikat_manuell",
	   CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(T1."SALE_DATE" - T1."PURCH_DATE"))) ELSE ((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','M','V','B')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','G','K','R')) 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 outer join "OPTIMA"."import"."vPP5R" T2 on (T1."VEHICLE_TYPE" = T2."VEHICLE_TYPE") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP5M" T3 on (T1."BOOK_KEEPING_CODE" = T3."BOOK_KEEPING_CODE") and (T1."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on (T1."SALES_DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") and (T1."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T5 on (T1."SALE_SALESMAN" = T5."SELLER_CODE") and (T1."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."CUSTOMER" T6 on (T1."ACCOUNT_SALES" = T6."CUSTOMER_NUMBER") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP48" T7 on (T6."CUSTOMER_GROUP" = T7."CUSTOMER_GROUP") and (T6."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."VEHICLE" T8 on (T1."BASIS_NUMBER" = T8."BASIS_NUMBER") and (T1."CLIENT_DB" = T8."CLIENT_DB")) left outer 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 outer join "OPTIMA"."import"."GLOBAL_MAKE" T10 on (T8."MAKE_CD" = T10."GLOBAL_MAKE_CD") and (T8."CLIENT_DB" = T10."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Marken" T11 on (T11."Client_DB" = T8."CLIENT_DB") and (T11."Make" = T8."MAKE_CD")) left outer join "OPTIMA"."import"."VPP43" T12 on (T1."PURCH_SALSMAN_CODE" = T12."SELLER_CODE") and (T1."CLIENT_DB" = T12."CLIENT_DB")) left outer join "OPTIMA"."import"."UNIT_HISTORY" T13 on (T1."UNIT_NUMBER" = T13."UNIT_NUMBER") and (T1."CLIENT_DB" = T13."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP5A" T14 on (T13."TRANSACTION_CODE" = T14."TRANSACTION_CODE") and (T13."CLIENT_DB" = T14."CLIENT_DB")) left outer 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 outer join "OPTIMA"."data"."GC_Department" T16 on (T15."CLIENT_DB" = T16."Hauptbetrieb") and ((left(T15."DEPARTMENT",2)) = 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