COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\nw\nw_gw_vk_csv.imr TITLE,nw_gw_vk_csv.imr BEGIN SQL select c1 as c1, c2 as c2, c3 as c3, c4 as c4, c5 as c5, c6 as c6, c7 as c7, c8 as c8, c9 as c9, c10 as c10, c11 as c11, c12 as c12, c13 as c13, c14 as c14, c15 as c15, c16 as c16, c17 as c17, c18 as c18, c19 as c19, c20 as c20, c21 as c21, c22 as c22, c23 as c23, c24 as c24, c25 as c25, c26 as c26, c27 as c27, c28 as c28, c29 as c29, c30 as c30, c31 as c31, c32 as c32, c33 as c33, c34 as c34, c35 as c35, c36 as c36, c37 as c37, c38 as c38, c39 as c39, c40 as c40, c41 as c41, c42 as c42, c43 as c43, c44 as c44, c45 as c45, c46 as c46, c47 as c47, c48 as c48, c49 as c49, c50 as c50, c51 as c51, XMAX(c51 for c1) as c52, c53 as c53, c54 as c54, c55 as c55, c56 as c56, c57 as c57, c58 as c58, c59 as c59, c60 as c60, c61 as c61, c62 as c62, c63 as c63, c64 as c64, c65 as c65, c66 as c66, c67 as c67, c68 as c68, c69 as c69, c70 as c70, c71 as c71, c72 as c72, c73 as c73, CASE WHEN ((XSUM(c86 for c1)) < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as c74, c75 as c75, c76 as c76, c77 as c77, c78 as c78, c79 as c79, c80 as c80, c81 as c81, c82 as c82, c83 as c83, XCOUNT(c96 for c1) as c84, (c83 / XCOUNT(c96 for c1)) as c85, c86 as c86, XSUM(c86 for c1) as c87, c88 as c88, c89 as c89, c90 as c90, c91 as c91, c92 as c92, c93 as c93, c94 as c94, c95 as c95 from (select c100 as c1, c172 as c2, c171 as c3, c170 as c4, c169 as c5, c168 as c6, c167 as c7, c126 as c8, c166 as c9, c165 as c10, c164 as c11, c163 as c12, c162 as c13, '' as c14, c161 as c15, c160 as c16, c159 as c17, c158 as c18, c157 as c19, c117 as c20, c117 as c21, c115 as c22, c156 as c23, c126 as c24, c155 as c25, c154 as c26, c153 as c27, c152 as c28, c151 as c29, c150 as c30, c149 as c31, c148 as c32, c147 as c33, c146 as c34, c145 as c35, c144 as c36, c143 as c37, c113 as c38, c120 as c39, c112 as c40, c142 as c41, c114 as c42, c141 as c43, c140 as c44, c111 as c45, c139 as c46, c138 as c47, c137 as c48, c136 as c49, c135 as c50, c134 as c51, c133 as c53, c132 as c54, c121 as c55, c131 as c56, c130 as c57, c129 as c58, c128 as c59, c127 as c60, c126 as c61, c125 as c62, c124 as c63, c123 as c64, c122 as c65, c121 as c66, c113 as c67, c111 as c68, c114 as c69, c112 as c70, c120 as c71, c119 as c72, c118 as c73, c117 as c75, '' as c76, c116 as c77, c115 as c78, c114 as c79, c113 as c80, c112 as c81, c111 as c82, 1 as c83, c110 as c86, c109 as c88, c108 as c89, c107 as c90, c106 as c91, c105 as c92, c104 as c93, c103 as c94, c102 as c95, c101 as c96, XMAX(c134 for c100) as c97 from (select T1."UNIT_NUMBER" as c100, T1."BASIS_NUMBER" as c101, T10."UNIQUE_IDENT" as c102, CASE WHEN (((datepart({month},T1."SALE_DATE"))) = 1) THEN ('Januar') WHEN (((datepart({month},T1."SALE_DATE"))) = 2) THEN ('Februar') WHEN (((datepart({month},T1."SALE_DATE"))) = 3) THEN ('März') WHEN (((datepart({month},T1."SALE_DATE"))) = 4) THEN ('April') WHEN (((datepart({month},T1."SALE_DATE"))) = 5) THEN ('Mai') WHEN (((datepart({month},T1."SALE_DATE"))) = 6) THEN ('Juni') WHEN (((datepart({month},T1."SALE_DATE"))) = 7) THEN ('Juli') WHEN (((datepart({month},T1."SALE_DATE"))) = 8) THEN ('August') WHEN (((datepart({month},T1."SALE_DATE"))) = 9) THEN ('September') WHEN (((datepart({month},T1."SALE_DATE"))) = 10) THEN ('Oktober') WHEN (((datepart({month},T1."SALE_DATE"))) = 11) THEN ('November') WHEN (((datepart({month},T1."SALE_DATE"))) = 12) THEN ('Dezember') ELSE null END as c103, (ascii(((datepart({year},T1."SALE_DATE"))))) || (ascii(((datepart({month},T1."SALE_DATE"))))) as c104, (datepart({month},T1."SALE_DATE")) as c105, (datepart({year},T1."SALE_DATE")) as c106, T14."Standort_Name" as c107, ((T14."Standort_ID")) as c108, CASE WHEN ((extract(DAY FROM (getdate()) - T1."SALE_DATE")) <= 365) THEN ((truncate(T1."UNIT_NUMBER")) || ' - ' || (truncate(T14."Standort_Name")) || ' - ' || (truncate((CASE WHEN (T1."VEHICLE_TYPE" IS NULL) THEN (T1."VEHICLE_TYPE") ELSE (T1."VEHICLE_TYPE" || ' - ' || T6."VEHICLE_TYPE_TEXT") END))) || ' - ' || (truncate(T2."SEL_NAME")) || ' - ' || (truncate(((truncate(T7."CUSTOMER_NUMBER")) || ' - ' || (truncate(T7."NAME")))))) ELSE ('FZG-Rechnung älter 365 Tage') END as c109, (CASE WHEN (T10."TRANSACTION_CODE" IN ('10','14','24')) THEN (T10."ORDERS_GROSSVALUE" * -1) ELSE (0) END) + (CASE WHEN (T10."TRANSACTION_CODE" IN ('19','22','16')) THEN (T10."ORDERS_GROSSVALUE" * -1) ELSE (0) END) + (CASE WHEN (T10."TRANSACTION_CODE" IN ('12')) THEN (T10."ORDERS_GROSSVALUE" * -1) ELSE (0) END) - (CASE WHEN (T10."TRANSACTION_CODE" IN ('11 ','15','17')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END) - (CASE WHEN (T10."TRANSACTION_CODE" IN ('13','90')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END) - ((CASE WHEN (T10."TRANSACTION_CODE" IN ('25 ','18','27','28','29','26','23')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END) + (CASE WHEN (T10."TRANSACTION_CODE" IN ('30','31','32','33','34','35','36','37','38','39','46','47','48','49','40','41','44','45')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END)) as c110, (((CASE WHEN (T10."TRANSACTION_CODE" IN ('25 ','18','27','28','29','26','23')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END) + (CASE WHEN (T10."TRANSACTION_CODE" IN ('30','31','32','33','34','35','36','37','38','39','46','47','48','49','40','41','44','45')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END))) as c111, (CASE WHEN (T10."TRANSACTION_CODE" IN ('12')) THEN (T10."ORDERS_GROSSVALUE" * -1) ELSE (0) END) as c112, ((CASE WHEN (T10."TRANSACTION_CODE" IN ('10','14','24')) THEN (T10."ORDERS_GROSSVALUE" * -1) ELSE (0) END)) as c113, ((CASE WHEN (T10."TRANSACTION_CODE" IN ('13','90')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END)) as c114, T1."PREV_OWNER" as c115, CASE WHEN ((extract(DAY FROM (getdate()) - T1."SALE_DATE")) <= 365) THEN ((truncate((ascii(T10."DOCUMENT_NR")))) || ' - ' || T10."TRANSACTION_CODE" || ' - ' || T10."REFERENCE") ELSE ('FZG-Rechnung älter 365 Tage') END as c116, T9."SEL_NAME" as c117, (truncate(T9."SEL_NAME")) || ' / ' || T1."PREV_OWNER" as c118, CASE WHEN (T13."Order_By" IS NULL) THEN (1) ELSE (T13."Order_By") END as c119, (CASE WHEN (T10."TRANSACTION_CODE" IN ('19','22','16')) THEN (T10."ORDERS_GROSSVALUE" * -1) ELSE (0) END) as c120, T7."ZIPCODE" as c121, (substring(T7."ZIPCODE" from 1 for 4)) as c122, (substring(T7."ZIPCODE" from 1 for 3)) as c123, (substring(T7."ZIPCODE" from 1 for 2)) as c124, (substring(T7."ZIPCODE" from 1 for 1)) as c125, (CASE WHEN (T4."ORIG_MODEL_CODE1" = '5767474P2000') THEN ('Jeep Compass Limited') ELSE (T4."MODEL_TEXT") END) as c126, T13."Order_By" as c127, CASE WHEN (T3."DESCRIPTION" IN ('Dacia','Fiat','Jeep','Kia','Renault','Piaggio (Vespa)')) THEN (T3."DESCRIPTION") ELSE ('Fremdfabrikat') END as c128, (T14."Hauptbetrieb_Name") as c129, (T14."Hauptbetrieb_ID") as c130, T13."Fabrikat" as c131, (truncate(T10."TRANSACTION_CODE")) || ' - ' || (truncate(T11."SPECIFY")) as c132, (substring(T12."DEPARTMENT" from 1 for 2)) as c133, T12."UNIQUE_IDENT" as c134, T12."DEPARTMENT" as c135, (db_name()) as c136, CASE WHEN (T3."DESCRIPTION" IN ('Opel')) THEN (T3."DESCRIPTION") ELSE ('Fremdfabrikat') END as c137, CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM T1."SALE_DATE" - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM T1."SALE_DATE" - T1."RECEPTION_DATE"))) END as c138, CASE WHEN (T10."TRANSACTION_CODE" IN ('18','27','28','29')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END as c139, CASE WHEN (T10."TRANSACTION_CODE" IN ('30','31','32','33','34','35','36','37','38','39','46','47','48','49','40','41','44','45')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END as c140, CASE WHEN (T10."TRANSACTION_CODE" IN ('25 ','18','27','28','29','26','23')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END as c141, CASE WHEN (T10."TRANSACTION_CODE" IN ('11 ','15','17')) THEN (T10."ORDERS_GROSSVALUE") ELSE (0) END as c142, T10."BOOK_KEEPING_CODE" as c143, T10."DISCOUNT" as c144, T10."ORDERS_GROSSVALUE" as c145, T10."COSTS" as c146, T10."REFERENCE" as c147, T11."SPECIFY" as c148, T10."TRANSACTION_CODE" as c149, T10."ORDER_NUMBER" as c150, T10."ORDER_DATE" as c151, T10."DOCUMENT_NR" as c152, T10."DOCUMENT_DATE" as c153, T10."LINE_TYPE" as c154, T2."SEL_NAME" || ' / ' || ((truncate(T7."CUSTOMER_NUMBER")) || ' - ' || (truncate(T7."NAME"))) as c155, CASE WHEN (T9."SEL_NAME" IS NULL) THEN (' - ' || ' / ' || T1."PREV_OWNER") ELSE (T9."SEL_NAME" || ' / ' || T1."PREV_OWNER") END as c156, ((truncate(T1."UNIT_NUMBER")) || ' - ' || ((truncate(T7."CUSTOMER_NUMBER")) || ' - ' || (truncate(T7."NAME")))) || ' - ' || T1."VEHICLE_TYPE" as c157, T1."UNIT_NUMBER" || ' - ' || T1."OWNER" || ' - ' || (asciiz(extract(YEAR FROM T4."ORIG_INV_DATE"),4) || '-' || asciiz(extract(MONTH FROM T4."ORIG_INV_DATE"),2) || '-' || asciiz(extract(DAY FROM T4."ORIG_INV_DATE"),2)) as c158, (truncate(T1."UNIT_NUMBER")) || ' - ' || ((truncate(T7."CUSTOMER_NUMBER")) || ' - ' || (truncate(T7."NAME"))) as c159, CASE WHEN (T1."ECC_STATUS" = '21') THEN ('21 - Vorlauf') WHEN (T1."ECC_STATUS" = '25') THEN ('25 - für Kd best. FZG') WHEN (T1."ECC_STATUS" = '41') THEN ('41 - Bestand') WHEN (T1."ECC_STATUS" = '44') THEN ('44 - am Hof nicht fakt.') WHEN (T1."ECC_STATUS" = '64') THEN ('64 - verkauft') ELSE null END as c160, T4."COLOUR_CODE" || ' - ' || T4."COLOUR_CF" as c161, T7."CUSTOMER_GROUP" || ' - ' || T8."CUST_GROUP_SPECIFY" as c162, (truncate(T7."CUSTOMER_NUMBER")) || ' - ' || (truncate(T7."NAME")) as c163, CASE WHEN (T1."VEHICLE_TYPE" IS NULL) THEN (T1."VEHICLE_TYPE") ELSE (T1."VEHICLE_TYPE" || ' - ' || T6."VEHICLE_TYPE_TEXT") END as c164, CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','W','M','V')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','G','K','R')) THEN ('Gebrauchtwagen') ELSE null END as c165, T4."CHASSIS_NUMBER" as c166, CASE WHEN ((CASE WHEN (T3."DESCRIPTION" IN ('Dacia','Fiat','Jeep','Kia','Renault','Piaggio (Vespa)')) THEN (T3."DESCRIPTION") ELSE ('Fremdfabrikat') END) = 'Fremdfabrikat') THEN (T4."MODEL_TEXT") ELSE (T5."MOD_LIN_SPECIFY") END as c167, T3."DESCRIPTION" as c168, T2."SEL_NAME" as c169, (substring(T1."SALES_DEPARTMENT" from 1 for 2)) as c170, T1."CLIENT_DB" as c171, T1."SALE_DATE" as c172 from ((((((((((((("OPTIMA"."import"."UNIT_FILE" T1 left outer join "OPTIMA"."import"."VPP43" T2 on (T1."SALE_SALESMAN" = T2."SELLER_CODE") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."VEHICLE" T4 on (T1."BASIS_NUMBER" = T4."BASIS_NUMBER") and (T1."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."GLOBAL_MAKE" T3 on (T4."MAKE_CD" = T3."GLOBAL_MAKE_CD") and (T4."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP5Q" T5 on ((T4."MODEL_LINE" = T5."MODEL_LINE") and (T4."MAKE_CD" = T5."MAKE_CD")) and (T4."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP5R" T6 on (T1."VEHICLE_TYPE" = T6."VEHICLE_TYPE") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."CUSTOMER" T7 on (T1."ACCOUNT_SALES" = T7."CUSTOMER_NUMBER") and (T1."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP48" T8 on (T7."CUSTOMER_GROUP" = T8."CUSTOMER_GROUP") and (T7."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T9 on (T1."PURCH_SALSMAN_CODE" = T9."SELLER_CODE") and (T1."CLIENT_DB" = T9."CLIENT_DB")) left outer join "OPTIMA"."import"."UNIT_HISTORY" T10 on (T1."UNIT_NUMBER" = T10."UNIT_NUMBER") and (T1."CLIENT_DB" = T10."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP5A" T11 on (T10."TRANSACTION_CODE" = T11."TRANSACTION_CODE") and (T10."CLIENT_DB" = T11."CLIENT_DB")) left outer join "OPTIMA"."import"."UNIT_CHANGE_HIST" T12 on ((T12."UNIT_NUMBER" = T1."UNIT_NUMBER") and (T12."DEPARTMENT" <> ' ')) and (T1."CLIENT_DB" = T12."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Marken" T13 on (T13."Client_DB" = T4."CLIENT_DB") and (T13."Make" = T4."MAKE_CD")) left outer join "OPTIMA"."data"."GC_Department" T14 on (T12."CLIENT_DB" = T14."Hauptbetrieb") and ((substring(T12."DEPARTMENT" from 1 for 2)) = T14."Standort")) where ((((T1."ECC_STATUS" = '64') and (not T10."TRANSACTION_CODE" IN ('76 ','77 '))) and (T1."SALE_DATE" >= TIMESTAMP '2020-01-01 00:00:00.000')) and (not T1."CLIENT_DB" IN ('11','1'))) ) D2 ) D1 where (c51 = c97) order by c1 asc END SQL COLUMN,0,Unit Number COLUMN,1,Invoice Date COLUMN,2,Hauptbetrieb COLUMN,3,Standort_Verkaufskostenstelle COLUMN,4,Verkäufer COLUMN,5,Fabrikat_ori COLUMN,6,Model COLUMN,7,Modellbez COLUMN,8,Fahrgestellnr COLUMN,9,Fahrzeugart COLUMN,10,Fahrzeugtyp COLUMN,11,Kunde COLUMN,12,Kundenart COLUMN,13,Geschäftsart COLUMN,14,Farbe COLUMN,15,ECC_Status_Text COLUMN,16,FZG COLUMN,17,FZG_1 COLUMN,18,FZG_Liste_1 COLUMN,19,Sel Name_EK_vpp43 COLUMN,20,Einkäufer COLUMN,21,Vorbesitzer COLUMN,22,FZG_Liste_2 COLUMN,23,FZG_Liste_3 COLUMN,24,FZG_Liste_4 COLUMN,25,Line Type COLUMN,26,Document Date COLUMN,27,Document Nr COLUMN,28,Order Date COLUMN,29,Order Number COLUMN,30,Transaction Code COLUMN,31,Specify_vpp5a COLUMN,32,Reference COLUMN,33,Costs COLUMN,34,Orders Grossvalue COLUMN,35,Discount COLUMN,36,Book Keeping Code COLUMN,37,Erlös COLUMN,38,Sonst. Erlöse COLUMN,39,Ums. Fracht und Überf. COLUMN,40,Nachlass COLUMN,41,Provisionen COLUMN,42,EK Fahrzeug COLUMN,43,FZG-Kosten COLUMN,44,Einsatz COLUMN,45,VK_Hilfen COLUMN,46,Standtage COLUMN,47,Fabrikat_manuell COLUMN,48,Mandant COLUMN,49,Department_unit_change_hist COLUMN,50,Unique Ident_unit_change_hist COLUMN,51,Maximum_Unique_Ident COLUMN,52,Standort COLUMN,53,TR Code COLUMN,54,Zipcode COLUMN,55,Fabrikat_GC_Marke_ori COLUMN,56,Hauptbetrieb_ID COLUMN,57,Hauptbetrieb_Name COLUMN,58,Fabrikat COLUMN,59,Order By_ori COLUMN,60,Modell_Beschreibung COLUMN,61,PLZ_1 COLUMN,62,PLZ_2 COLUMN,63,PLZ_3 COLUMN,64,PLZ_4 COLUMN,65,PLZ Code_Deb COLUMN,66,FZG_Erlös COLUMN,67,Einsatz_FZG COLUMN,68,Erlös_Prov COLUMN,69,Erlös_Überf. COLUMN,70,Erlös Sonst. COLUMN,71,Order By COLUMN,72,Einkäufer/Vorbesitzer COLUMN,73,DB1 < 0 COLUMN,74,Name_Einkäufer COLUMN,75,Konto COLUMN,76,Buch_Text COLUMN,77,Name_Lieferant COLUMN,78,Erlös Prov COLUMN,79,Erlös_FZG COLUMN,80,Erlös Überf. COLUMN,81,Einsatz FZG COLUMN,82,Menge_1 COLUMN,83,Anzahl_Datensätze COLUMN,84,Menge COLUMN,85,DB1 COLUMN,86,Summe_DB1 COLUMN,87,FZG_Detail COLUMN,88,Standort_ID COLUMN,89,Standort_Name COLUMN,90,Jahr_Invoice_Date COLUMN,91,Monat_Invoice_Date COLUMN,92,Jahr_Monat COLUMN,93,Monat_Text COLUMN,94,Unique Ident_Unit_history