C11/System/AUTOLINE/IQD/NW/NW_GW_Bestand_GW.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_2",
	   T2."InvoiceSequence" as "Invoicesequence",
	   T2."InvoiceDate" as "Invoicedate_2",
	   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 (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END)) ELSE ((rtrim((((T1."StockbookNumber"))))) + ' - ' + (CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END)) END as "FZG",
	   T1."CustomerAccount" + ' - ' + T1."CustomerName" as "Kunde",
	   T7."VehicleMagicNumber" as "Vehiclemagicnumber_MK_Demo_Veh",
	   T7."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 (T7."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 (T7."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_2",
	   T1."VehicleDescriptio002" as "Modell_Beschreibung",
	   '' as "Name_Einkäufer_2",
	   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",
	   0 as "Boni",
	   T1."OverallSaleNet" - (T1."OverallSaleNet" - T1."LatestProfitValue") as "DB1_berechnet",
	   (getdate()) as "Invoice Date",
	   T1."InvoiceNumber" as "Invoicenumber",
	   T1."InvoiceDate" as "Invoicedate",
	   CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END as "Standtage",
	   CASE WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((-1 * datediff(day, (getdate()), T1."InvoiceDate"))) ELSE (0) END) > 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."BoughtBy" as "Boughtby",
	   T1."SupplierName" + ' / ' + T1."BoughtBy" as "Lieferant/Einkäufer",
	   T1."SupplierName" as "Name_Lieferant",
	   T1."BoughtBy" as "Name_Einkäufer",
	   '' as "FZG_Einkäufer",
	   '' as "EZ",
	   '' as "Km-stand",
	   '' as "Bestandsart",
	   T1."Cost" as "Einsatz"
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"."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'))
-- order by "Vehiclenumber" asc