GAPS/Portal/System/IQD/Serv_Teile/Auftraege_EDS_benutzte_Zeit.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Serv_Teile\Auftraege_EDS_benutzte_Zeit.imr
TITLE,Auftraege_EDS_benutzte_Zeit.imr
BEGIN SQL
select c122 as c1,
	   c121 as c2,
	   c120 as c3,
	   c119 as c4,
	   c118 as c5,
	   c117 as c6,
	   c116 as c7,
	   c115 as c8,
	   c114 as c9,
	   c113 as c10,
	   c112 as c11,
	   c111 as c12,
	   c110 as c13,
	   c109 as c14,
	   c108 as c15,
	   c107 as c16,
	   c106 as c17,
	   c105 as c18,
	   c104 as c19,
	   c103 as c20,
	   c102 as c21,
	   c94 as c22,
	   c101 as c23,
	   ('1') as c24,
	   c99 as c25,
	   c99 as c26,
	   c100 as c27,
	   c99 as c28,
	   c98 as c29,
	   c97 as c30,
	   c96 as c31,
	   c95 as c32,
	   c94 as c33,
	   c93 as c34,
	   c92 as c35,
	   c91 as c36,
	   c91 as c37,
	   c90 as c38,
	   c89 as c39,
	   c88 as c40,
	   'Service' as c41,
	   (c87) / (XCOUNT(c104 for c67)) as c42,
	   c86 as c43,
	   c85 as c44,
	   c84 as c45,
	   c83 as c46,
	   c82 as c47,
	   1 as c48,
	   c81 as c49,
	   c80 as c50,
	   c79 as c51,
	   1 as c52,
	   XCOUNT(c104 for c67) as c53,
	   c78 as c54,
	   c77 as c55,
	   c76 as c56,
	   c75 as c57,
	   c74 as c58,
	   c73 as c59,
	   c72 as c60,
	   c71 as c61,
	   c70 as c62,
	   c69 as c63,
	   c68 as c64
from 
(select (T6."Auftragsnr_" || T8."Employee No_" || T8."Time Account No_") as c67,
	   CASE WHEN (((CASE WHEN (T7."Erstzulassung" >= TIMESTAMP '1920-01-01 00:00:00.000') THEN ((extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - T7."Erstzulassung"))) ELSE (0) END) / 365) BETWEEN -2 AND 3) THEN ('0 - 3 Jahre') WHEN (((CASE WHEN (T7."Erstzulassung" >= TIMESTAMP '1920-01-01 00:00:00.000') THEN ((extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - T7."Erstzulassung"))) ELSE (0) END) / 365) BETWEEN 3.000001 AND 8) THEN ('4 - 8 Jahre') WHEN (((CASE WHEN (T7."Erstzulassung" >= TIMESTAMP '1920-01-01 00:00:00.000') THEN ((extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - T7."Erstzulassung"))) ELSE (0) END) / 365) > 8) THEN ('> 9 Jahre') WHEN (((CASE WHEN (T7."Erstzulassung" >= TIMESTAMP '1920-01-01 00:00:00.000') THEN ((extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - T7."Erstzulassung"))) ELSE (0) END) / 365) = 0) THEN ('keine Angabe') ELSE null END as c68,
	   (CASE WHEN (T7."Erstzulassung" >= TIMESTAMP '1920-01-01 00:00:00.000') THEN ((extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - T7."Erstzulassung"))) ELSE (0) END) / 365 as c69,
	   CASE WHEN (T7."Erstzulassung" >= TIMESTAMP '1920-01-01 00:00:00.000') THEN ((extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - T7."Erstzulassung"))) ELSE (0) END as c70,
	   CASE WHEN (T1."Interner Auftrag" = 1) THEN ('Interner Auftrag') ELSE ('Externer Auftrag') END as c71,
	   T7."Erstzulassung" as c72,
	   CASE WHEN ((T1."Serviceberaternr_" || ' - ' || T4."Vorname" || ' ' || T4."Nachname") IN ('28 - Michael Gißke','69 - Jan Conradi','18 - Jessica Grunert','256 - Michael Scherer','31 - Olaf Wozniak','39 - Thomas Friedrich','15 - Henry Kingler','245 - Marcel Stoof','12 - Dominik Scherz','58 - Gardo Brieseck','111 - Fred Frenzel','258 - Lutz Pittelkow','71 - Dirk Jagelmann','143 - Volker Hellie','142 - Peter Löchel','147 - Helmer Schröter','195 - Marcel Liebers','145 - Olaf Gladewitz','144 - Joachim Riedel','146 - Klaus Schmiedel','189 - Carolin Hof','103 - Petra Recklies','192 - Leif Haseloff','190 - Katja Kanzenbach','139 - Klaus-Dieter Klitsch','133 - Jeanette Paasch','140 - Heike Ihms','134 - Nadine Reiter')) THEN ('Serviceberater') ELSE ('Serviceberater') END as c73,
	   T8."Time Account Value" as c74,
	   T8."Time Account No_" as c75,
	   T8."Order No_" as c76,
	   T8."Current Date" as c77,
	   T8."Employee No_" as c78,
	   (((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T5."Name" || ' - ' || T1."Rech_ an Deb_-Nr_") || ' - ' || (T1."Serviceberaternr_" || ' - ' || T4."Vorname" || ' ' || T4."Nachname") || ' - ' || (asciiz(extract(YEAR FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),4) || '-' || asciiz(extract(MONTH FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2) || '-' || asciiz(extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2))) as c79,
	   CASE WHEN (((dayofweek((now()))) = 1) and ((extract(DAY FROM (now()) - (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) IN (2,3,4,5,6,7,8))) THEN (((((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T5."Name" || ' - ' || T1."Rech_ an Deb_-Nr_") || ' - ' || (T1."Serviceberaternr_" || ' - ' || T4."Vorname" || ' ' || T4."Nachname") || ' - ' || (asciiz(extract(YEAR FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),4) || '-' || asciiz(extract(MONTH FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2) || '-' || asciiz(extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2))))) WHEN (((dayofweek((now()))) IN (2,3,4,5,6,7)) and ((extract(DAY FROM (now()) - (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) IN (1,2,3,4,5,6,7))) THEN (((((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T5."Name" || ' - ' || T1."Rech_ an Deb_-Nr_") || ' - ' || (T1."Serviceberaternr_" || ' - ' || T4."Vorname" || ' ' || T4."Nachname") || ' - ' || (asciiz(extract(YEAR FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),4) || '-' || asciiz(extract(MONTH FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2) || '-' || asciiz(extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2))))) ELSE null END as c80,
	   (cdate((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) as c81,
	   T6."Kundengruppencode" as c82,
	   CASE WHEN (T6."Markencode" IN ('OPEL','RENAULT','SKODA','VOLKSWAGEN','VW')) THEN (T6."Markencode") ELSE ('FREMDMARKE') END as c83,
	   CASE WHEN (T6."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T6."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') ELSE ('Extern') END as c84,
	   T9."Belegnr_" as c85,
	   T6."Auftragsnr_" as c86,
	   cast_float(T8."Time Account Value") as c87,
	   CASE WHEN (T6."Kostenstellencode" LIKE '1%') THEN ('1') WHEN (T6."Kostenstellencode" LIKE '2%') THEN ('2') WHEN (T6."Kostenstellencode" IN ('30','31')) THEN ('6') WHEN (T6."Kostenstellencode" IN ('40')) THEN ('3') WHEN (T6."Kostenstellencode" = '41') THEN ('4') WHEN (T6."Kostenstellencode" = '42') THEN ('5') WHEN (T6."Kostenstellencode" LIKE '5%') THEN ('7') WHEN (T6."Kostenstellencode" LIKE '9%') THEN ('0') ELSE null END as c88,
	   CASE WHEN (T6."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T6."Kostenstellencode" from 3 for 2))) END as c89,
	   T6."Fahrgestellnummer" || ' - ' || T7."Modell" as c90,
	   T7."Modell" as c91,
	   T6."Markencode" as c92,
	   (CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T5."Name" || ' - ' || T1."Rech_ an Deb_-Nr_" as c93,
	   (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) as c94,
	   T5."Nr_" || ' - ' || T5."Name" as c95,
	   T1."Serviceberaternr_" || ' - ' || T4."Vorname" || ' ' || T4."Nachname" as c96,
	   T4."Nachname" as c97,
	   T4."Vorname" as c98,
	   T3."Code" as c99,
	   T3."Bezeichnung" as c100,
	   CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END as c101,
	   CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END as c102,
	   CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END as c103,
	   CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END as c104,
	   CASE WHEN (T1."Belegart" IS NULL) THEN (T2."Belegart") ELSE (T1."Belegart") END as c105,
	   T2."Filialcode" as c106,
	   T1."Interne Belegnr_" as c107,
	   T1."Interner Auftrag" as c108,
	   T2."Interne Belegnr_" as c109,
	   T2."Buchungsdatum" as c110,
	   T2."Auftragsdatum" as c111,
	   T2."Rech_ an Deb_-Nr_" as c112,
	   T2."Verk_ an Deb_-Nr_" as c113,
	   T2."Nr_" as c114,
	   T2."Belegart" as c115,
	   T1."Filialcode" as c116,
	   T1."Buchungsdatum" as c117,
	   T1."Auftragsdatum" as c118,
	   T1."Rech_ an Deb_-Nr_" as c119,
	   T1."Verk_ an Deb_-Nr_" as c120,
	   T1."Nr_" as c121,
	   T1."Belegart" as c122
from (((((((("DE0682"."dbo"."Tretter Bad Bergzabern$Werkstattposten" T6 full outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Archiv_ Werkstattkopf" T1 on T6."Auftragsnr_" = T1."Nr_") full outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Werkstattkopf" T2 on T2."Nr_" = T6."Auftragsnr_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Filialbezeichnung" T3 on T3."Code" = T6."Filialcode") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Employee" T4 on T4."Nr_" = T1."Serviceberaternr_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Debitor" T5 on T6."Herkunftsnr_" = T5."Nr_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Fahrzeug" T7 on T6."Fahrgestellnummer" = T7."Fahrgestellnummer") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Time Entry_T" T8 on T1."Nr_" = T8."Order No_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Archiv_ Werkstattzeile" T9 on (T6."Auftragsnr_" = T9."Belegnr_") and (T6."Auftragszeilennr_" = T9."Zeilennr_"))
where (((((((((((T6."Art" <> 2) and (not T6."Belegnr_" LIKE 'G%')) and (T6."Abgeschlossen" = 1)) and (T6."Nummernserie" <> '')) and (T6."Offen" = 0)) and ((T6."Belegnr_" LIKE 'WVRG%') or (T6."Belegnr_" LIKE 'WSGG%'))) and (T6."Nummernserie" <> 'WSLIEFG')) or ((((((T6."Art" <> 2) and (not T6."Belegnr_" LIKE 'G%')) and (T6."Abgeschlossen" = 1)) and (T6."Nummernserie" <> '')) and (T6."Offen" = 1)) and (T6."Belegnr_" LIKE 'WVLG%'))) or (((((T6."Art" <> 2) and (not T6."Belegnr_" LIKE 'G%')) and (T6."Abgeschlossen" = 1)) and (T6."Offen" = 1)) and (T6."Belegnr_" LIKE 'WVAN%'))) and (((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) >= TIMESTAMP '2012-01-01 00:00:00.000')) and (T8."Time Account No_" IN ('6121','6122','6123','6130')))
) D1
order by c19 asc

END SQL
COLUMN,0,A_Belegart
COLUMN,1,A_Nr
COLUMN,2,A_Verk An Deb -nr
COLUMN,3,A_Rech An Deb -nr
COLUMN,4,A_Auftragsdatum
COLUMN,5,A_Buchungsdatum
COLUMN,6,A_Filialcode
COLUMN,7,B_Belegart
COLUMN,8,B_Nr
COLUMN,9,B_Verk An Deb -nr
COLUMN,10,B_Rech An Deb -nr
COLUMN,11,B_Auftragsdatum
COLUMN,12,B_Buchungsdatum
COLUMN,13,B_Interne Belegnr
COLUMN,14,A_Interner Auftrag
COLUMN,15,A_Interne Belegnr
COLUMN,16,B_Filialcode
COLUMN,17,Belegart
COLUMN,18,Nr
COLUMN,19,Verk An Deb -nr
COLUMN,20,Auftragsdatum
COLUMN,21,Buchungsdatum
COLUMN,22,Filialcode
COLUMN,23,Hauptbetrieb
COLUMN,24,Standort
COLUMN,25,FIL-Code
COLUMN,26,FIL-Bezeichnung
COLUMN,27,Standort_1_ori
COLUMN,28,Vorname
COLUMN,29,Nachname
COLUMN,30,Serviceberater
COLUMN,31,Kunde
COLUMN,32,Invoice Date
COLUMN,33,Order Number
COLUMN,34,Fabrikat
COLUMN,35,Modell
COLUMN,36,Model
COLUMN,37,Fahrzeug
COLUMN,38,KST_aus_Code
COLUMN,39,Kostenstelle
COLUMN,40,Auftragsart
COLUMN,41,benutzte Zeit
COLUMN,42,Auftragsnr
COLUMN,43,A_Belegnr
COLUMN,44,Umsatzart
COLUMN,45,Marke
COLUMN,46,Kundenart
COLUMN,47,DG_1
COLUMN,48,Buchungsdatum_Datum
COLUMN,49,Rechnungsausgang
COLUMN,50,Order_Number_RG_Ausg
COLUMN,51,DG_1
COLUMN,52,DG_2
COLUMN,53,Employee No
COLUMN,54,Current Date
COLUMN,55,Order No
COLUMN,56,Time Account No
COLUMN,57,Time Account Value
COLUMN,58,Standort_1
COLUMN,59,Erstzulassung
COLUMN,60,Umsatzart_Auftrag
COLUMN,61,Fahrzeugalter_Tage
COLUMN,62,Fahrzeugalter_Jahr
COLUMN,63,FZG-Altersstaffel