select T1."StockbookNumber" as "Stockbooknumber", T1."Franchise" as "Franchise", T1."ModelCode" as "Modelcode", T1."ACSStatus" as "Acsstatus", T1."VariantCode" as "Variantcode", T1."VehicleDescriptio001" as "Vehicledescriptio001", T1."VehicleDescriptio002" as "Vehicledescriptio002", T1."VehicleDescriptio003" as "Vehicledescriptio003", T1."VehicleDescriptio004" as "Vehicledescriptio004", T1."PhysicalBranch" as "Physicalbranch", 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."CusAddressPostcode" as "Cusaddresspostcode", 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."InvAddressPostcode" as "Invaddresspostcode", T1."CustomerOrderNo" as "Customerorderno", T1."OrderDate" as "Orderdate", T1."SOrderType" as "Sordertype", T1."SalesExecutive001" as "Salesexecutive001", 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."ConsignmentTotal" as "Consignmenttotal", T1."OverallSaleNet" as "Overallsalenet_Erlös_FZG", T1."RecovOptionPrice015" as "Recovoptionprice015_VK-Hilfe", T1."SupplierName" as "Suppliername_2", T1."Client_DB" as "Client Db", 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", T3."FullName002" as "Fabrikat", (left((ucase(T1."VehicleDescriptio002")),4)) as "Model", CASE WHEN (T1."FullChassis" IS NOT NULL) THEN ((rtrim((((T1."StockbookNumber"))))) + '/' + T1."FullChassis" + ' - ' + (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END)) ELSE ((rtrim((((T1."StockbookNumber"))))) + ' - ' + (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END)) END as "FZG", T1."CustomerAccount" + ' - ' + T1."CustomerName" as "Kunde", T7."VehicleMagicNumber" as "Vehiclemagicnumber_MK_Demo_Veh", T7."VehicleType" as "Vehicletype_MK_Demo_Veh", ('Neuwagen') as "Fahrzeugart", CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') 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 (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') 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."CusAddressPostcode",1)) as "PLZ_1", (left(T1."CusAddressPostcode",2)) as "PLZ_2", (left(T1."CusAddressPostcode",3)) as "PLZ_3", (left(T1."CusAddressPostcode",4)) as "PLZ_4", T1."CusAddressPostcode" as "PLZ Code_Deb", T1."SupplierName" as "Name_Lieferant_2", T1."VehicleDescriptio002" as "Modell_Beschreibung", '' as "Name_Einkäufer", CASE WHEN (T1."LatestProfitValue" < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as "DB1 < 0", 1 as "Menge", T1."OverallSaleNet" as "Erlös_FZG", T1."OverallSaleNet" - T1."LatestProfitValue" as "Einsatz FZG", T1."RecovOptionPrice015" as "Boni", T1."OverallSaleNet" - (T1."OverallSaleNet" - T1."LatestProfitValue") as "DB1_berechnet", (getdate()) as "Invoice Date", T1."ConsignmentDate" as "Consignmentdate", T1."EstimDeliveryDate" as "Estimdeliverydate", T1."EstimDateReceived" as "Estimdatereceived", (-1 * datediff(day, (getdate()), T1."ConsignmentDate")) as "Standtage", CASE WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) > 360) THEN ('> 360 Tage') ELSE null END as "Standtagestaffel", T1."ColourWheelbaseDesc" as "Colourwheelbasedesc", T1."ColourWheelbaseDesc" as "Farbe", T1."PhysicalBranch" as "Standort_1", T1."SupplierName" as "Suppliername", T1."SupplierName" as "Name_Lieferant", '' as "Lieferant/Einkäufer", '' as "Einkäufer", '' as "FZG_Einkäufer", '' as "EZ", '' as "Km-stand", '' as "Bestandsart", T1."Cost" as "Einsatz" from (((((("AUTOLINE"."import"."VS_NewVehicleStock" 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"."MK_DemonstrationVehicles" T7 on (T1."CRMVehicleMagic" = T7."VehicleMagicNumber") and (T7."Client_DB" <> '00')) where (((((T1."SalesInvoiceNumber" IS NULL) or (T1."SalesInvoiceNumber" = 0)) or (T1."InvoiceTotal" = 0)) and (T1."Client_DB" <> '00')) and (T1."ConsignmentDate" IS NOT NULL)) -- order by "Stockbooknumber" asc