C11/System/WERWISO/IQD/OP/OP_DATEV_WERWISO_FINAL.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,OP_DATEV
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\op\OP_DATEV_WERWISO_FINAL.imr
TITLE,OP_DATEV_WERWISO_FINAL.imr
BEGIN SQL
select c48 as c1,
	   c85 as c2,
	   c84 as c3,
	   c83 as c4,
	   c82 as c5,
	   c81 as c6,
	   c80 as c7,
	   c79 as c8,
	   c78 as c9,
	   c77 as c10,
	   c76 as c11,
	   c75 as c12,
	   c74 as c13,
	   c73 as c14,
	   c72 as c15,
	   c71 as c16,
	   c70 as c17,
	   c69 as c18,
	   c68 as c19,
	   c67 as c20,
	   c66 as c21,
	   c65 as c22,
	   c64 as c23,
	   c63 as c24,
	   '1' as c25,
	   'S+K' as c26,
	   c62 as c27,
	   c62 as c28,
	   c61 as c29,
	   c60 as c30,
	   c59 as c31,
	   c58 as c32,
	   c57 as c33,
	   c57 as c34,
	   'alle Forderungen' as c35,
	   'alle Forderungen' as c36,
	   c56 as c37,
	   c55 as c38,
	   c54 as c39,
	   c53 as c40,
	   c52 as c41,
	   c51 as c42,
	   XSUM(c51 for c48) as c43,
	   c50 as c44,
	   c49 as c45
from 
(select T1."KONTONUMMER" as c48,
	   ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) as c49,
	   CASE WHEN (T2."Anrede" IS NOT NULL) THEN ((CASE WHEN (T2."Vorname" IS NOT NULL) THEN (((ascii(T2."Kundennummer"))) || ' - ' || (truncate(T2."Name1")) || ', ' || (truncate(T2."Vorname"))) ELSE (((ascii(T2."Kundennummer"))) || ' - ' || (truncate(T2."Name1"))) END) || ' - ' || T2."Anrede") ELSE ((CASE WHEN (T2."Vorname" IS NOT NULL) THEN (((ascii(T2."Kundennummer"))) || ' - ' || (truncate(T2."Name1")) || ', ' || (truncate(T2."Vorname"))) ELSE (((ascii(T2."Kundennummer"))) || ' - ' || (truncate(T2."Name1"))) END)) END as c50,
	   CASE WHEN (T1."BETRAG SOLL" = 0) THEN (T1."BETRAG HABEN" * -1) ELSE (T1."BETRAG SOLL") END as c51,
	   CASE WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) BETWEEN 0 AND 7) THEN ('< 1 Woche') WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) BETWEEN 8 AND 14) THEN ('1 - 2 Wochen') WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) > 84) THEN ('> 12 Wochen') WHEN (((extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END)))))) < 0) THEN ('noch nicht fällig') ELSE null END as c52,
	   (extract(DAY FROM (now()) - (cdatetime((CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END))))) as c53,
	   CASE WHEN (T2."Vorname" IS NOT NULL) THEN (((ascii(T2."Kundennummer"))) || ' - ' || (truncate(T2."Name1")) || ', ' || (truncate(T2."Vorname"))) ELSE (((ascii(T2."Kundennummer"))) || ' - ' || (truncate(T2."Name1"))) END as c54,
	   (ascii(T2."Kundennummer")) as c55,
	   T1."MAHNSTUFE" as c56,
	   (CASE WHEN (((CASE WHEN (T4."Auftragsart_Service" IS NOT NULL) THEN (T4."Auftragsart_Service") WHEN (T3."Fahrzeugart" IS NOT NULL) THEN (T3."Fahrzeugart") WHEN (((length((rtrim(T1."RECHNUNGS-NR")) || 'Z') - 1) = 3) and ((((T1."BUCHUNGSTEXT" LIKE '%Ochsendorf%') or (T1."BUCHUNGSTEXT" LIKE '%Hüsing%')) or (T1."BUCHUNGSTEXT" LIKE '%Mojen%')) or (T1."BUCHUNGSTEXT" LIKE '%Werkstatt%'))) THEN ('Service') WHEN (((length((rtrim(T1."RECHNUNGS-NR")) || 'Z') - 1) = 3) and ((((not T1."BUCHUNGSTEXT" LIKE '%Ochsendorf%') and (not T1."BUCHUNGSTEXT" LIKE '%Hüsing%')) and (not T1."BUCHUNGSTEXT" LIKE '%Mojen%')) and (not T1."BUCHUNGSTEXT" LIKE '%Werkstatt%'))) THEN ('Neuwagen') ELSE ('nicht zuzuordnen') END) = 'nicht zuzuordnen') and (((((CASE WHEN (T1."MAHNSTUFE" IS NOT NULL) THEN (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (truncate((CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END))) || ' - MS: ' || T1."MAHNSTUFE") ELSE (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END)) END) LIKE '%Ochsendorf%') or ((CASE WHEN (T1."MAHNSTUFE" IS NOT NULL) THEN (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (truncate((CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END))) || ' - MS: ' || T1."MAHNSTUFE") ELSE (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END)) END) LIKE '%Hüsing%')) or ((CASE WHEN (T1."MAHNSTUFE" IS NOT NULL) THEN (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (truncate((CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END))) || ' - MS: ' || T1."MAHNSTUFE") ELSE (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END)) END) LIKE '%Mojen%')) or ((CASE WHEN (T1."MAHNSTUFE" IS NOT NULL) THEN (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (truncate((CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END))) || ' - MS: ' || T1."MAHNSTUFE") ELSE (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END)) END) LIKE '%Werkstatt%'))) THEN ('Service') WHEN ((CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END) IN ('Seiz, Anton','Matz, Hendrik','Heintzen, Sandra','Hoffmann, Thorsten','Grell, André','Brümmer, Niklas','Sachse, Sandra','Behrens, Lea','Gude, Christopher')) THEN ('Neuwagen') ELSE ((CASE WHEN (T4."Auftragsart_Service" IS NOT NULL) THEN (T4."Auftragsart_Service") WHEN (T3."Fahrzeugart" IS NOT NULL) THEN (T3."Fahrzeugart") WHEN (((length((rtrim(T1."RECHNUNGS-NR")) || 'Z') - 1) = 3) and ((((T1."BUCHUNGSTEXT" LIKE '%Ochsendorf%') or (T1."BUCHUNGSTEXT" LIKE '%Hüsing%')) or (T1."BUCHUNGSTEXT" LIKE '%Mojen%')) or (T1."BUCHUNGSTEXT" LIKE '%Werkstatt%'))) THEN ('Service') WHEN (((length((rtrim(T1."RECHNUNGS-NR")) || 'Z') - 1) = 3) and ((((not T1."BUCHUNGSTEXT" LIKE '%Ochsendorf%') and (not T1."BUCHUNGSTEXT" LIKE '%Hüsing%')) and (not T1."BUCHUNGSTEXT" LIKE '%Mojen%')) and (not T1."BUCHUNGSTEXT" LIKE '%Werkstatt%'))) THEN ('Neuwagen') ELSE ('nicht zuzuordnen') END)) END) as c57,
	   CASE WHEN (T1."MAHNSTUFE" IS NOT NULL) THEN (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (truncate((CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END))) || ' - MS: ' || T1."MAHNSTUFE") ELSE (CASE WHEN (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END IS NULL) THEN ('ohne Belegnr.') ELSE (CASE WHEN (T1."RECHNUNGS-NR" IS NULL) THEN (T1."BELEGFELD 2") ELSE (T1."RECHNUNGS-NR") END) END || ' - ' || T1."BUCHUNGSTEXT" || ' - ' || (CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END)) END as c58,
	   CASE WHEN (T4."Auftragsart_Service" IS NOT NULL) THEN (T4."Auftragsart_Service") WHEN (T3."Fahrzeugart" IS NOT NULL) THEN (T3."Fahrzeugart") WHEN (((length((rtrim(T1."RECHNUNGS-NR")) || 'Z') - 1) = 3) and ((((T1."BUCHUNGSTEXT" LIKE '%Ochsendorf%') or (T1."BUCHUNGSTEXT" LIKE '%Hüsing%')) or (T1."BUCHUNGSTEXT" LIKE '%Mojen%')) or (T1."BUCHUNGSTEXT" LIKE '%Werkstatt%'))) THEN ('Service') WHEN (((length((rtrim(T1."RECHNUNGS-NR")) || 'Z') - 1) = 3) and ((((not T1."BUCHUNGSTEXT" LIKE '%Ochsendorf%') and (not T1."BUCHUNGSTEXT" LIKE '%Hüsing%')) and (not T1."BUCHUNGSTEXT" LIKE '%Mojen%')) and (not T1."BUCHUNGSTEXT" LIKE '%Werkstatt%'))) THEN ('Neuwagen') ELSE ('nicht zuzuordnen') END as c59,
	   CASE WHEN (T4."Serviceberater" IS NOT NULL) THEN (T4."Serviceberater") WHEN ((reverse(truncate(reverse(T3."Verkäufer")))) IS NOT NULL) THEN ((reverse(truncate(reverse(T3."Verkäufer"))))) ELSE ('n.n.') END as c60,
	   CASE WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '1') THEN ('Neu W') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '2') THEN ('BH TOY') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '3') THEN ('HB TOY') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '4') THEN ('LB TOY') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '5') THEN ('Berge') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '6') THEN ('BH REN') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '7') THEN ('HB REN') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '8') THEN ('Stade TOY') WHEN (((CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END)) = '9') THEN ('Taxi-Werk') ELSE ('nicht zuzuordnen') END as c61,
	   (CASE WHEN ((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 8) THEN ((od_left((T1."RECHNUNGS-NR"),1))) WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '7')) THEN ('5') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '5')) THEN ('6') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 9) and ((od_left((T1."RECHNUNGS-NR"),1)) = '9')) THEN ('7') WHEN (((length((T1."RECHNUNGS-NR") || 'Z') - 1) = 3) or (T1."RECHNUNGS-NR" LIKE '%-%')) THEN ((od_left((T1."RECHNUNGS-NR"),1))) ELSE ('nicht zuzuordnen') END) as c62,
	   CASE WHEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0)))) IS NULL) THEN ((cdate((cinteger(nconvert((substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from char_length((asciiz(round(T1."RECHNUNGSDATUM",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."RECHNUNGSDATUM",0,0),8)) from 1 for 2))),0))))) ELSE ((cdate((cinteger(nconvert((substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from char_length((asciiz(round(T1."FÄLLIGKEIT",0,0),8))) - 4 + 1 for 4)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 3 for 2)) || (substring((asciiz(round(T1."FÄLLIGKEIT",0,0),8)) from 1 for 2))),0))))) END as c63,
	   T4."Serviceberater" as c64,
	   T4."Auftragsart_Service" as c65,
	   T4."Umsatzart" as c66,
	   T3."Fahrzeugart" as c67,
	   T3."Verkäufer" as c68,
	   T2."Anrede" as c69,
	   T2."Name2" as c70,
	   T2."Name1" as c71,
	   T2."Vorname" as c72,
	   T2."Kundennummer" as c73,
	   T1."KOST2 - KOSTENSTELLE" as c74,
	   T1."KOST1 - KOSTENSTELLE" as c75,
	   T1."BUCHUNGSTEXT" as c76,
	   T1."BELEGFELD 2" as c77,
	   T1."FÄLLIG" as c78,
	   T1."SALDO" as c79,
	   T1."BETRAG HABEN" as c80,
	   T1."BETRAG SOLL" as c81,
	   T1."FÄLLIGKEIT" as c82,
	   T1."RECHNUNGSDATUM" as c83,
	   T1."RECHNUNGS-NR" as c84,
	   T1."NAME (ADRESSATTYP UNTERNEHMEN)" as c85
from ((("Opos.txt" T1 left outer join QSS."C:\GlobalCube\System\WERWISO\IQD\OP\Kunden_OP.ims" T2 on T1."KONTONUMMER" = T2."Kundennummer") left outer join QSS."C:\GlobalCube\System\WERWISO\IQD\OP\Rechnungen_FZG_Verkauf_fuer_OP_WERWISO_ims.ims" T3 on T1."RECHNUNGS-NR" = T3."RGnr_zeichen_1") left outer join QSS."C:\GlobalCube\System\WERWISO\IQD\OP\Service_Rechnungsausgangsbuch_SB_OP_ims.ims" T4 on T1."RECHNUNGS-NR" = T4."RGnr_Zeichen_1")
order by c48 desc
) D1

END SQL
COLUMN,0,Kontonummer
COLUMN,1,Name (adressattyp Unternehmen)
COLUMN,2,Rechnungs-nr
COLUMN,3,Rechnungsdatum
COLUMN,4,Fälligkeit
COLUMN,5,Betrag Soll
COLUMN,6,Betrag Haben
COLUMN,7,Saldo
COLUMN,8,Fällig
COLUMN,9,Belegfeld 2
COLUMN,10,Buchungstext
COLUMN,11,Kost1 - Kostenstelle
COLUMN,12,Kost2 - Kostenstelle
COLUMN,13,Kundennummer
COLUMN,14,Vorname
COLUMN,15,Name1
COLUMN,16,Name2
COLUMN,17,Anrede_KD
COLUMN,18,Verkäufer
COLUMN,19,Fahrzeugart
COLUMN,20,Umsatzart
COLUMN,21,Auftragsart Service
COLUMN,22,Serviceberater
COLUMN,23,Invoice Date
COLUMN,24,Hauptbetrieb_ID
COLUMN,25,Hauptbetrieb_Name
COLUMN,26,Standort_ber
COLUMN,27,Standort_ID
COLUMN,28,Standort_Name
COLUMN,29,Sel Name
COLUMN,30,Bereich_ori
COLUMN,31,Beleg
COLUMN,32,Bereich
COLUMN,33,Kostenstelle
COLUMN,34,Forderungsart
COLUMN,35,Abwarten
COLUMN,36,Mahnstufe
COLUMN,37,KD-Nr
COLUMN,38,Kunde_ori
COLUMN,39,Tage
COLUMN,40,Staffel
COLUMN,41,offen
COLUMN,42,Gesamt Offen Kd (Info)
COLUMN,43,Kunde
COLUMN,44,Rg-Datum