select T1."StockbookNumber" as "Stockbooknumber", T1."Franchise" as "Franchise", T1."ModelCode" as "Modelcode", T1."ModelVariant" as "Modelvariant", T1."VehicleDescriptio001" as "Vehicledescriptio001", T1."VehicleDescriptio002" as "Vehicledescriptio002", T1."VehicleDescriptio003" as "Vehicledescriptio003", T1."VehicleDescriptio004" as "Vehicledescriptio004", T1."PhysicalBranch" as "Physicalbranch", T1."ACSStatus" as "Acsstatus", T1."FullChassis" as "Fullchassis", T1."RegistrationNumber" as "Registrationnumber", T1."RegDate" as "Regdate", T1."CustomerName" as "Customername", T1."CustomerAddress001" as "Customeraddress001", T1."CustomerAddress002" as "Customeraddress002", T1."CustomerAddress003" as "Customeraddress003", T1."CustomerAddress004" as "Customeraddress004", T1."CustomerAddress005" as "Customeraddress005", T1."CustomerPostcode" as "Customerpostcode", T1."InvoiceName" as "Invoicename", T1."InvoiceAddress001" as "Invoiceaddress001", T1."InvoiceAddress002" as "Invoiceaddress002", T1."InvoiceAddress003" as "Invoiceaddress003", T1."InvoiceAddress004" as "Invoiceaddress004", T1."InvoiceAddress005" as "Invoiceaddress005", T1."InvAddrPostcode" as "Invaddrpostcode", T1."CustomerOrderNo" as "Customerorderno", T1."OrderDate" as "Orderdate", T1."SOrderType" as "Sordertype", T1."SalesExecutive001" as "Salesexecutive001", T1."DeliveryDate" as "Deliverydate", T1."SalesInvoiceNumber" as "Salesinvoicenumber", T1."InvoiceAccount" as "Invoiceaccount", T1."CustomerAccount" as "Customeraccount", T1."InvoiceTotal" as "Invoicetotal", T1."LatestProfitValue" as "Latestprofitvalue_DB1", T1."Cost" as "Cost_EW", T1."MainInvoiceTotal" as "Maininvoicetotal", T1."OverallSaleNet" as "Overallsalenet_Erlös_FZG", T2."VehicleType" as "Vehicletype", T2."VehicleNumber" as "Vehiclenumber", T2."InvoiceNumber" as "Invoicenumber", T2."InvoiceSequence" as "Invoicesequence", T2."InvoiceDate" as "Invoicedate", T2."InvoiceValue" as "Invoicevalue", T3."FranchiseCode" as "Franchisecode", T3."FullName001" as "Fullname001", T3."FullName002" as "Fullname002", T3."FullName003" as "Fullname003", T4."LocationCode" as "Locationcode", T4."Description" as "Description", T4."LocationName" as "Locationname", T5."UserID" as "Userid", T5."UserName" as "Username", T6."OrderType" as "Ordertype", T6."Description001" as "Description001", T6."Description002" as "Description002", T6."Description003" as "Description003", T1."Client_DB" as "Hauptbetrieb_ID", '' as "Hauptbetrieb_Name", T4."LocationCode" as "Standort_ID", T4."LocationCode" + ' - ' + T4."Description" as "Standort_Name", T5."UserName" as "Verkäufer", CASE WHEN (T1."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T1."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T1."Franchise" = 'Z') THEN ('Fremd') WHEN (T1."Franchise" = 'S') THEN ('Smart') WHEN (T1."Franchise" = 'F') THEN ('Fremd TRAPO') ELSE null END as "Fabrikat", T7."Description002" as "Model", T2."VehicleType" + (rtrim((((T2."VehicleNumber"))))) + '/' + (right(T1."FullChassis",7)) + ' - ' + T5."UserName" + ' - ' + T1."CustomerName" as "FZG", T1."CustomerAccount" + ' - ' + T1."CustomerName" as "Kunde", T8."VehicleMagicNumber" as "Vehiclemagicnumber_MK_Demo_Veh", T8."VehicleType" as "Vehicletype_MK_Demo_Veh", CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END as "Fahrzeugart", CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T8."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END as "Fahrzeugtyp", '' as "Konto", '' as "Fahrzeugtyp_1", T6."OrderType" + ' - ' + T6."Description002" as "Kundenart", T2."VehicleType" + (rtrim((((T2."VehicleNumber"))))) + '/' + (right(T1."FullChassis",7)) + ' - ' + T4."LocationCode" + ' - ' + (CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T8."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END) + ' - ' + T5."UserName" + ' - ' + T1."CustomerName" + ' - ' + (convert(varchar(50), year(T2."InvoiceDate")) + '-' + convert(varchar(50), month(T2."InvoiceDate")) + '-' + convert(varchar(50), day(T2."InvoiceDate"))) as "FZG_Detail", (left(T1."CustomerPostcode",1)) as "PLZ_1", (left(T1."CustomerPostcode",2)) as "PLZ_2", (left(T1."CustomerPostcode",3)) as "PLZ_3", (left(T1."CustomerPostcode",4)) as "PLZ_4", T1."CustomerPostcode" as "PLZ Code_Deb", T1."SupplierName" as "Name_Lieferant", T1."VehicleDescriptio002" as "Modell_Beschreibung", T1."BoughtBy" as "Name_Einkäufer", CASE WHEN (T1."LatestProfitValue" < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as "DB1 < 0", 1 as "Menge", T1."OverallSaleNet" - T1."OtherCosts012" as "Erlös_FZG", (T1."OverallSaleNet" - T1."OtherCosts012") - T1."LatestProfitValue" as "Einsatz FZG", 0 as "Boni", (T1."OverallSaleNet" - T1."OtherCosts012") - ((T1."OverallSaleNet" - T1."OtherCosts012") - T1."LatestProfitValue") as "DB1_berechnet", T2."InvoiceDate" as "Invoice Date", T1."InvoiceDate" as "Invoicedate_VS_UsedVehicle", (-1 * datediff(day, T2."InvoiceDate", T1."InvoiceDate")) as "Standtage", T7."Description002" as "Description002_Dd_Modelrecords", T1."OtherCosts012" as "Othercosts012" from ((((((("AUTOLINE"."import"."VS_UsedVehicleStock" T1 left outer join "AUTOLINE"."import"."VS_InvoiceStockFile" T2 on (T1."SalesInvoiceNumber" = T2."InvoiceNumber") and (T1."Client_DB" = T2."Client_DB")) left outer join "AUTOLINE"."import"."DD_FranchiseRecords" T3 on T1."Franchise" = T3."FranchiseCode") left outer join "AUTOLINE"."import"."GB_CompanyLocations" T4 on T1."SellingBranch" = T4."LocationCode") left outer join "AUTOLINE"."import"."GB_UserDetails" T5 on T1."SalesExecutive001" = T5."UserID") left outer join "AUTOLINE"."import"."VS_SalesOrderTypes" T6 on (T1."SOrderType" = T6."OrderType") and (T1."Client_DB" = T6."Client_DB")) left outer join "AUTOLINE"."import"."DD_ModelRecords" T7 on (T7."ShortFranchise" = T1."Franchise") and (T7."ModelCode" = T1."ModelCode")) left outer join "AUTOLINE"."import"."MK_DemonstrationVehicles" T8 on (T1."CRMVehicleMagic" = T8."VehicleMagicNumber") and (T8."Client_DB" <> '00')) where ((((((T1."SalesInvoiceNumber" IS NOT NULL) and (T1."SalesInvoiceNumber" <> 0)) and (T1."Client_DB" <> '00')) and (T1."InvoiceTotal" <> 0)) and (T2."InvoiceDate" >= convert(datetime, '2018-01-01 00:00:00.000'))) and (T1."Client_DB" = '10')) -- order by "Vehiclenumber" asc