C11/System/OPTIMA/IQD/Serv_Teile/offene_auftraege_csv.sql

select "Order Number_ori_2",
	   "Register Number",
	   "Status",
	   "State Key Date",
	   "Debit Account",
	   "Invoice Number",
	   "Workshop Model",
	   "State Code",
	   "Transact Date",
	   "Handler",
	   "Delivery Account",
	   "Department",
	   "Debet Department",
	   "Salesman_2",
	   "Debit Perm",
	   "Order Date",
	   "Delivery Date",
	   "Invoice Date_ori",
	   "Tax Code",
	   "Tax Perc",
	   "Pmt Term",
	   "Next Line Number",
	   "Duedate 1",
	   "Payment Text",
	   "Costs",
	   "Purch Tax",
	   "Orders Grossvalue",
	   "Tax Share",
	   "Discount Amount",
	   "Price Code",
	   "Misc Adds",
	   "Stock",
	   "Place Code",
	   "Internal Code",
	   "Customer Group_2",
	   "Small Accessories",
	   "Invoice Copy Code",
	   "Basis Number",
	   "Mileage",
	   "Prev Status",
	   "Sales Class Number",
	   "Invoice Disc Perc",
	   "Invoice Rounded",
	   "Invoice Charge",
	   "Sales Tax Free",
	   "Title",
	   "Name",
	   "Street Addr",
	   "Addr 2",
	   "Zipcode",
	   "Mail Addr",
	   "Discount Limit",
	   "Reference Number",
	   "Expected Order Tim",
	   "Bol Tax Share",
	   "Model Text",
	   "Workshop Pricecode",
	   "Split Counter",
	   "Arrival Time",
	   "Arrival Date",
	   "End Date",
	   "End Time",
	   "Fac Model Code S",
	   "Make Cd_3",
	   "Year Model",
	   "Transfer Make Cd",
	   "Chassis Number",
	   "Workshop Team",
	   "Commission Salesman",
	   "Ref Ident Inv Total",
	   "Ref Ident Sales Class",
	   "Use Parts Pre Picking",
	   "Ldc Order",
	   "Fhg Report",
	   "Actual Inv Date Time",
	   "Conv Flag",
	   "Unique Ident_2",
	   "Order Number_ori",
	   "Line Number",
	   "Order Linetype",
	   "Reduction Code",
	   "Reduction Amount",
	   "Mechanic Code",
	   "Salesman",
	   "Discount",
	   "Stdprice",
	   "Lines Net Value",
	   "Prod Code",
	   "Make Cd_2",
	   "Product Group",
	   "Prod Name",
	   "Order Quantity",
	   "Delivery Quantity",
	   "Line Costs",
	   "Repair Code",
	   "Repair Group_2",
	   "Repair Name",
	   "Used Time",
	   "Est Time",
	   "Inv Time",
	   "Used Time Int",
	   "Est Time Int",
	   "Inv Time Int",
	   "Make Time Unit",
	   "Unique Ident",
	   "Stat Code",
	   "Stat Specify",
	   "Department Type Id",
	   "Description_2",
	   "Seller Code_2",
	   "Sel Name",
	   "Sel Department_2",
	   "Sel First Name",
	   "Sel Family Name",
	   "Customer Group",
	   "Cust Group Specify",
	   "Seller Code",
	   "Sel Name_Monteur",
	   "Sel Department",
	   "Sel First Name_Monteur",
	   "Sel Family Name_Monteur",
	   "Repair Group",
	   "Make Cd",
	   12 as "AW_Faktor",
	   "Fabrikat_ori",
	   "Repair Grp Specify",
	   "Hauptbetrieb",
	   "Standort",
	   "Marke",
	   "Kostenstelle",
	   "Kundenart",
	   "Umsatzart",
	   "Geschäftsart",
	   "Sel Name" as "Serviceberater",
	   "Sel Name_Monteur" as "Monteur",
	   "Auftragsart",
	   "Auftragsstatus",
	   "Umsatz Teile Service",
	   "Umsatz Lohn",
	   "Umsatz Teile (nur Teile)" as "Umsatz Teile (nur Teile)",
	   "Status_1",
	   "Inv Time Int" as "verk. Stunden",
	   "Est Time Int" as "Soll-Stunden (Auftrag)",
	   "Used Time Int" as "benutzte Zeit (Auftrag)",
	   "Umsatz Sonstiges",
	   "verk. AW",
	   "Kunde",
	   "Soll AW",
	   "benutzte AW",
	   "Global Make Cd",
	   "Description",
	   "Model",
	   "Fahrzeug",
	   "Auftrag_Det_S",
	   "Auftrag_Det_T",
	   "Anzahl Tage",
	   "Order Number",
	   "Fabrikat",
	   1 as "DG_1",
	   COUNT("Status") OVER (partition by "Order Number_ori_2") as "Anzahl_Datensätze",
	   1 / (COUNT("Status") OVER (partition by "Order Number_ori_2")) as "DG",
	   "Order Date" as "Invoice Date",
	   "Anzahl Tage" as "Tage offen_1",
	   ("Anzahl Tage") / (COUNT("Status") OVER (partition by "Order Number_ori_2")) as "Tage offen",
	   "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort_ID",
	   "Standort_Name"
from 
(select T1."ORDER_NUMBER" as "Order Number_ori_2",
	   T12."Standort_Name" as "Standort_Name",
	   T12."Standort_ID" as "Standort_ID",
	   T12."Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   CASE WHEN (T12."Hauptbetrieb_ID" IS NULL) THEN ('1') ELSE (T12."Hauptbetrieb_ID") END as "Hauptbetrieb_ID",
	   (day((getdate()) - T1."ORDER_DATE")) as "Anzahl Tage",
	   T1."ORDER_DATE" as "Order Date",
	   CASE WHEN (T9."Fabrikat" IS NULL) THEN ('Fremd') ELSE (T9."Fabrikat") END as "Fabrikat",
	   (left((((T1."ORDER_NUMBER"))),7)) + ' - ' + (rtrim(((rtrim(T1."DEBIT_ACCOUNT")) + ' - ' + T1."NAME"))) + ' - ' + T1."REGISTER_NUMBER" as "Order Number",
	   (left((((T1."ORDER_NUMBER"))),7)) + ' - ' + (substring((convert(varchar(50), year(T1."ORDER_DATE")) + '-' + convert(varchar(50), month(T1."ORDER_DATE")) + '-' + convert(varchar(50), day(T1."ORDER_DATE"))), 1, 10)) + ' - ' + T1."DELIVERY_ACCOUNT" + T1."NAME" + ' - ' + T1."REGISTER_NUMBER" + ' - ' + T1."STATUS" as "Auftrag_Det_T",
	   (left((((T1."ORDER_NUMBER"))),7)) + ' - ' + (substring((convert(varchar(50), year(T1."ORDER_DATE")) + '-' + convert(varchar(50), month(T1."ORDER_DATE")) + '-' + convert(varchar(50), day(T1."ORDER_DATE"))), 1, 10)) + ' - ' + (rtrim(T1."DELIVERY_ACCOUNT")) + ' ' + T1."NAME" + ' - ' + (rtrim(T1."REGISTER_NUMBER")) + ' - ' + T1."STATUS" as "Auftrag_Det_S",
	   T1."BASIS_NUMBER" + ' - ' + T1."CHASSIS_NUMBER" as "Fahrzeug",
	   T11."MOD_LIN_SPECIFY" as "Model",
	   T10."DESCRIPTION" as "Description",
	   T10."GLOBAL_MAKE_CD" as "Global Make Cd",
	   T2."USED_TIME_INT" * 12 as "benutzte AW",
	   T2."EST_TIME_INT" * 12 as "Soll AW",
	   (rtrim(T1."DEBIT_ACCOUNT")) + ' - ' + T1."NAME" as "Kunde",
	   T2."INV_TIME_INT" * 12 as "verk. AW",
	   CASE WHEN (T2."ORDER_LINETYPE" = '3') THEN (T2."LINES_NET_VALUE") ELSE null END as "Umsatz Sonstiges",
	   T2."USED_TIME_INT" as "Used Time Int",
	   T2."EST_TIME_INT" as "Est Time Int",
	   T2."INV_TIME_INT" as "Inv Time Int",
	   CASE WHEN (T1."STATUS" IN ('35','37','39','47','49','34','36')) THEN ('Rechnung/Gutschrift') WHEN (T1."STATUS" IN ('30','40','32')) THEN ('offen') WHEN (T1."STATUS" IN ('41','31')) THEN ('Kostenvoranschlag') ELSE null END as "Status_1",
	   CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END as "Umsatz Teile (nur Teile)",
	   CASE WHEN (T2."INV_TIME" <> 0) THEN (T2."LINES_NET_VALUE") ELSE null END as "Umsatz Lohn",
	   CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '49')) THEN (T2."LINES_NET_VALUE") ELSE null END as "Umsatz Teile Service",
	   T1."STATUS" + ' - ' + T3."STAT_SPECIFY" as "Auftragsstatus",
	   CASE WHEN (T1."STATUS" BETWEEN '30' AND '39') THEN ('Teile') WHEN (T1."STATUS" BETWEEN '40' AND '49') THEN ('Service') WHEN (T1."STATUS" = '70') THEN ('sonst. Auftrag') WHEN (T1."STATUS" = '91') THEN ('Anfrage') ELSE null END as "Auftragsart",
	   T7."SEL_NAME" as "Sel Name_Monteur",
	   T5."SEL_NAME" as "Sel Name",
	   T1."PMT_TERM" + ' - ' + T1."PAYMENT_TEXT" as "Geschäftsart",
	   CASE WHEN (T1."CUSTOMER_GROUP" BETWEEN '10' AND '59') THEN ('extern') WHEN (T1."CUSTOMER_GROUP" IN ('91','92','60','70')) THEN ('GWL') WHEN (((T1."CUSTOMER_GROUP" IN ('99')) or (T1."PMT_TERM" = 'IN')) or ((left(T1."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as "Umsatzart",
	   T6."CUSTOMER_GROUP" + ' - ' + T6."CUST_GROUP_SPECIFY" as "Kundenart",
	   T4."DEPARTMENT_TYPE_ID" + ' - ' + T4."DESCRIPTION" as "Kostenstelle",
	   CASE WHEN (((left(T1."DEPARTMENT",2))) IN ('05','09','111','06','08','01','10','14','12','03','04','02')) THEN ('1') ELSE ((substring(T1."DEPARTMENT", 2, 1))) END as "Marke",
	   (left(T1."DEPARTMENT",2)) as "Standort",
	   T1."CLIENT_DB" as "Hauptbetrieb",
	   T8."REPAIR_GRP_SPECIFY" as "Repair Grp Specify",
	   T9."Fabrikat" as "Fabrikat_ori",
	   T8."MAKE_CD" as "Make Cd",
	   T8."REPAIR_GROUP" as "Repair Group",
	   T7."SEL_FAMILY_NAME" as "Sel Family Name_Monteur",
	   T7."SEL_FIRST_NAME" as "Sel First Name_Monteur",
	   T7."SEL_DEPARTMENT" as "Sel Department",
	   T7."SELLER_CODE" as "Seller Code",
	   T6."CUST_GROUP_SPECIFY" as "Cust Group Specify",
	   T6."CUSTOMER_GROUP" as "Customer Group",
	   T5."SEL_FAMILY_NAME" as "Sel Family Name",
	   T5."SEL_FIRST_NAME" as "Sel First Name",
	   T5."SEL_DEPARTMENT" as "Sel Department_2",
	   T5."SELLER_CODE" as "Seller Code_2",
	   T4."DESCRIPTION" as "Description_2",
	   T4."DEPARTMENT_TYPE_ID" as "Department Type Id",
	   T3."STAT_SPECIFY" as "Stat Specify",
	   T3."STAT_CODE" as "Stat Code",
	   T2."UNIQUE_IDENT" as "Unique Ident",
	   T2."MAKE_TIME_UNIT" as "Make Time Unit",
	   T2."INV_TIME" as "Inv Time",
	   T2."EST_TIME" as "Est Time",
	   T2."USED_TIME" as "Used Time",
	   T2."REPAIR_NAME" as "Repair Name",
	   T2."REPAIR_GROUP" as "Repair Group_2",
	   T2."REPAIR_CODE" as "Repair Code",
	   T2."LINE_COSTS" as "Line Costs",
	   T2."DELIVERY_QUANTITY" as "Delivery Quantity",
	   T2."ORDER_QUANTITY" as "Order Quantity",
	   T2."PROD_NAME" as "Prod Name",
	   T2."PRODUCT_GROUP" as "Product Group",
	   T2."MAKE_CD" as "Make Cd_2",
	   T2."PROD_CODE" as "Prod Code",
	   T2."LINES_NET_VALUE" as "Lines Net Value",
	   T2."STDPRICE" as "Stdprice",
	   T2."DISCOUNT" as "Discount",
	   T2."SALESMAN" as "Salesman",
	   T2."MECHANIC_CODE" as "Mechanic Code",
	   T2."REDUCTION_AMOUNT" as "Reduction Amount",
	   T2."REDUCTION_CODE" as "Reduction Code",
	   T2."ORDER_LINETYPE" as "Order Linetype",
	   T2."LINE_NUMBER" as "Line Number",
	   T2."ORDER_NUMBER" as "Order Number_ori",
	   T1."UNIQUE_IDENT" as "Unique Ident_2",
	   T1."CONV_FLAG" as "Conv Flag",
	   T1."ACTUAL_INV_DATE_TIME" as "Actual Inv Date Time",
	   T1."FHG_REPORT" as "Fhg Report",
	   T1."LDC_ORDER" as "Ldc Order",
	   T1."USE_PARTS_PRE_PICKING" as "Use Parts Pre Picking",
	   T1."REF_IDENT_SALES_CLASS" as "Ref Ident Sales Class",
	   T1."REF_IDENT_INV_TOTAL" as "Ref Ident Inv Total",
	   T1."COMMISSION_SALESMAN" as "Commission Salesman",
	   T1."WORKSHOP_TEAM" as "Workshop Team",
	   T1."CHASSIS_NUMBER" as "Chassis Number",
	   T1."TRANSFER_MAKE_CD" as "Transfer Make Cd",
	   T1."YEAR_MODEL" as "Year Model",
	   T1."MAKE_CD" as "Make Cd_3",
	   T1."FAC_MODEL_CODE_S" as "Fac Model Code S",
	   T1."END_TIME" as "End Time",
	   T1."END_DATE" as "End Date",
	   T1."ARRIVAL_DATE" as "Arrival Date",
	   T1."ARRIVAL_TIME" as "Arrival Time",
	   T1."SPLIT_COUNTER" as "Split Counter",
	   T1."WORKSHOP_PRICECODE" as "Workshop Pricecode",
	   T1."MODEL_TEXT" as "Model Text",
	   T1."BOL_TAX_SHARE" as "Bol Tax Share",
	   T1."EXPECTED_ORDER_TIM" as "Expected Order Tim",
	   T1."REFERENCE_NUMBER" as "Reference Number",
	   T1."DISCOUNT_LIMIT" as "Discount Limit",
	   T1."MAIL_ADDR" as "Mail Addr",
	   T1."ZIPCODE" as "Zipcode",
	   T1."ADDR_2" as "Addr 2",
	   T1."STREET_ADDR" as "Street Addr",
	   T1."NAME" as "Name",
	   T1."TITLE" as "Title",
	   T1."SALES_TAX_FREE" as "Sales Tax Free",
	   T1."INVOICE_CHARGE" as "Invoice Charge",
	   T1."INVOICE_ROUNDED" as "Invoice Rounded",
	   T1."INVOICE_DISC_PERC" as "Invoice Disc Perc",
	   T1."SALES_CLASS_NUMBER" as "Sales Class Number",
	   T1."PREV_STATUS" as "Prev Status",
	   T1."MILEAGE" as "Mileage",
	   T1."BASIS_NUMBER" as "Basis Number",
	   T1."INVOICE_COPY_CODE" as "Invoice Copy Code",
	   T1."SMALL_ACCESSORIES" as "Small Accessories",
	   T1."CUSTOMER_GROUP" as "Customer Group_2",
	   T1."INTERNAL_CODE" as "Internal Code",
	   T1."PLACE_CODE" as "Place Code",
	   T1."STOCK" as "Stock",
	   T1."MISC_ADDS" as "Misc Adds",
	   T1."PRICE_CODE" as "Price Code",
	   T1."DISCOUNT_AMOUNT" as "Discount Amount",
	   T1."TAX_SHARE" as "Tax Share",
	   T1."ORDERS_GROSSVALUE" as "Orders Grossvalue",
	   T1."PURCH_TAX" as "Purch Tax",
	   T1."COSTS" as "Costs",
	   T1."PAYMENT_TEXT" as "Payment Text",
	   T1."DUEDATE_1" as "Duedate 1",
	   T1."NEXT_LINE_NUMBER" as "Next Line Number",
	   T1."PMT_TERM" as "Pmt Term",
	   T1."TAX_PERC" as "Tax Perc",
	   T1."TAX_CODE" as "Tax Code",
	   T1."INVOICE_DATE" as "Invoice Date_ori",
	   T1."DELIVERY_DATE" as "Delivery Date",
	   T1."DEBIT_PERM" as "Debit Perm",
	   T1."SALESMAN" as "Salesman_2",
	   T1."DEBET_DEPARTMENT" as "Debet Department",
	   T1."DEPARTMENT" as "Department",
	   T1."DELIVERY_ACCOUNT" as "Delivery Account",
	   T1."HANDLER" as "Handler",
	   T1."TRANSACT_DATE" as "Transact Date",
	   T1."STATE_CODE" as "State Code",
	   T1."WORKSHOP_MODEL" as "Workshop Model",
	   T1."INVOICE_NUMBER" as "Invoice Number",
	   T1."DEBIT_ACCOUNT" as "Debit Account",
	   T1."STATE_KEY_DATE" as "State Key Date",
	   T1."STATUS" as "Status",
	   T1."REGISTER_NUMBER" as "Register Number"
from (((((((((((("OPTIMA"."import"."ORDER_HEADER" T1 left outer join "OPTIMA"."import"."ORDER_LINE" T2 on (T1."ORDER_NUMBER" = T2."ORDER_NUMBER") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP25" T3 on (T1."STATUS" = T3."STAT_CODE") and (T1."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on (T1."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") and (T1."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T5 on (T1."SALESMAN" = T5."SELLER_CODE") and (T1."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP48" T6 on (T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T7 on (T2."MECHANIC_CODE" = T7."SELLER_CODE") and (T2."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP73" T8 on ((T2."REPAIR_GROUP" = T8."REPAIR_GROUP") and (T2."MAKE_CD" = T8."MAKE_CD")) and (T2."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Marken" T9 on (T1."MAKE_CD" = T9."Make") and (T1."CLIENT_DB" = T9."Client_DB")) left outer join "OPTIMA"."import"."GLOBAL_MAKE" T10 on (T1."MAKE_CD" = T10."GLOBAL_MAKE_CD") and (T1."CLIENT_DB" = T10."CLIENT_DB")) left outer join "OPTIMA"."import"."VEHICLE" T13 on ((T13."CHASSIS_NUMBER" = T1."CHASSIS_NUMBER") and (T13."BASIS_NUMBER" = T1."BASIS_NUMBER")) and (T13."CLIENT_DB" = T1."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP5Q" T11 on ((T13."MODEL_LINE" = T11."MODEL_LINE") and (T13."MAKE_CD" = T11."MAKE_CD")) and (T13."CLIENT_DB" = T11."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T12 on ((left(T1."DEPARTMENT",2)) = T12."Standort") and (T1."CLIENT_DB" = T12."Hauptbetrieb"))
where ((((T1."STATUS" IN ('30','40','42','43','44')) and (T1."INVOICE_DATE" = convert(datetime, '1800-01-01 00:00:00.000'))) and (T1."ORDER_DATE" >= convert(datetime, '2020-01-01 00:00:00.000'))) and (T2."ORDER_LINETYPE" <> '2'))
-- order by "Order Number_ori_2" asc
) D1