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" as "Menge",
	   "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 c230 as "Accounting Date",
	   c271 as "Document Type",
	   c270 as "Document Number",
	   c269 as "Position In Document",
	   c216 as "Nominal Account Number",
	   c268 as "Is Balanced",
	   c267 as "Clearing Number",
	   c266 as "Document Date",
	   c265 as "Posted Value",
	   c264 as "Debit Or Credit",
	   c263 as "Posted Count",
	   c262 as "Branch Number",
	   c261 as "Customer Contra Account",
	   c260 as "Nominal Contra Account",
	   c259 as "Contra Account Text",
	   c258 as "Account Form Page Number",
	   c257 as "Account Form Page Line",
	   c256 as "Serial Number Each Month",
	   c255 as "Employee Number",
	   c151 as "Invoice Date_journal_accountings",
	   c254 as "Invoice Number",
	   c253 as "Dunning Level",
	   c252 as "Last Dunning Date",
	   c251 as "Journal Page",
	   c250 as "Journal Line",
	   c249 as "Cash Discount",
	   c248 as "Term Of Payment",
	   c247 as "Posting Text",
	   c246 as "Vehicle Reference",
	   c245 as "Vat Id Number",
	   c244 as "Account Statement Number",
	   c243 as "Account Statement Page",
	   c242 as "Vat Key",
	   c241 as "Days For Cash Discount",
	   c240 as "Day Of Actual Accounting",
	   c239 as "Skr51 Branch",
	   c238 as "Skr51 Make",
	   c237 as "Skr51 Cost Center",
	   c236 as "Skr51 Sales Channel",
	   c235 as "Skr51 Cost Unit",
	   c234 as "Previously Used Account No",
	   c233 as "Free Form Accounting Text",
	   c232 as "Free Form Document Text",
	   c231 as "Nom_Account_Is Profit Loss Account",
	   c187 as "Rechtseinheit",
	   c186 as "Betrieb",
	   c230 as "Bookkeep Date",
	   c229 as "Marke",
	   c228 as "Text",
	   '1' as "Mandant",
	   c227 as "Betrag",
	   c178 as "Konto_mit_Bezeichnung",
	   c226 as "Menge",
	   c225 as "Stellen Cost Center",
	   c224 as "KST",
	   c223 as "Stellen Sales Channel",
	   c222 as "Absatzkanal",
	   c221 as "Stellen Cost Unit",
	   c220 as "Kostenträger_mit_Null",
	   c219 as "Kostenträger",
	   c218 as "GuV_Bilanz",
	   c217 as "Susa",
	   c216 as "Acct Nr",
	   c215 as "Document Type In Journal",
	   c214 as "Document Type Description",
	   c213 as "FZG_ja_nein",
	   c212 as "Invoice Type_Invoices",
	   c211 as "Invoice Number_Invoices",
	   c210 as "Invoice Date_Invoices",
	   c209 as "Is Canceled_Invoices",
	   c208 as "Vehicle Number_Invoices",
	   c207 as "Invoice_Date_1",
	   c206 as "Internal Number_Vehicles",
	   c205 as "Vin_Vehicles",
	   c204 as "Dealer Vehicle Type",
	   c203 as "Fahrzeugtyp",
	   c202 as "Fahrzeugart",
	   c201 as "Make Number_Vehicles",
	   c200 as "Description_Make",
	   c199 as "Fabrikat",
	   c198 as "Model Code_Models",
	   c197 as "Model_Detail",
	   c196 as "Model",
	   c195 as "Owner Number_Vehicles",
	   c194 as "Holder Number_Vehicles",
	   c193 as "First Name_Owner_Customer",
	   c192 as "Family Name_Owner_Customer",
	   c191 as "First Name_Holder_Customer",
	   c190 as "Family Name_Holder_Customer",
	   c189 as "Kunde",
	   c188 as "Sales_Channel_FZG",
	   c187 as "Hauptbetrieb",
	   c186 as "Standort_alt",
	   c185 as "Salesman Number_Vehicles",
	   c184 as "Employee Number_Employees_Salesman",
	   c183 as "Name_Employees_Salesman",
	   c182 as "Verkäufer",
	   c181 as "Standort_FZG_Verkauf",
	   c180 as "Standort",
	   c179 as "FZG",
	   c178 as "Konto",
	   c177 as "Body Paint Code",
	   c176 as "Body Paint Description",
	   c176 as "Farbe",
	   c175 as "Erlös FZG",
	   c174 as "Nachlass",
	   c173 as "VAK FZG",
	   c172 as "Erlös Zulass.",
	   c171 as "VAK Zulass.",
	   c170 as "Erlös Gar.",
	   c169 as "VAK Gar.",
	   c168 as "Erlös Nachr./Aufb.",
	   c167 as "VAK Nachr./Aufb.",
	   c166 as "VAK Fertigm.",
	   c165 as "Erlös Überf.",
	   c164 as "VAK Überf.",
	   c163 as "Erlös Sonst._8900",
	   c162 as "Erlös Sonst.",
	   c161 as "VAK Sonst.",
	   c160 as "VAK intern",
	   c159 as "Erlös Prov.",
	   c158 as "Boni/VK-Hilfen",
	   c157 as "Erlös ges.",
	   c156 as "VAK ges.",
	   1 as "Menge_1",
	   c150 as "Vehicle Reference_berechnet",
	   c155 as "var. Kosten",
	   c154 as "Dealer Vehicle Type_Vehicles",
	   c153 as "Dealer Vehicle Number_Vehicles",
	   c152 as c142,
	   MAX(c208) OVER (partition by c150) as c143,
	   (CASE WHEN ((MAX(c207) OVER (partition by c150)) IS NULL) THEN ((c151)) ELSE ((MAX(c207) OVER (partition by c150))) END) as c144,
	   (CASE WHEN ((SUM(c213) OVER (partition by c150)) > 0) THEN ('FZG-Geschäft') ELSE ('Buchungen ohne FZG-Geschäft') END) as c145
from 
(select (left(T1."vehicle_reference",7)) + '_' + (od_right(T1."vehicle_reference",17)) as c150,
	   (cdate((nconvert((left(T1."invoice_date",4)) + (substring(T1."invoice_date", 6, 2)) + (substring(T1."invoice_date", 9, 2)))))) as c151,
	   T2."in_buy_invoice_no_date" as c152,
	   T8."dealer_vehicle_number" as c153,
	   T8."dealer_vehicle_type" as c154,
	   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 c155,
	   (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 c156,
	   (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 c157,
	   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 c158,
	   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 c159,
	   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 c160,
	   (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 c161,
	   (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 c162,
	   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 c163,
	   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 c164,
	   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 c165,
	   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 c166,
	   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 c167,
	   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 c168,
	   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 c169,
	   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 c170,
	   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 c171,
	   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 c172,
	   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 c173,
	   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 c174,
	   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 c175,
	   T8."body_paint_description" as c176,
	   T8."body_paint_code" as c177,
	   ((left(((convert(varchar(50), T1."nominal_account_number"))))) + ' - ' + T4."account_description") as c178,
	   (left(((left(T1."vehicle_reference",7)) + '_' + (od_right(T1."vehicle_reference",17))),7)) + ' / ' + ((left(((convert(varchar(50), T8."internal_number")))))) + ' - ' + T10."description" + ' - ' + (((left(((convert(varchar(50), T8."holder_number"))))) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ('') ELSE (T3."first_name") END) + ' ' + T3."family_name")) as c179,
	   CASE WHEN (T2."out_subsidiary" IS NULL) THEN ((('0' + ((convert(varchar(50), T1."skr51_branch")))))) ELSE ('0' + ((convert(varchar(50), T2."out_subsidiary")))) END as c180,
	   T2."out_subsidiary" as c181,
	   (left(((convert(varchar(50), T8."salesman_number"))))) + ' - ' + T12."name" as c182,
	   T12."name" as c183,
	   T12."employee_number" as c184,
	   T8."salesman_number" as c185,
	   ('0' + ((convert(varchar(50), T1."skr51_branch")))) as c186,
	   T1."subsidiary_to_company_ref" as c187,
	   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 c188,
	   ((left(((convert(varchar(50), T8."holder_number"))))) + ' - ' + (CASE WHEN (T3."first_name" IS NULL) THEN ('') ELSE (T3."first_name") END) + ' ' + T3."family_name") as c189,
	   T3."family_name" as c190,
	   CASE WHEN (T3."first_name" IS NULL) THEN ('') ELSE (T3."first_name") END as c191,
	   T11."family_name" as c192,
	   T11."first_name" as c193,
	   T8."holder_number" as c194,
	   T8."owner_number" as c195,
	   (left((ucase(T10."description")),3)) as c196,
	   T10."description" as c197,
	   T10."model_code" as c198,
	   CASE WHEN (((rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + T5."skr51_make_description") = '3 - Husqvarna') THEN ('Husqvarna') ELSE (T9."description") END as c199,
	   T9."description" as c200,
	   T8."make_number" as c201,
	   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 c202,
	   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 c203,
	   (left(T1."vehicle_reference",1)) as c204,
	   T8."vin" as c205,
	   T8."internal_number" as c206,
	   CASE WHEN ((CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8510)) THEN (1) ELSE (0) END) = 1) THEN (((cdate((nconvert((left(T1."invoice_date",4)) + (substring(T1."invoice_date", 6, 2)) + (substring(T1."invoice_date", 9, 2)))))))) ELSE null END as c207,
	   T7."vehicle_number" as c208,
	   T7."is_canceled" as c209,
	   T7."invoice_date" as c210,
	   T7."invoice_number" as c211,
	   T7."invoice_type" as c212,
	   CASE WHEN (T1."nominal_account_number" IN (8000,8010,8100,8110,8510)) THEN (1) ELSE (0) END as c213,
	   T6."document_type_description" as c214,
	   T6."document_type_in_journal" as c215,
	   T1."nominal_account_number" as c216,
	   (left(((convert(varchar(50), T1."nominal_account_number"))))) as c217,
	   CASE WHEN (T4."is_profit_loss_account" = 'J') THEN ('2') ELSE ('1') END as c218,
	   CASE WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49) THEN (((rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + (rtrim(((convert(varchar(50), T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name"))) ELSE ((rtrim(((convert(varchar(50), T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name")) END as c219,
	   (rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + (rtrim(((convert(varchar(50), T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name") as c220,
	   (len(((convert(varchar(50), T1."skr51_cost_unit"))) + 'Z') - 1) as c221,
	   (rtrim(((convert(varchar(50), T1."skr51_sales_channel"))))) + ' - ' + T5."skr51_sales_channel_name" as c222,
	   (len(((convert(varchar(50), T1."skr51_sales_channel"))) + 'Z') - 1) as c223,
	   (rtrim(((convert(varchar(50), T1."skr51_cost_center"))))) + ' - ' + T5."skr51_cost_center_name" as c224,
	   (len(((convert(varchar(50), T1."skr51_cost_center"))) + 'Z') - 1) as c225,
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100 * -1) ELSE (T1."posted_count" / 100) END as c226,
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END as c227,
	   CASE WHEN ((day((getdate()) - (convert(datetime, T1."accounting_date")))) <= 360) THEN (T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + (rtrim(((convert(varchar(50), T1."employee_number")))))) ELSE ('Buchungen älter 360 Tage') END as c228,
	   (rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + T5."skr51_make_description" as c229,
	   T1."accounting_date" as c230,
	   T4."is_profit_loss_account" as c231,
	   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 c232,
	   T3."zip_code" as c233,
	   T1."previously_used_account_no" as c234,
	   T1."skr51_cost_unit" as c235,
	   T1."skr51_sales_channel" as c236,
	   T1."skr51_cost_center" as c237,
	   T1."skr51_make" as c238,
	   T1."skr51_branch" as c239,
	   T1."day_of_actual_accounting" as c240,
	   T1."days_for_cash_discount" as c241,
	   T1."vat_key" as c242,
	   T1."account_statement_page" as c243,
	   T1."account_statement_number" as c244,
	   T1."vat_id_number" as c245,
	   T1."vehicle_reference" as c246,
	   T1."posting_text" as c247,
	   T1."term_of_payment" as c248,
	   T1."cash_discount" as c249,
	   T1."journal_line" as c250,
	   T1."journal_page" as c251,
	   T1."last_dunning_date" as c252,
	   T1."dunning_level" as c253,
	   T1."invoice_number" as c254,
	   T1."employee_number" as c255,
	   T1."serial_number_each_month" as c256,
	   T1."account_form_page_line" as c257,
	   T1."account_form_page_number" as c258,
	   T1."contra_account_text" as c259,
	   T1."nominal_contra_account" as c260,
	   T1."customer_contra_account" as c261,
	   T1."branch_number" as c262,
	   T1."posted_count" as c263,
	   T1."debit_or_credit" as c264,
	   T1."posted_value" as c265,
	   T1."document_date" as c266,
	   T1."clearing_number" as c267,
	   T1."is_balanced" as c268,
	   T1."position_in_document" as c269,
	   T1."document_number" as c270,
	   T1."document_type" as c271
from "nominal_accounts" T4,
	(((((((((("journal_accountings" T1 left outer join "vehicles" T8 on (od_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