C11/System/AUTOLINE/IQD/NW/NW_GW_VK_GW_final.sql

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