C11/System/LOCOSOFT/IQD/Serv_Teile/Aftersales_Rechnungen_neu.sql

select c254 as "Invoice Type",
	   c253 as "Type_Invoice_Types",
	   c252 as "Description_Invoice_Types",
	   c251 as "Invoice Number",
	   c250 as "Subsidiary",
	   c249 as "Paying Customer",
	   c248 as "Order Customer",
	   c247 as "Invoice Date",
	   c246 as "Service Date",
	   c245 as "Is Canceled",
	   c244 as "Cancelation Number",
	   c243 as "Cancelation Date",
	   c242 as "Cancelation Employee",
	   c241 as "Is Own Vehicle",
	   c240 as "Is Credit",
	   c239 as "Credit Invoice Type",
	   c238 as "Credit Invoice Number",
	   c237 as "Odometer Reading",
	   c236 as "Creating Employee",
	   c235 as "Internal Cost Account",
	   c234 as "Vehicle Number",
	   c233 as "Full Vat Basevalue",
	   c232 as "Full Vat Percentage",
	   c231 as "Full Vat Value",
	   c230 as "Reduced Vat Basevalue",
	   c229 as "Reduced Vat Percentage",
	   c228 as "Reduced Vat Value",
	   c227 as "Used Part Vat Value",
	   c226 as "Job Amount Net",
	   c225 as "Job Amount Gross",
	   c224 as "Job Rebate",
	   c223 as "Part Amount Net",
	   c222 as "Part Amount Gross",
	   c221 as "Part Rebate",
	   c220 as "Part Disposal",
	   c219 as "Total Gross",
	   c218 as "Total Net",
	   c217 as "Inv_Type_Invoice_Number",
	   c172 as "Accounting Date",
	   c216 as "Document Type",
	   c215 as "Document Number",
	   c214 as "Position In Document",
	   c213 as "Customer Number",
	   c212 as "Nominal Account Number",
	   c211 as "Is Balanced",
	   c210 as "Clearing Number",
	   c209 as "Document Date",
	   c208 as "Posted Value",
	   c207 as "Debit Or Credit",
	   c206 as "Posted Count",
	   c205 as "Branch Number",
	   c204 as "Customer Contra Account",
	   c203 as "Nominal Contra Account",
	   c202 as "Contra Account Text",
	   c201 as "Account Form Page Number",
	   c200 as "Account Form Page Line",
	   c199 as "Serial Number Each Month",
	   c198 as "Employee Number",
	   c197 as "Invoice Date_falsch",
	   c196 as "Invoice Number",
	   c195 as "Dunning Level",
	   c194 as "Last Dunning Date",
	   c193 as "Journal Page",
	   c192 as "Journal Line",
	   c191 as "Cash Discount",
	   c190 as "Term Of Payment",
	   c189 as "Posting Text",
	   c188 as "Vehicle Reference",
	   c187 as "Vat Id Number",
	   c186 as "Account Statement Number",
	   c185 as "Account Statement Page",
	   c184 as "Vat Key",
	   c183 as "Days For Cash Discount",
	   c182 as "Day Of Actual Accounting",
	   c181 as "Skr51 Branch",
	   c158 as "Skr51 Make",
	   c180 as "Skr51 Cost Center",
	   c179 as "Skr51 Sales Channel",
	   c178 as "Skr51 Cost Unit",
	   c177 as "Previously Used Account No",
	   c176 as "Free Form Accounting Text",
	   c175 as "Free Form Document Text",
	   c174 as "Hauptbetrieb",
	   c173 as "Standort",
	   c172 as "Invoice Date_accounting",
	   c171 as "Employee Number_Employees",
	   c170 as "Name_Employees",
	   c169 as "Mechanic Number_Employees",
	   c168 as "Salesman Number_Employees",
	   c167 as "Is Business Executive_Employees",
	   c166 as "Is Master Craftsman_Employees",
	   c165 as "Serviceberater_Rg_Steller",
	   c164 as "Customer Number_Customers_Suppliers",
	   c163 as "First Name_Customers_Suppliers",
	   c162 as "Family Name_Customers_Suppliers",
	   c161 as "Kunde",
	   c160 as "Betrag",
	   c159 as "Kostenstelle",
	   c158 as "Marke",
	   c157 as "Kundenart",
	   c156 as "KST_1_Stelle",
	   c155 as "Umsatzart",
	   c154 as "Auftragsart",
	   '' as "Geschäftsart",
	   c153 as "Make Number",
	   c152 as "Free Form Make Text",
	   c151 as "Model Code",
	   c150 as "Free Form Model Text",
	   c149 as "Description_Makes",
	   c148 as "Description_Models",
	   c149 as "Fabrikat",
	   c148 as "Model_Detail",
	   c147 as "Model",
	   (0) as "Umsatz Lohn",
	   (0) as "Umsatz FL",
	   (0) as "Einsatz FL",
	   (0) as "Umsatz Teile",
	   (0) as "Einsatz Teile",
	   (0) as "Umsatz MW",
	   (0) as "Einsatz MW",
	   (0) as "Umsatz Sonst.",
	   (0) as "Einsatz Sonst.",
	   1 as "DG_1",
	   COUNT(c254) OVER (partition by c141) as "DG_2",
	   1 / (COUNT(c254) OVER (partition by c141)) as "DG",
	   c146 as "Fahrzeug",
	   c141 as "Order Number",
	   c145 as "Invoice_Date_Uhrzeit",
	   c144 as "Order Number_Rg_Ausg",
	   '' as "Konto Nr",
	   '' as "Konto Bezeichnung",
	   '' as "Ebene1",
	   '' as "Ebene2",
	   '' as "Ebene3",
	   '' as "Ebene4",
	   c143 as "Serviceberater_order_pos",
	   c142 as "Serviceberater"
from 
(select (CASE WHEN ((CASE WHEN (T1."invoice_type" = 5) THEN (((substring((convert(varchar(50), T5."employee_number")), 1, 4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) IS NOT NULL) THEN (((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9))) + ' - ' + (CASE WHEN (T1."invoice_type" = 5) THEN (((substring((convert(varchar(50), T5."employee_number")), 1, 4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."family_name") ELSE ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) ELSE (((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9))) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."family_name") ELSE ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) END) as c141,
	   CASE WHEN (T1."invoice_type" = 5) THEN (((substring((convert(varchar(50), T5."employee_number")), 1, 4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END as c142,
	   T10."Serviceberater" as c143,
	   CASE WHEN ((day((getdate()) - ((convert(datetime, T1."invoice_date"))))) <= 4) THEN ((CASE WHEN ((CASE WHEN (T1."invoice_type" = 5) THEN (((substring((convert(varchar(50), T5."employee_number")), 1, 4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) IS NOT NULL) THEN (((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9))) + ' - ' + (CASE WHEN (T1."invoice_type" = 5) THEN (((substring((convert(varchar(50), T5."employee_number")), 1, 4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."family_name") ELSE ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) ELSE (((substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9))) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."family_name") ELSE ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) END)) ELSE null END as c144,
	   (convert(datetime, T1."invoice_date")) as c145,
	   (substring((substring((convert(varchar(50), T1."vehicle_number")), 1, 5)) + ' - ' + T9."description", 1, 100)) as c146,
	   (substring((upper(T9."description")), 1, 3)) as c147,
	   T9."description" as c148,
	   T8."description" as c149,
	   T7."free_form_model_text" as c150,
	   T7."model_code" as c151,
	   T7."free_form_make_text" as c152,
	   T7."make_number" as c153,
	   CASE WHEN (T1."invoice_type" = 5) THEN ('Teile') ELSE ('Service') END as c154,
	   CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T4."customer_number" IN (1000000,1000001,1000002,1000003,1000010))) THEN ('intern') ELSE ('extern') END as c155,
	   (substring(((rtrim((convert(varchar(50), T3."skr51_cost_center")))) + ' - ' + T6."skr51_cost_center_name"), 1, 1)) as c156,
	   (rtrim((convert(varchar(50), T3."skr51_sales_channel")))) + ' - ' + T6."skr51_sales_channel_name" as c157,
	   T3."skr51_make" as c158,
	   (rtrim((convert(varchar(50), T3."skr51_cost_center")))) + ' - ' + T6."skr51_cost_center_name" as c159,
	   CASE WHEN (T3."debit_or_credit" = 'H') THEN (((T3."posted_value")) / 100 * -1) ELSE (((T3."posted_value")) / 100) END as c160,
	   CASE WHEN (T4."first_name" IS NULL) THEN ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."family_name") ELSE ((substring((convert(varchar(50), T4."customer_number")), 1, 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END as c161,
	   T4."family_name" as c162,
	   T4."first_name" as c163,
	   T4."customer_number" as c164,
	   (substring((convert(varchar(50), T5."employee_number")), 1, 4)) + ' - ' + T5."name" as c165,
	   T5."is_master_craftsman" as c166,
	   T5."is_business_executive" as c167,
	   T5."salesman_number" as c168,
	   T5."mechanic_number" as c169,
	   T5."name" as c170,
	   T5."employee_number" as c171,
	   T3."accounting_date" as c172,
	   '0' + (convert(varchar(50), T3."branch_number")) as c173,
	   T3."subsidiary_to_company_ref" as c174,
	   CASE WHEN (T3."skr51_cost_unit" BETWEEN 1 AND 49) THEN ('Neuwagen') WHEN (T3."skr51_cost_unit" BETWEEN 50 AND 59) THEN ('Gebrauchtwagen') WHEN (T3."skr51_cost_unit" BETWEEN 60 AND 69) THEN ('Teile & Zubehör') WHEN (T3."skr51_cost_unit" BETWEEN 70 AND 79) THEN ('Service') WHEN (T3."skr51_cost_unit" = 0) THEN ('Ohne Kostenträger') ELSE null END as c175,
	   T4."zip_code" as c176,
	   T3."previously_used_account_no" as c177,
	   T3."skr51_cost_unit" as c178,
	   T3."skr51_sales_channel" as c179,
	   T3."skr51_cost_center" as c180,
	   T3."skr51_branch" as c181,
	   T3."day_of_actual_accounting" as c182,
	   T3."days_for_cash_discount" as c183,
	   T3."vat_key" as c184,
	   T3."account_statement_page" as c185,
	   T3."account_statement_number" as c186,
	   T3."vat_id_number" as c187,
	   T3."vehicle_reference" as c188,
	   T3."posting_text" as c189,
	   T3."term_of_payment" as c190,
	   T3."cash_discount" as c191,
	   T3."journal_line" as c192,
	   T3."journal_page" as c193,
	   T3."last_dunning_date" as c194,
	   T3."dunning_level" as c195,
	   T3."invoice_number" as c196,
	   T3."invoice_date" as c197,
	   T3."employee_number" as c198,
	   T3."serial_number_each_month" as c199,
	   T3."account_form_page_line" as c200,
	   T3."account_form_page_number" as c201,
	   CASE WHEN (T3."nominal_account_number" = 5701) THEN ((rtrim((convert(varchar(50), T3."nominal_account_number")))) + '_' + (rtrim((convert(varchar(50), T3."skr51_cost_center"))))) ELSE ((rtrim((convert(varchar(50), T3."nominal_account_number"))))) END as c202,
	   T3."nominal_contra_account" as c203,
	   T3."customer_contra_account" as c204,
	   T3."branch_number" as c205,
	   T3."posted_count" as c206,
	   T3."debit_or_credit" as c207,
	   (T3."posted_value") as c208,
	   T3."document_date" as c209,
	   T3."clearing_number" as c210,
	   T3."is_balanced" as c211,
	   T3."nominal_account_number" as c212,
	   T3."customer_number" as c213,
	   T3."position_in_document" as c214,
	   T3."document_number" as c215,
	   T3."document_type" as c216,
	   (substring((convert(varchar(50), T1."invoice_type")), 1, 1)) + (substring((convert(varchar(50), T1."invoice_number")), 1, 9)) as c217,
	   T1."total_net" as c218,
	   T1."total_gross" as c219,
	   T1."part_disposal" as c220,
	   T1."part_rebate" as c221,
	   T1."part_amount_gross" as c222,
	   T1."part_amount_net" as c223,
	   T1."job_rebate" as c224,
	   T1."job_amount_gross" as c225,
	   T1."job_amount_net" as c226,
	   T1."used_part_vat_value" as c227,
	   T1."reduced_vat_value" as c228,
	   T1."reduced_vat_percentage" as c229,
	   T1."reduced_vat_basevalue" as c230,
	   T1."full_vat_value" as c231,
	   T1."full_vat_percentage" as c232,
	   T1."full_vat_basevalue" as c233,
	   T1."vehicle_number" as c234,
	   T1."internal_cost_account" as c235,
	   T1."creating_employee" as c236,
	   T1."odometer_reading" as c237,
	   T1."credit_invoice_number" as c238,
	   T1."credit_invoice_type" as c239,
	   T1."is_credit" as c240,
	   T1."is_own_vehicle" as c241,
	   T1."cancelation_employee" as c242,
	   T1."cancelation_date" as c243,
	   T1."cancelation_number" as c244,
	   T1."is_canceled" as c245,
	   T1."service_date" as c246,
	   T1."invoice_date" as c247,
	   T1."order_customer" as c248,
	   T1."paying_customer" as c249,
	   T1."subsidiary" as c250,
	   T1."invoice_number" as c251,
	   T2."description" as c252,
	   T2."type" as c253,
	   T1."invoice_type" as c254
from ((((((((("dbo"."invoices" T1 left outer join "dbo"."invoice_types" T2 on T2."type" = T1."invoice_type") left outer join "dbo"."journal_accountings" T3 on T3."invoice_number" = T1."invtype_invnr") left outer join "dbo"."customers_suppliers" T4 on T4."customer_number" = T1."paying_customer") left outer join "dbo"."employees" T5 on T1."creating_employee" = T5."employee_number") left outer join "dbo"."accounts_characteristics" T6 on (((((T6."skr51_branch" = T3."skr51_branch") and (T6."skr51_make" = T3."skr51_make")) and (T6."skr51_cost_center" = T3."skr51_cost_center")) and (T6."skr51_sales_channel" = T3."skr51_sales_channel")) and (T6."skr51_cost_unit" = T3."skr51_cost_unit")) and (T6."subsidiary_to_company_ref" = T3."subsidiary_to_company_ref")) left outer join "dbo"."vehicles" T7 on T1."vehicle_number" = T7."internal_number") left outer join "dbo"."makes" T8 on T7."make_number" = T8."make_number") left outer join "dbo"."models" T9 on (T7."make_number" = T9."make_number") and (T7."model_code" = T9."model_code")) left outer join "ims"."Serviceberater_Rechnung" T10 on T1."invtype_invnr" = T10."invtype_invnr")
where (((((T1."invoice_type" BETWEEN 2 AND 6) and ((substring((convert(varchar(50), T3."nominal_account_number")), 1, 1)) IN ('4','5','7','8'))) and (T1."invoice_date" >= convert(date, '2019-01-01'))) and (T1."is_canceled" <> 1)) and (((len((rtrim((convert(varchar(50), T3."nominal_account_number"))))))) = 4))
-- order by c141 asc,c215 asc,c214 asc,c217 asc
) D1