GAPS/Portal/System/IQD/OP/Kundenbewegung.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\OP\Kundenbewegung.imr
TITLE,Kundenbewegung.imr
BEGIN SQL
select T1."CUSTOMER_NUMBER" as c1,
	   T1."TRANSACT_DATE" as c2,
	   T1."NAME" as c3,
	   T1."STREET_ADDR" as c4,
	   T1."ADDR_2" as c5,
	   T1."MAIL_ADDR" as c6,
	   T1."ZIPCODE" as c7,
	   T1."CUSTOMER_GROUP" as c8,
	   T1."PHONE_1" as c9,
	   T1."PHONE_2" as c10,
	   T1."CONTACT_DATE" as c11,
	   T1."LAST_INVOICE_DATE" as c12,
	   T1."SALDO_C_U_CUST" as c13,
	   T1."MOBILE_PHONE" as c14,
	   T1."MOBILE_PHONE_2" as c15,
	   (extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE")) as c16,
	   T2."VOUCHER_NO" as c17,
	   T2."BOOKKEEP_DATE" as c18,
	   T2."SALES_TYPE" as c19,
	   T2."CUSTOMER_NUMBER" as c20,
	   T2."VOUCHER_NO" as c21,
	   T2."LINE_NO_CU" as c22,
	   T2."TRANSACTION_STATUS" as c23,
	   T2."DUE_DATE" as c24,
	   T2."BOOKKEEP_DATE" as c25,
	   T2."STATE_CODE" as c26,
	   T2."TRANSACT_DATE" as c27,
	   T2."HANDLER" as c28,
	   T2."CREATION_DATE" as c29,
	   T2."CREATION_TIME_8" as c30,
	   T2."STATUS" as c31,
	   T2."MODUL" as c32,
	   T2."BATCH_NUMBER" as c33,
	   T2."JOURNAL_NO" as c34,
	   T2."DOCUMENT_NO" as c35,
	   T2."DOCUMENT_DATE" as c36,
	   T2."DISCOUNT_DATE" as c37,
	   T2."DUNNING_DATE" as c38,
	   T2."START_INT_DATE" as c39,
	   T2."LAST_INT_DATE" as c40,
	   T2."PMT_TERM" as c41,
	   T2."DUNTIMES" as c42,
	   T2."TAX_CODE" as c43,
	   T2."REMINDER_CODE" as c44,
	   T2."SUM_C_U" as c45,
	   T2."TAX_C_U" as c46,
	   T2."CASH_DISCOUNT_C_U" as c47,
	   T2."INTEREST_PERC" as c48,
	   T2."PAID_C_U" as c49,
	   T2."COMMENT_CU" as c50,
	   T2."SETOFF_ACCT" as c51,
	   T2."COLLECT_ACCT" as c52,
	   T2."INTEREST_CALC" as c53,
	   T2."EEC_VAT_WORK_CODE" as c54,
	   T2."EEC_VAT_LIST_NO" as c55,
	   T2."SPLIT_CODE_CU" as c56,
	   T2."PMT_PROPOSAL" as c57,
	   T2."APPROVED" as c58,
	   T2."BOOK_NO" as c59,
	   T2."CHECK_NUMBER" as c60,
	   T2."ACTIVITY_CODE" as c61,
	   T2."SALES_TYPE" as c62,
	   T2."INT_VOUCHER_NO" as c63,
	   T2."CONV_FLAG" as c64,
	   T2."UNIQUE_IDENT" as c65,
	   CASE WHEN ((T2."MODUL" = '6') and (T2."STATUS" IN ('34','35','36','37','39'))) THEN ('Teile') WHEN ((T2."MODUL" = '6') and (T2."STATUS" IN ('47','49'))) THEN ('Service') WHEN ((T2."MODUL" = '6') and (T2."STATUS" IN ('FG','FR'))) THEN ('Verkauf') ELSE null END as c66,
	   '1' as c67,
	   (od_left(T3."DEPARTMENT_TYPE_ID",2)) as c68,
	   (substring(T3."DEPARTMENT_TYPE_ID" from 4 for 1)) as c69,
	   T4."SEL_NAME" as c70,
	   T1."NAME" || ' - ' || T1."CUSTOMER_NUMBER" as c71,
	   T1."CUSTOMER_NUMBER" || ' -  ' || T1."NAME" as c72,
	   T1."ADDR_2" || '  -  ' || T1."ZIPCODE" || T1."MAIL_ADDR" as c73,
	   (T1."CUSTOMER_NUMBER" || ' -  ' || T1."NAME") || ' - ' || (T1."ADDR_2" || '  -  ' || T1."ZIPCODE" || T1."MAIL_ADDR") || ' - Tel.: ' || T1."PHONE_1" || ' | ' || (CASE WHEN ((T2."MODUL" = '6') and (T2."STATUS" IN ('34','35','36','37','39'))) THEN ('Teile') WHEN ((T2."MODUL" = '6') and (T2."STATUS" IN ('47','49'))) THEN ('Service') WHEN ((T2."MODUL" = '6') and (T2."STATUS" IN ('FG','FR'))) THEN ('Verkauf') ELSE null END) || ' - ' || (asciiz(extract(YEAR FROM T1."LAST_INVOICE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."LAST_INVOICE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."LAST_INVOICE_DATE"),2)) || ' - ' || T5."MAKE_CD" || ' - ' || T5."MODEL_TEXT" as c74,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE"))) BETWEEN 730 AND 1095) THEN ('24 - 36 Monate') WHEN (((extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE"))) BETWEEN 1096 AND 1460) THEN ('37 - 48 Monate') WHEN (((extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE"))) BETWEEN 1461 AND 1825) THEN ('49 - 60 Monate') WHEN (((extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE"))) BETWEEN 578 AND 699) THEN ('19 - 23 Monate') WHEN (((extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE"))) BETWEEN 547 AND 577) THEN ('18 Monate') ELSE null END as c75,
	   T5."MODEL_TEXT" as c76,
	   T5."MAKE_CD" as c77,
	   T5."CHASSIS_NUMBER" as c78,
	   T5."MILEAGE" as c79,
	   T6."FIRST_REG_DATE" as c80
from (((((("deop01"."dbo"."CUSTOMER_TRANSACT" T2 left outer join "deop01"."dbo"."CUSTOMER" T1 on T2."CUSTOMER_NUMBER" = T1."CUSTOMER_NUMBER") left outer join "deop01"."dbo"."VEH_ORDER_HEADER" T7 on T2."DOCUMENT_NO" = T7."ORDER_NUMBER") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T3 on T7."DEPARTMENT" = T3."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."ORDER_HEADER" T5 on T2."DOCUMENT_NO" = T5."ORDER_NUMBER") left outer join "deop01"."dbo"."vPP43" T4 on T5."SALESMAN" = T4."SELLER_CODE") left outer join "deop01"."dbo"."VEHICLE" T6 on (T5."CHASSIS_NUMBER" = T6."CHASSIS_NUMBER") and (T5."BASIS_NUMBER" = T6."BASIS_NUMBER"))
where ((((((extract(DAY FROM (now()) - T1."LAST_INVOICE_DATE"))) BETWEEN 182 AND 1825) and (T1."LAST_INVOICE_DATE" = T2."BOOKKEEP_DATE")) and (T2."MODUL" = '6')) and (not T1."ADDR_2" LIKE '%verstorben%'))
order by c1 asc

END SQL
COLUMN,0,Customer Number
COLUMN,1,Transact Date
COLUMN,2,Name
COLUMN,3,Street Addr
COLUMN,4,Addr 2
COLUMN,5,Mail Addr
COLUMN,6,Zipcode
COLUMN,7,Customer Group
COLUMN,8,Phone 1
COLUMN,9,Phone 2
COLUMN,10,Contact Date
COLUMN,11,Last Invoice Date
COLUMN,12,Saldo C U Cust
COLUMN,13,Mobile Phone
COLUMN,14,Mobile Phone 2
COLUMN,15,Tage letzte Rechnung
COLUMN,16,Voucher No
COLUMN,17,Bookkeep Date
COLUMN,18,Sales Type
COLUMN,19,Customer Number
COLUMN,20,Voucher No
COLUMN,21,Line No Cu
COLUMN,22,Transaction Status
COLUMN,23,Due Date
COLUMN,24,Bookkeep Date
COLUMN,25,State Code
COLUMN,26,Transact Date
COLUMN,27,Handler
COLUMN,28,Creation Date
COLUMN,29,Creation Time 8
COLUMN,30,Status
COLUMN,31,Modul
COLUMN,32,Batch Number
COLUMN,33,Journal No
COLUMN,34,Document No
COLUMN,35,Document Date
COLUMN,36,Discount Date
COLUMN,37,Dunning Date
COLUMN,38,Start Int Date
COLUMN,39,Last Int Date
COLUMN,40,Pmt Term
COLUMN,41,Duntimes
COLUMN,42,Tax Code
COLUMN,43,Reminder Code
COLUMN,44,Sum C U
COLUMN,45,Tax C U
COLUMN,46,Cash Discount C U
COLUMN,47,Interest Perc
COLUMN,48,Paid C U
COLUMN,49,Comment Cu
COLUMN,50,Setoff Acct
COLUMN,51,Collect Acct
COLUMN,52,Interest Calc
COLUMN,53,Eec Vat Work Code
COLUMN,54,Eec Vat List No
COLUMN,55,Split Code Cu
COLUMN,56,Pmt Proposal
COLUMN,57,Approved
COLUMN,58,Book No
COLUMN,59,Check Number
COLUMN,60,Activity Code
COLUMN,61,Sales Type
COLUMN,62,Int Voucher No
COLUMN,63,Conv Flag
COLUMN,64,Unique Ident
COLUMN,65,Herkunft
COLUMN,66,Hauptbetrieb
COLUMN,67,Standort
COLUMN,68,Kostenstelle
COLUMN,69,Sel Name
COLUMN,70,Kunde
COLUMN,71,Kunde_Nr_Name
COLUMN,72,Adresse
COLUMN,73,Kunde_Komplett
COLUMN,74,Monate_keine_Rechnung
COLUMN,75,Model Text
COLUMN,76,Make Cd
COLUMN,77,Chassis Number
COLUMN,78,Mileage
COLUMN,79,First Reg Date