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

select c253 as "Invoice Type",
	   c252 as "Type_Invoice_Types",
	   c251 as "Description_Invoice_Types",
	   c250 as "Invoice Number",
	   c249 as "Subsidiary",
	   c248 as "Paying Customer",
	   c247 as "Order Customer",
	   c246 as "Invoice Date",
	   c245 as "Service Date",
	   c244 as "Is Canceled",
	   c243 as "Cancelation Number",
	   c242 as "Cancelation Date",
	   c241 as "Cancelation Employee",
	   c240 as "Is Own Vehicle",
	   c239 as "Is Credit",
	   c238 as "Credit Invoice Type",
	   c237 as "Credit Invoice Number",
	   c236 as "Odometer Reading",
	   c235 as "Creating Employee",
	   c234 as "Internal Cost Account",
	   c233 as "Vehicle Number",
	   c232 as "Full Vat Basevalue",
	   c231 as "Full Vat Percentage",
	   c230 as "Full Vat Value",
	   c229 as "Reduced Vat Basevalue",
	   c228 as "Reduced Vat Percentage",
	   c227 as "Reduced Vat Value",
	   c226 as "Used Part Vat Value",
	   c225 as "Job Amount Net",
	   c224 as "Job Amount Gross",
	   c223 as "Job Rebate",
	   c222 as "Part Amount Net",
	   c221 as "Part Amount Gross",
	   c220 as "Part Rebate",
	   c219 as "Part Disposal",
	   c218 as "Total Gross",
	   c217 as "Total Net",
	   c216 as "Inv_Type_Invoice_Number",
	   c172 as "Accounting Date",
	   c215 as "Document Type",
	   c214 as "Document Number",
	   c213 as "Position In Document",
	   c212 as "Customer Number",
	   c211 as "Nominal Account Number",
	   c210 as "Is Balanced",
	   c209 as "Clearing Number",
	   c208 as "Document Date",
	   c207 as "Posted Value",
	   c206 as "Debit Or Credit",
	   c205 as "Posted Count",
	   c204 as "Branch Number",
	   c203 as "Customer Contra Account",
	   c202 as "Nominal Contra Account",
	   c201 as "Contra Account Text",
	   c200 as "Account Form Page Number",
	   c199 as "Account Form Page Line",
	   c198 as "Serial Number Each Month",
	   c197 as "Employee Number",
	   c196 as "Invoice Date_falsch",
	   c195 as "Invoice Number",
	   c194 as "Dunning Level",
	   c193 as "Last Dunning Date",
	   c192 as "Journal Page",
	   c191 as "Journal Line",
	   c190 as "Cash Discount",
	   c189 as "Term Of Payment",
	   c188 as "Posting Text",
	   c187 as "Vehicle Reference",
	   c186 as "Vat Id Number",
	   c185 as "Account Statement Number",
	   c184 as "Account Statement Page",
	   c183 as "Vat Key",
	   c182 as "Days For Cash Discount",
	   c181 as "Day Of Actual Accounting",
	   c180 as "Skr51 Branch",
	   c158 as "Skr51 Make",
	   c179 as "Skr51 Cost Center",
	   c178 as "Skr51 Sales Channel",
	   c177 as "Skr51 Cost Unit",
	   c176 as "Previously Used Account No",
	   c175 as "Free Form Accounting Text",
	   c174 as "Free Form Document Text",
	   '1' 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(c253) OVER (partition by c141) as "DG_2",
	   1 / (COUNT(c253) 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 (((left(((convert(varchar(50), T5."employee_number"))))) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) IS NOT NULL) THEN (((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number")))))) + ' - ' + (CASE WHEN (T1."invoice_type" = 5) THEN (((left(((convert(varchar(50), T5."employee_number"))))) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."family_name") ELSE ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) ELSE (((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number")))))) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."family_name") ELSE ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) END) as c141,
	   CASE WHEN (T1."invoice_type" = 5) THEN (((left(((convert(varchar(50), T5."employee_number"))))) + ' - ' + 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 (((left(((convert(varchar(50), T5."employee_number"))))) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) IS NOT NULL) THEN (((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number")))))) + ' - ' + (CASE WHEN (T1."invoice_type" = 5) THEN (((left(((convert(varchar(50), T5."employee_number"))))) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."family_name") ELSE ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) ELSE (((left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number")))))) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."family_name") ELSE ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) END)) ELSE null END as c144,
	   (convert(datetime, T1."invoice_date")) as c145,
	   (left((left(((convert(varchar(50), T1."vehicle_number"))))) + ' - ' + T9."description",100)) as c146,
	   (left((ucase(T9."description")),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,
	   (left(((rtrim(((convert(varchar(50), T3."skr51_cost_center"))))) + ' - ' + T6."skr51_cost_center_name"))) 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 (((convert(float, T3."posted_value"))) / 100 * -1) ELSE (((convert(float, T3."posted_value"))) / 100) END as c160,
	   CASE WHEN (T4."first_name" IS NULL) THEN ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."family_name") ELSE ((left(((convert(varchar(50), T4."customer_number"))))) + ' - ' + T4."first_name" + ' ' + T4."family_name") END as c161,
	   T4."family_name" as c162,
	   T4."first_name" as c163,
	   T4."customer_number" as c164,
	   (left(((convert(varchar(50), T5."employee_number"))))) + ' - ' + 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,
	   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 c174,
	   T4."zip_code" as c175,
	   T3."previously_used_account_no" as c176,
	   T3."skr51_cost_unit" as c177,
	   T3."skr51_sales_channel" as c178,
	   T3."skr51_cost_center" as c179,
	   T3."skr51_branch" as c180,
	   T3."day_of_actual_accounting" as c181,
	   T3."days_for_cash_discount" as c182,
	   T3."vat_key" as c183,
	   T3."account_statement_page" as c184,
	   T3."account_statement_number" as c185,
	   T3."vat_id_number" as c186,
	   T3."vehicle_reference" as c187,
	   T3."posting_text" as c188,
	   T3."term_of_payment" as c189,
	   T3."cash_discount" as c190,
	   T3."journal_line" as c191,
	   T3."journal_page" as c192,
	   T3."last_dunning_date" as c193,
	   T3."dunning_level" as c194,
	   T3."invoice_number" as c195,
	   T3."invoice_date" as c196,
	   T3."employee_number" as c197,
	   T3."serial_number_each_month" as c198,
	   T3."account_form_page_line" as c199,
	   T3."account_form_page_number" as c200,
	   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 c201,
	   T3."nominal_contra_account" as c202,
	   T3."customer_contra_account" as c203,
	   T3."branch_number" as c204,
	   T3."posted_count" as c205,
	   T3."debit_or_credit" as c206,
	   (convert(float, T3."posted_value")) as c207,
	   T3."document_date" as c208,
	   T3."clearing_number" as c209,
	   T3."is_balanced" as c210,
	   T3."nominal_account_number" as c211,
	   T3."customer_number" as c212,
	   T3."position_in_document" as c213,
	   T3."document_number" as c214,
	   T3."document_type" as c215,
	   (left(((convert(varchar(50), T1."invoice_type"))))) + (left(((convert(varchar(50), T1."invoice_number"))))) as c216,
	   T1."total_net" as c217,
	   T1."total_gross" as c218,
	   T1."part_disposal" as c219,
	   T1."part_rebate" as c220,
	   T1."part_amount_gross" as c221,
	   T1."part_amount_net" as c222,
	   T1."job_rebate" as c223,
	   T1."job_amount_gross" as c224,
	   T1."job_amount_net" as c225,
	   T1."used_part_vat_value" as c226,
	   T1."reduced_vat_value" as c227,
	   T1."reduced_vat_percentage" as c228,
	   T1."reduced_vat_basevalue" as c229,
	   T1."full_vat_value" as c230,
	   T1."full_vat_percentage" as c231,
	   T1."full_vat_basevalue" as c232,
	   T1."vehicle_number" as c233,
	   T1."internal_cost_account" as c234,
	   T1."creating_employee" as c235,
	   T1."odometer_reading" as c236,
	   T1."credit_invoice_number" as c237,
	   T1."credit_invoice_type" as c238,
	   T1."is_credit" as c239,
	   T1."is_own_vehicle" as c240,
	   T1."cancelation_employee" as c241,
	   T1."cancelation_date" as c242,
	   T1."cancelation_number" as c243,
	   T1."is_canceled" as c244,
	   T1."service_date" as c245,
	   T1."invoice_date" as c246,
	   T1."order_customer" as c247,
	   T1."paying_customer" as c248,
	   T1."subsidiary" as c249,
	   T1."invoice_number" as c250,
	   T2."description" as c251,
	   T2."type" as c252,
	   T1."invoice_type" as c253
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 ((left(((convert(varchar(50), T3."nominal_account_number"))))) 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"))))) + 'Z') - 1)) = 4))
-- order by c141 asc,c214 asc,c213 asc,c216 asc
) D1