select c113 as "Accounting Date", c155 as "Document Type", c154 as "Document Number", c153 as "Position In Document", c110 as "Nominal Account Number", c152 as "Customer Number", c151 as "Is Balanced", c150 as "Clearing Number", c149 as "Document Date", c148 as "Posted Value", c147 as "Debit Or Credit", c146 as "Posted Count", c145 as "Branch Number", c144 as "Customer Contra Account", c143 as "Nominal Contra Account", c142 as "Contra Account Text", c141 as "Account Form Page Number", c140 as "Account Form Page Line", c139 as "Serial Number Each Month", c138 as "Employee Number", c137 as "Invoice Date", c136 as "Invoice Number", c135 as "Dunning Level", c134 as "Last Dunning Date", c133 as "Journal Page", c132 as "Journal Line", c131 as "Cash Discount", c130 as "Term Of Payment", c129 as "Posting Text", c128 as "Vehicle Reference", c127 as "Vat Id Number", c126 as "Account Statement Number", c125 as "Account Statement Page", c124 as "Vat Key", c123 as "Days For Cash Discount", c122 as "Day Of Actual Accounting", c121 as "Skr51 Branch", c120 as "Skr51 Make", c119 as "Skr51 Cost Center", c118 as "Skr51 Sales Channel", c117 as "Skr51 Cost Unit", c116 as "Previously Used Account No", c115 as "Free Form Accounting Text", c114 as "Free Form Document Text", '1' as "Rechtseinheit", c85 as "Betrieb", c113 as "Bookkeep Date", c112 as "Text", '1' as "Mandant", c97 as "Betrag", c111 as "Menge", c110 as "Acct Nr", c83 as "Customer Number_suctomer_supplier", c109 as "Is Supplier", c108 as "First Name", c107 as "Family Name", c106 as "Document Type In Journal", c105 as "Document Type Description", SUM(c97) OVER (partition by c83) as "Debitorsaldo_", c104 as "Invoice Type_Invoices", c103 as "Invoice Number_Invoices", c102 as "Subsidiary_Invoices", c101 as "Invoice Date_Invoices", c100 as "Hauptbetrieb", c99 as "Standort_ori", c98 as "Sel Name", c97 as "OP-Saldo", c96 as "Kostenstelle", c95 as "Tage", c94 as "Staffel", c93 as "Kunde", c92 as "Beleg", c91 as "Employee Number_Employees_journal_accountings", c90 as "Name_Employees_journal_accountings", c89 as "Anzahl_Stellen_Invoice_Number", c88 as "Forderungsart_aus_erste_Stelle_Inv_Number", c87 as "Forderungsart", c86 as "Standort_aus_2_Stelle_Invoice_Number", c85 as "Standort", c84 as "Standort_ims_8520" from (select T3."customer_number" as c83, T6."Standort" as c84, ('0' + ((convert(varchar(50), T1."subsidiary_to_company_ref")))) as c85, CASE WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((substring(T1."invoice_number", 2, 1)) BETWEEN '1' AND '4')) THEN ('0' + (substring(T1."invoice_number", 2, 1))) ELSE null END as c86, CASE WHEN (T1."dunning_level" = 'Gesperrt') THEN ('Versicherung') ELSE ((CASE WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('2','3','4'))) THEN ('Service') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('5'))) THEN ('Teile') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('6'))) THEN ('Garantie') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('7'))) THEN ('Neuwagen') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('8'))) THEN ('Gebrauchtwagen') WHEN (T1."document_type" = 'R') THEN ('Rent') ELSE ('nicht zuzuordnen') END)) END as c87, CASE WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('2','3','4'))) THEN ('Service') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('5'))) THEN ('Teile') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('6'))) THEN ('Garantie') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('7'))) THEN ('Neuwagen') WHEN ((((len(T1."invoice_number" + 'Z') - 1)) = 7) and ((left(T1."invoice_number",1)) IN ('8'))) THEN ('Gebrauchtwagen') WHEN (T1."document_type" = 'R') THEN ('Rent') ELSE ('nicht zuzuordnen') END as c88, (len(T1."invoice_number" + 'Z') - 1) as c89, T2."name" as c90, T2."employee_number" as c91, CASE WHEN (T1."posting_text" IS NOT NULL) THEN ((substring((convert(varchar(50), ((T1."document_number")))), 1, 7)) + ' - ' + T1."posting_text" + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END as c92, CASE WHEN (T3."first_name" <> ' ') THEN ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."first_name" + ' ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."family_name") END as c93, CASE WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) > 84) THEN ('> 12 Wochen') ELSE null END as c94, (day((getdate()) - (convert(datetime, T1."accounting_date")))) as c95, CASE WHEN (T5."invoice_type" IN (2,4,6,3)) THEN ('3') WHEN (T5."invoice_type" IN (5)) THEN ('6') WHEN (T5."invoice_type" IN (7)) THEN ('1') WHEN (T5."invoice_type" IN (8)) THEN ('2') WHEN (T4."document_type_in_journal" = 'R') THEN ('7') ELSE null END as c96, (CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) as c97, (left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name" as c98, CASE WHEN (T5."subsidiary" IS NOT NULL) THEN ('0' + (left(((convert(varchar(50), T5."subsidiary")))))) ELSE ('nicht zuzuordnen') END as c99, CASE WHEN ((('0' + ((convert(varchar(50), T1."subsidiary_to_company_ref"))))) IN ('015 ')) THEN ('15') ELSE ('1') END as c100, T5."invoice_date" as c101, T5."subsidiary" as c102, T5."invoice_number" as c103, T5."invoice_type" as c104, T4."document_type_description" as c105, T4."document_type_in_journal" as c106, T3."family_name" as c107, T3."first_name" as c108, T3."is_supplier" as c109, T1."nominal_account_number" as c110, CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100 * -1) ELSE (T1."posted_count" / 100) END as c111, ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name")) as c112, T1."accounting_date" as c113, T1."free_form_document_text" as c114, T1."free_form_accounting_text" as c115, T1."previously_used_account_no" as c116, T1."skr51_cost_unit" as c117, T1."skr51_sales_channel" as c118, T1."skr51_cost_center" as c119, T1."skr51_make" as c120, T1."skr51_branch" as c121, T1."day_of_actual_accounting" as c122, T1."days_for_cash_discount" as c123, T1."vat_key" as c124, T1."account_statement_page" as c125, T1."account_statement_number" as c126, T1."vat_id_number" as c127, T1."vehicle_reference" as c128, T1."posting_text" as c129, T1."term_of_payment" as c130, T1."cash_discount" as c131, T1."journal_line" as c132, T1."journal_page" as c133, T1."last_dunning_date" as c134, T1."dunning_level" as c135, T1."invoice_number" as c136, T1."invoice_date" as c137, T1."employee_number" as c138, T1."serial_number_each_month" as c139, T1."account_form_page_line" as c140, T1."account_form_page_number" as c141, T1."contra_account_text" as c142, T1."nominal_contra_account" as c143, T1."customer_contra_account" as c144, T1."branch_number" as c145, T1."posted_count" as c146, T1."debit_or_credit" as c147, T1."posted_value" as c148, T1."document_date" as c149, T1."clearing_number" as c150, T1."is_balanced" as c151, T1."customer_number" as c152, T1."position_in_document" as c153, (T1."document_number") as c154, T1."document_type" as c155 from "ims"."OP_aus_LOC_Belege_8520" T6, (((("journal_accountings" T1 left outer join "LOCOSOFT"."dbo"."employees" T2 on T1."employee_number" = T2."employee_number") left outer join "LOCOSOFT"."dbo"."customers_suppliers" T3 on T3."customer_number" = T1."customer_number") left outer join "document_types" T4 on T4."document_type_in_journal" = T1."document_type") left outer join "invoices" T5 on T5."invtype_invnr" = T1."invoice_number") where ((T1."invoice_number" = T6."invoice_number") and (T1."customer_number" = T6."customer_contra_account")) and (((((T1."nominal_account_number" IN (1400,1409,1451,1408,1525)) and (T1."clearing_number" = 0)) and (T1."customer_number" IS NOT NULL)) and (not T4."document_type_in_journal" IN ('A','E','U','V','L'))) and (T3."customer_number" IS NOT NULL)) ) D1 -- order by "Customer Number" asc,"Document Type In Journal" asc,"Nominal Account Number" asc