C11/System/LOCOSOFT/IQD/NW/LOC_Belege_NW_GW_VK.sql

select "Accounting Date" as "Accounting Date",
	   "Document Type" as "Document Type",
	   "Document Number" as "Document Number",
	   "Position In Document" as "Position In Document",
	   (day((convert(datetime, (CASE WHEN ((MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet")) IS NULL) THEN ("Invoice Date_journal_accountings") ELSE ((MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet"))) END))) - c142)) as "Customer Number",
	   "Nominal Account Number" as "Nominal Account Number",
	   "Is Balanced" as "Is Balanced",
	   "Clearing Number" as "Clearing Number",
	   "Document Date" as "Document Date",
	   "Posted Value" as "Posted Value",
	   "Debit Or Credit" as "Debit Or Credit",
	   "Posted Count" as "Posted Count",
	   "Branch Number" as "Branch Number",
	   "Customer Contra Account" as "Customer Contra Account",
	   "Nominal Contra Account" as "Nominal Contra Account",
	   "Contra Account Text" as "Contra Account Text",
	   "Account Form Page Number" as "Account Form Page Number",
	   "Account Form Page Line" as "Account Form Page Line",
	   "Serial Number Each Month" as "Serial Number Each Month",
	   "Employee Number" as "Employee Number",
	   "Invoice Date_journal_accountings" as "Invoice Date_journal_accountings",
	   "Invoice Number" as "Invoice Number",
	   "Dunning Level" as "Dunning Level",
	   "Last Dunning Date" as "Last Dunning Date",
	   "Journal Page" as "Journal Page",
	   "Journal Line" as "Journal Line",
	   "Cash Discount" as "Cash Discount",
	   "Term Of Payment" as "Term Of Payment",
	   "Posting Text" as "Posting Text",
	   "Vehicle Reference" as "Vehicle Reference",
	   "Vat Id Number" as "Vat Id Number",
	   "Account Statement Number" as "Account Statement Number",
	   "Account Statement Page" as "Account Statement Page",
	   "Vat Key" as "Vat Key",
	   "Days For Cash Discount" as "Days For Cash Discount",
	   "Day Of Actual Accounting" as "Day Of Actual Accounting",
	   "Skr51 Branch" as "Skr51 Branch",
	   "Skr51 Make" as "Skr51 Make",
	   "Skr51 Cost Center" as "Skr51 Cost Center",
	   "Skr51 Sales Channel" as "Skr51 Sales Channel",
	   "Skr51 Cost Unit" as "Skr51 Cost Unit",
	   "Previously Used Account No" as "Previously Used Account No",
	   "Free Form Accounting Text" as "Free Form Accounting Text",
	   "Free Form Document Text" as "Free Form Document Text",
	   "Nom_Account_Is Profit Loss Account" as "Nom_Account_Is Profit Loss Account",
	   "Rechtseinheit" as "Rechtseinheit",
	   "Betrieb" as "Betrieb",
	   "Bookkeep Date" as "Bookkeep Date",
	   "Marke" as "Marke",
	   "Text" as "Text",
	   "Mandant" as "Mandant",
	   "Betrag" as "Betrag",
	   "Konto_mit_Bezeichnung" as "Konto_mit_Bezeichnung",
	   "Menge_2" as "Menge_2",
	   "Stellen Cost Center" as "Stellen Cost Center",
	   "KST" as "KST",
	   "Stellen Sales Channel" as "Stellen Sales Channel",
	   "Absatzkanal" as "Absatzkanal",
	   "Stellen Cost Unit" as "Stellen Cost Unit",
	   "Kostenträger_mit_Null" as "Kostenträger_mit_Null",
	   "Kostenträger" as "Kostenträger",
	   "GuV_Bilanz" as "GuV_Bilanz",
	   "Susa" as "Susa",
	   "Acct Nr" as "Acct Nr",
	   "Document Type In Journal" as "Document Type In Journal",
	   "Document Type Description" as "Document Type Description",
	   "FZG_ja_nein" as "FZG_ja_nein",
	   SUM("FZG_ja_nein") OVER (partition by "Vehicle Reference_berechnet") as "Summe_FZG_ja_nein",
	   CASE WHEN (SUM("FZG_ja_nein") OVER (partition by "Vehicle Reference_berechnet") > 0) THEN ('FZG-Geschäft') ELSE ('Buchungen ohne FZG-Geschäft') END as "FZG_Geschäft ja/nein",
	   "Invoice Type_Invoices" as "Invoice Type_Invoices",
	   "Invoice Number_Invoices" as "Invoice Number_Invoices",
	   "Invoice Date_Invoices" as "Invoice Date_Invoices",
	   "Is Canceled_Invoices" as "Is Canceled_Invoices",
	   "Vehicle Number_Invoices" as "Vehicle Number_Invoices",
	   MAX("Vehicle Number_Invoices") OVER (partition by "Vehicle Reference_berechnet") as "Vehicle_Number_Max",
	   "Invoice_Date_1" as "Invoice_Date_1",
	   MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet") as "Invoice Date_FZG",
	   CASE WHEN (MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet") IS NULL) THEN ("Invoice Date_journal_accountings") ELSE MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet") END as "Invoice Date",
	   SUM("Betrag") OVER (partition by "Vehicle Reference_berechnet") as "Summe Betrag",
	   "Internal Number_Vehicles" as "Internal Number_Vehicles",
	   "Vin_Vehicles" as "Vin_Vehicles",
	   "Dealer Vehicle Type" as "Dealer Vehicle Type",
	   "Fahrzeugtyp" as "Fahrzeugtyp",
	   "Fahrzeugart" as "Fahrzeugart",
	   "Make Number_Vehicles" as "Make Number_Vehicles",
	   "Description_Make" as "Description_Make",
	   "Fabrikat" as "Fabrikat",
	   "Model Code_Models" as "Model Code_Models",
	   "Model_Detail" as "Model_Detail",
	   "Model" as "Model",
	   "Owner Number_Vehicles" as "Owner Number_Vehicles",
	   "Holder Number_Vehicles" as "Holder Number_Vehicles",
	   "First Name_Owner_Customer" as "First Name_Owner_Customer",
	   "Family Name_Owner_Customer" as "Family Name_Owner_Customer",
	   "First Name_Holder_Customer" as "First Name_Holder_Customer",
	   "Family Name_Holder_Customer" as "Family Name_Holder_Customer",
	   "Kunde" as "Kunde",
	   "Sales_Channel_FZG" as "Sales_Channel_FZG",
	   MAX("Sales_Channel_FZG") OVER (partition by "Vehicle Reference_berechnet") as "Kundenart_Zahl",
	   CASE WHEN (MAX("Sales_Channel_FZG") OVER (partition by "Vehicle Reference_berechnet") BETWEEN 10 AND 19) THEN ('Privater Endkunde') WHEN (MAX("Sales_Channel_FZG") OVER (partition by "Vehicle Reference_berechnet") BETWEEN 20 AND 29) THEN ('Gewerbekunde') WHEN (MAX("Sales_Channel_FZG") OVER (partition by "Vehicle Reference_berechnet") BETWEEN 30 AND 39) THEN ('Großkunde') WHEN (MAX("Sales_Channel_FZG") OVER (partition by "Vehicle Reference_berechnet") BETWEEN 40 AND 49) THEN ('Sonderabnehmer') WHEN (MAX("Sales_Channel_FZG") OVER (partition by "Vehicle Reference_berechnet") BETWEEN 50 AND 59) THEN ('Wiederverkäufer') ELSE null END as "Kundenart",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort_alt" as "Standort_alt",
	   "Salesman Number_Vehicles" as "Salesman Number_Vehicles",
	   "Employee Number_Employees_Salesman" as "Employee Number_Employees_Salesman",
	   "Name_Employees_Salesman" as "Name_Employees_Salesman",
	   "Verkäufer" as "Verkäufer",
	   "Standort_FZG_Verkauf" as "Standort_FZG_Verkauf",
	   MAX("Standort_FZG_Verkauf") OVER (partition by "Vehicle Reference_berechnet") as "Standort_Zahl",
	   "Standort" as "Standort",
	   "FZG" as "FZG",
	   "Konto" as "Konto",
	   "Body Paint Code" as "Body Paint Code",
	   "Body Paint Description" as "Body Paint Description",
	   "Farbe" as "Farbe",
	   "Erlös FZG" as "Erlös FZG",
	   "Nachlass" as "Nachlass",
	   "VAK FZG" as "VAK FZG",
	   "Erlös Zulass." as "Erlös Zulass.",
	   "VAK Zulass." as "VAK Zulass.",
	   "Erlös Gar." as "Erlös Gar.",
	   "VAK Gar." as "VAK Gar.",
	   "Erlös Nachr./Aufb." as "Erlös Nachr./Aufb.",
	   "VAK Nachr./Aufb." as "VAK Nachr./Aufb.",
	   "VAK Fertigm." as "VAK Fertigm.",
	   "Erlös Überf." as "Erlös Überf.",
	   "VAK Überf." as "VAK Überf.",
	   "Erlös Sonst._8900" as "Erlös Sonst._8900",
	   "Erlös Sonst." as "Erlös Sonst.",
	   "VAK Sonst." as "VAK Sonst.",
	   "VAK intern" as "VAK intern",
	   "Erlös Prov." as "Erlös Prov.",
	   "Boni/VK-Hilfen" as "Boni/VK-Hilfen",
	   "Erlös ges." as "Erlös ges.",
	   "VAK ges." as "VAK ges.",
	   "Menge_1" as "Menge_1",
	   COUNT("Accounting Date") OVER (partition by "Vehicle Reference_berechnet") as "Menge_2",
	   ("Menge_1" / COUNT("Accounting Date") OVER (partition by "Vehicle Reference_berechnet")) as "Menge",
	   "Vehicle Reference_berechnet" as "Vehicle Reference_berechnet",
	   "var. Kosten" as "var. Kosten",
	   "Dealer Vehicle Type_Vehicles" as "Dealer Vehicle Type_Vehicles",
	   "Dealer Vehicle Number_Vehicles" as "Dealer Vehicle Number_Vehicles"
from 
(select "Accounting Date",
	   "Document Type",
	   "Document Number",
	   "Position In Document",
	   "Nominal Account Number",
	   "Is Balanced",
	   "Clearing Number",
	   "Document Date",
	   "Posted Value",
	   "Debit Or Credit",
	   "Posted Count",
	   "Branch Number",
	   "Customer Contra Account",
	   "Nominal Contra Account",
	   "Contra Account Text",
	   "Account Form Page Number",
	   "Account Form Page Line",
	   "Serial Number Each Month",
	   "Employee Number",
	   "Invoice Date_journal_accountings",
	   "Invoice Number",
	   "Dunning Level",
	   "Last Dunning Date",
	   "Journal Page",
	   "Journal Line",
	   "Cash Discount",
	   "Term Of Payment",
	   "Posting Text",
	   "Vehicle Reference",
	   "Vat Id Number",
	   "Account Statement Number",
	   "Account Statement Page",
	   "Vat Key",
	   "Days For Cash Discount",
	   "Day Of Actual Accounting",
	   "Skr51 Branch",
	   "Skr51 Make",
	   "Skr51 Cost Center",
	   "Skr51 Sales Channel",
	   "Skr51 Cost Unit",
	   "Previously Used Account No",
	   "Free Form Accounting Text",
	   "Free Form Document Text",
	   "Nom_Account_Is Profit Loss Account",
	   "Rechtseinheit",
	   "Betrieb",
	   "Accounting Date" as "Bookkeep Date",
	   "Marke",
	   "Text",
	   '1' as "Mandant",
	   "Betrag",
	   "Konto_mit_Bezeichnung",
	   "Menge_2",
	   "Stellen Cost Center",
	   "KST",
	   "Stellen Sales Channel",
	   "Absatzkanal",
	   "Stellen Cost Unit",
	   "Kostenträger_mit_Null",
	   "Kostenträger",
	   "GuV_Bilanz",
	   "Susa",
	   "Nominal Account Number" as "Acct Nr",
	   "Document Type In Journal",
	   "Document Type Description",
	   "FZG_ja_nein",
	   "Invoice Type_Invoices",
	   "Invoice Number_Invoices",
	   "Invoice Date_Invoices",
	   "Is Canceled_Invoices",
	   "Vehicle Number_Invoices",
	   "Invoice_Date_1",
	   "Internal Number_Vehicles",
	   "Vin_Vehicles",
	   "Dealer Vehicle Type",
	   "Fahrzeugtyp",
	   "Fahrzeugart",
	   "Make Number_Vehicles",
	   "Description_Make",
	   "Fabrikat",
	   "Model Code_Models",
	   "Model_Detail",
	   "Model",
	   "Owner Number_Vehicles",
	   "Holder Number_Vehicles",
	   "First Name_Owner_Customer",
	   "Family Name_Owner_Customer",
	   "First Name_Holder_Customer",
	   "Family Name_Holder_Customer",
	   "Kunde",
	   "Sales_Channel_FZG",
	   "Rechtseinheit" as "Hauptbetrieb",
	   "Betrieb" as "Standort_alt",
	   "Salesman Number_Vehicles",
	   "Employee Number_Employees_Salesman",
	   "Name_Employees_Salesman",
	   "Verkäufer",
	   "Standort_FZG_Verkauf",
	   "Standort",
	   "FZG",
	   "Konto_mit_Bezeichnung" as "Konto",
	   "Body Paint Code",
	   "Body Paint Description",
	   "Body Paint Description" as "Farbe",
	   "Erlös FZG",
	   "Nachlass",
	   "VAK FZG",
	   "Erlös Zulass.",
	   "VAK Zulass.",
	   "Erlös Gar.",
	   "VAK Gar.",
	   "Erlös Nachr./Aufb.",
	   "VAK Nachr./Aufb.",
	   "VAK Fertigm.",
	   "Erlös Überf.",
	   "VAK Überf.",
	   "Erlös Sonst._8900",
	   "Erlös Sonst.",
	   "VAK Sonst.",
	   "VAK intern",
	   "Erlös Prov.",
	   "Boni/VK-Hilfen",
	   "Erlös ges.",
	   "VAK ges.",
	   1 as "Menge_1",
	   "Vehicle Reference_berechnet",
	   "var. Kosten",
	   "Dealer Vehicle Type_Vehicles",
	   "Dealer Vehicle Number_Vehicles",
	   c152 as c142,
	   MAX("Vehicle Number_Invoices") OVER (partition by "Vehicle Reference_berechnet") as c143,
	   (CASE WHEN ((MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet")) IS NULL) THEN (("Invoice Date_journal_accountings")) ELSE ((MAX("Invoice_Date_1") OVER (partition by "Vehicle Reference_berechnet"))) END) as c144,
	   (CASE WHEN ((SUM("FZG_ja_nein") OVER (partition by "Vehicle Reference_berechnet")) > 0) THEN ('FZG-Geschäft') ELSE ('Buchungen ohne FZG-Geschäft') END) as c145
from 
(select (left(T1."vehicle_reference",7)) + '_' + (right(T1."vehicle_reference",17)) as "Vehicle Reference_berechnet",
	   (((nconvert((left(T1."invoice_date",4)) + (substring(T1."invoice_date", 6, 2)) + (substring(T1."invoice_date", 9, 2)))))) as "Invoice Date_journal_accountings",
	   T2."in_buy_invoice_no_date" as c152,
	   T8."dealer_vehicle_number" as "Dealer Vehicle Number_Vehicles",
	   T8."dealer_vehicle_type" as "Dealer Vehicle Type_Vehicles",
	   CASE WHEN (T1."nominal_account_number" IN (4300,4560,4580,4530,4540,4590,4591,4690,4621,4622,4623,4500,4501,4502,4503,4504,5501)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "var. Kosten",
	   (CASE WHEN (T1."nominal_account_number" IN (7000,7010,7100,7101,7110,7111,7120,7510)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + ((CASE WHEN (T1."nominal_account_number" IN (7200)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7210)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7220)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7240)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7030)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END)) + (CASE WHEN (T1."nominal_account_number" IN (5001,5005,5006,5007,5008,5003,5004)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7800,7850)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) as "VAK ges.",
	   (CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8111,8112,8510,8928,8934)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + ((CASE WHEN (T1."nominal_account_number" IN (8200)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8210)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8220)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8030)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8900)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END)) + (CASE WHEN (T1."nominal_account_number" IN (8800,8801,8870,8820,8830)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8001,8011,5002)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) as "Erlös ges.",
	   CASE WHEN (T1."nominal_account_number" IN (7800,7850)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "Boni/VK-Hilfen",
	   CASE WHEN (T1."nominal_account_number" IN (8800,8801,8870,8820,8830)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös Prov.",
	   CASE WHEN (T1."nominal_account_number" IN (5001,5005,5006,5007,5008,5003,5004)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK intern",
	   (CASE WHEN (T1."nominal_account_number" IN (7200)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7210)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7220)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7240)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (7030)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END) as "VAK Sonst.",
	   (CASE WHEN (T1."nominal_account_number" IN (8200)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8210)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8220)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8030)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) + (CASE WHEN (T1."nominal_account_number" IN (8900)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END) as "Erlös Sonst.",
	   CASE WHEN (T1."nominal_account_number" IN (8900)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös Sonst._8900",
	   CASE WHEN (T1."nominal_account_number" IN (7030)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK Überf.",
	   CASE WHEN (T1."nominal_account_number" IN (8030)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös Überf.",
	   CASE WHEN (T1."nominal_account_number" IN (7240)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK Fertigm.",
	   CASE WHEN (T1."nominal_account_number" IN (7220)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK Nachr./Aufb.",
	   CASE WHEN (T1."nominal_account_number" IN (8220)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös Nachr./Aufb.",
	   CASE WHEN (T1."nominal_account_number" IN (7210)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK Gar.",
	   CASE WHEN (T1."nominal_account_number" IN (8210)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös Gar.",
	   CASE WHEN (T1."nominal_account_number" IN (7200)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK Zulass.",
	   CASE WHEN (T1."nominal_account_number" IN (8200)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös Zulass.",
	   CASE WHEN (T1."nominal_account_number" IN (7000,7010,7100,7101,7110,7111,7120,7510)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END)) ELSE (0) END as "VAK FZG",
	   CASE WHEN (T1."nominal_account_number" IN (8001,8011,5002)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Nachlass",
	   CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8111,8112,8510,8928,8934)) THEN ((CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) * -1) ELSE (0) END as "Erlös FZG",
	   T8."body_paint_description" as "Body Paint Description",
	   T8."body_paint_code" as "Body Paint Code",
	   ((left((((T1."nominal_account_number"))),4)) + ' - ' + T4."account_description") as "Konto_mit_Bezeichnung",
	   (left(((left(T1."vehicle_reference",7)) + '_' + (right(T1."vehicle_reference",17))),7)) + ' / ' + ((left((((T8."internal_number"))),5))) + ' - ' + T10."description" + ' - ' + (((left((((T8."holder_number"))),7)) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ('') ELSE (T3."first_name") END) + ' ' + T3."family_name")) as "FZG",
	   CASE WHEN (T2."out_subsidiary" IS NULL) THEN ((('0' + (((T1."skr51_branch")))))) ELSE ('0' + (((T2."out_subsidiary")))) END as "Standort",
	   T2."out_subsidiary" as "Standort_FZG_Verkauf",
	   (left((((T8."salesman_number"))),4)) + ' - ' + T12."name" as "Verkäufer",
	   T12."name" as "Name_Employees_Salesman",
	   T12."employee_number" as "Employee Number_Employees_Salesman",
	   T8."salesman_number" as "Salesman Number_Vehicles",
	   ('0' + (((T1."skr51_branch")))) as "Betrieb",
	   T1."subsidiary_to_company_ref" as "Rechtseinheit",
	   CASE WHEN (((CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8510)) THEN (1) ELSE (0) END) = 1) and (T7."is_canceled" <> '1')) THEN (T1."skr51_sales_channel") ELSE null END as "Sales_Channel_FZG",
	   ((left((((T8."holder_number"))),7)) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ('') ELSE (T3."first_name") END) + ' ' + T3."family_name") as "Kunde",
	   T3."family_name" as "Family Name_Holder_Customer",
	   CASE WHEN (T3."first_name" IS NULL) THEN ('') ELSE (T3."first_name") END as "First Name_Holder_Customer",
	   T11."family_name" as "Family Name_Owner_Customer",
	   T11."first_name" as "First Name_Owner_Customer",
	   T8."holder_number" as "Holder Number_Vehicles",
	   T8."owner_number" as "Owner Number_Vehicles",
	   (left((ucase(T10."description")),3)) as "Model",
	   T10."description" as "Model_Detail",
	   T10."model_code" as "Model Code_Models",
	   CASE WHEN (((rtrim((((T1."skr51_make"))))) + ' - ' + T5."skr51_make_description") = '3 - Husqvarna') THEN ('Husqvarna') ELSE (T9."description") END as "Fabrikat",
	   T9."description" as "Description_Make",
	   T8."make_number" as "Make Number_Vehicles",
	   CASE WHEN ((CASE WHEN (((left(T1."vehicle_reference",1))) IN ('N')) THEN ('NW') WHEN (((left(T1."vehicle_reference",1))) IN ('V')) THEN ('VFW') WHEN (((left(T1."vehicle_reference",1))) IN ('G','D')) THEN ('GW') WHEN (((left(T1."vehicle_reference",1))) IN ('T')) THEN ('TZ') ELSE null END) IN ('VFW','TZ','NW')) THEN ('Neuwagen') ELSE ('Gebrauchtwagen') END as "Fahrzeugart",
	   CASE WHEN (((left(T1."vehicle_reference",1))) IN ('N')) THEN ('NW') WHEN (((left(T1."vehicle_reference",1))) IN ('V')) THEN ('VFW') WHEN (((left(T1."vehicle_reference",1))) IN ('G','D')) THEN ('GW') WHEN (((left(T1."vehicle_reference",1))) IN ('T')) THEN ('TZ') ELSE null END as "Fahrzeugtyp",
	   (left(T1."vehicle_reference",1)) as "Dealer Vehicle Type",
	   T8."vin" as "Vin_Vehicles",
	   T8."internal_number" as "Internal Number_Vehicles",
	   CASE WHEN ((CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8510)) THEN (1) ELSE (0) END) = 1) THEN (((((nconvert((left(T1."invoice_date",4)) + (substring(T1."invoice_date", 6, 2)) + (substring(T1."invoice_date", 9, 2)))))))) ELSE null END as "Invoice_Date_1",
	   T7."vehicle_number" as "Vehicle Number_Invoices",
	   T7."is_canceled" as "Is Canceled_Invoices",
	   T7."invoice_date" as "Invoice Date_Invoices",
	   T7."invoice_number" as "Invoice Number_Invoices",
	   T7."invoice_type" as "Invoice Type_Invoices",
	   CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8510)) THEN (1) ELSE (0) END as "FZG_ja_nein",
	   T6."document_type_description" as "Document Type Description",
	   T6."document_type_in_journal" as "Document Type In Journal",
	   T1."nominal_account_number" as "Nominal Account Number",
	   (left((((T1."nominal_account_number"))),1)) as "Susa",
	   CASE WHEN (T4."is_profit_loss_account" = 'J') THEN ('2') ELSE ('1') END as "GuV_Bilanz",
	   CASE WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49) THEN (((rtrim((((T1."skr51_make"))))) + ' - ' + (rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name"))) ELSE ((rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name")) END as "Kostenträger",
	   (rtrim((((T1."skr51_make"))))) + ' - ' + (rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name") as "Kostenträger_mit_Null",
	   (len((((T1."skr51_cost_unit"))) + 'Z') - 1) as "Stellen Cost Unit",
	   (rtrim((((T1."skr51_sales_channel"))))) + ' - ' + T5."skr51_sales_channel_name" as "Absatzkanal",
	   (len((((T1."skr51_sales_channel"))) + 'Z') - 1) as "Stellen Sales Channel",
	   (rtrim((((T1."skr51_cost_center"))))) + ' - ' + T5."skr51_cost_center_name" as "KST",
	   (len((((T1."skr51_cost_center"))) + 'Z') - 1) as "Stellen Cost Center",
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100 * -1) ELSE (T1."posted_count" / 100) END as "Menge_2",
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END as "Betrag",
	   CASE WHEN ((-1 * datediff(day, (getdate()), (convert(datetime, T1."accounting_date")))) <= 360) THEN (T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + (rtrim((((T1."employee_number")))))) ELSE ('Buchungen älter 360 Tage') END as "Text",
	   (rtrim((((T1."skr51_make"))))) + ' - ' + T5."skr51_make_description" as "Marke",
	   T1."accounting_date" as "Accounting Date",
	   T4."is_profit_loss_account" as "Nom_Account_Is Profit Loss Account",
	   CASE WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49) THEN ('Neuwagen') WHEN (T1."skr51_cost_unit" BETWEEN 50 AND 59) THEN ('Gebrauchtwagen') WHEN (T1."skr51_cost_unit" BETWEEN 60 AND 69) THEN ('Teile & Zubehör') WHEN (T1."skr51_cost_unit" BETWEEN 70 AND 79) THEN ('Service') WHEN (T1."skr51_cost_unit" = 0) THEN ('Ohne Kostenträger') ELSE null END as "Free Form Document Text",
	   T3."zip_code" as "Free Form Accounting Text",
	   T1."previously_used_account_no" as "Previously Used Account No",
	   T1."skr51_cost_unit" as "Skr51 Cost Unit",
	   T1."skr51_sales_channel" as "Skr51 Sales Channel",
	   T1."skr51_cost_center" as "Skr51 Cost Center",
	   T1."skr51_make" as "Skr51 Make",
	   T1."skr51_branch" as "Skr51 Branch",
	   T1."day_of_actual_accounting" as "Day Of Actual Accounting",
	   T1."days_for_cash_discount" as "Days For Cash Discount",
	   T1."vat_key" as "Vat Key",
	   T1."account_statement_page" as "Account Statement Page",
	   T1."account_statement_number" as "Account Statement Number",
	   T1."vat_id_number" as "Vat Id Number",
	   T1."vehicle_reference" as "Vehicle Reference",
	   T1."posting_text" as "Posting Text",
	   T1."term_of_payment" as "Term Of Payment",
	   T1."cash_discount" as "Cash Discount",
	   T1."journal_line" as "Journal Line",
	   T1."journal_page" as "Journal Page",
	   T1."last_dunning_date" as "Last Dunning Date",
	   T1."dunning_level" as "Dunning Level",
	   T1."invoice_number" as "Invoice Number",
	   T1."employee_number" as "Employee Number",
	   T1."serial_number_each_month" as "Serial Number Each Month",
	   T1."account_form_page_line" as "Account Form Page Line",
	   T1."account_form_page_number" as "Account Form Page Number",
	   T1."contra_account_text" as "Contra Account Text",
	   T1."nominal_contra_account" as "Nominal Contra Account",
	   T1."customer_contra_account" as "Customer Contra Account",
	   T1."branch_number" as "Branch Number",
	   T1."posted_count" as "Posted Count",
	   T1."debit_or_credit" as "Debit Or Credit",
	   T1."posted_value" as "Posted Value",
	   T1."document_date" as "Document Date",
	   T1."clearing_number" as "Clearing Number",
	   T1."is_balanced" as "Is Balanced",
	   T1."position_in_document" as "Position In Document",
	   T1."document_number" as "Document Number",
	   T1."document_type" as "Document Type"
from "nominal_accounts" T4,
	(((((((((("journal_accountings" T1 left outer join "vehicles" T8 on (right(T1."vehicle_reference",17)) = T8."vin") left outer join "LOCOSOFT"."dbo"."dealer_vehicles" T2 on (T8."dealer_vehicle_type" = T2."dealer_vehicle_type") and (T8."dealer_vehicle_number" = T2."dealer_vehicle_number")) left outer join "customers_suppliers" T3 on T3."customer_number" = T8."holder_number") left outer join "accounts_characteristics" T5 on (((((T5."skr51_make" = T1."skr51_make") and (T5."skr51_cost_center" = T1."skr51_cost_center")) and (T5."skr51_sales_channel" = T1."skr51_sales_channel")) and (T5."skr51_cost_unit" = T1."skr51_cost_unit")) and (T5."skr51_branch" = T1."skr51_branch")) and (T5."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref")) left outer join "document_types" T6 on T6."document_type_in_journal" = T1."document_type") left outer join "invoices" T7 on T7."invtype_invnr" = T1."invoice_number") left outer join "makes" T9 on T8."make_number" = T9."make_number") left outer join "models" T10 on (T8."make_number" = T10."make_number") and (T8."model_code" = T10."model_code")) left outer join "customers_suppliers" T11 on T11."customer_number" = T8."owner_number") left outer join "employees" T12 on (T8."salesman_number" <> 0) and (T8."salesman_number" = T12."salesman_number"))
where ((T4."nominal_account_number" = T1."nominal_account_number") and (T4."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref"))
and (((T4."is_profit_loss_account" = 'J') and (T1."vehicle_reference" <> ' ')) and (T1."nominal_account_number" IN (8000,8001,8010,8011,5002,8100,8110,8111,8200,8210,8510,8800,8820,8830,8870,8928,8934,7000,7010,7100,7101,7110,7111,7120,7200,7210,7240,7510,5001,5005,5006,5007,5008,5003,5004,4300,4560,4580,4530,4540,4590,4591,4690,4621,4622,4623,4500,4501,4502,4503,4504,5501,7220,7800,7850,7801,7802,7803,7804,7805,7806,7807,7820,7825,7827,7808,8030,7030,8870,8871,8872,8220,7220)))
) D2
) D1
where (((((c143 = "Internal Number_Vehicles") or ("Internal Number_Vehicles" IS NULL)) or (c143 IS NULL)) and (c144 >= convert(date, '2017-01-01'))) and (c145 = 'FZG-Geschäft'))
-- order by "Vehicle Reference_berechnet" asc,"Vehicle Reference" asc,"Nominal Account Number" asc