GAPS/Portal/System/IQD/Zeiten/Auftraege_EDS_benutzte_Zeit_Kandel.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Serv_Teile\Auftraege_EDS_benutzte_Zeit_Kandel.imr
TITLE,Auftraege_EDS_benutzte_Zeit_Kandel.imr
BEGIN SQL
select c124 as c1,
	   c123 as c2,
	   c122 as c3,
	   c121 as c4,
	   c120 as c5,
	   c119 as c6,
	   c118 as c7,
	   c117 as c8,
	   c116 as c9,
	   c115 as c10,
	   c114 as c11,
	   c113 as c12,
	   c112 as c13,
	   c111 as c14,
	   c110 as c15,
	   c109 as c16,
	   c108 as c17,
	   c107 as c18,
	   c106 as c19,
	   c105 as c20,
	   c104 as c21,
	   c96 as c22,
	   c103 as c23,
	   ('1') as c24,
	   c101 as c25,
	   c101 as c26,
	   c102 as c27,
	   c101 as c28,
	   c100 as c29,
	   c99 as c30,
	   c98 as c31,
	   c97 as c32,
	   c96 as c33,
	   c95 as c34,
	   c94 as c35,
	   c93 as c36,
	   c93 as c37,
	   c92 as c38,
	   c91 as c39,
	   c90 as c40,
	   'Service' as c41,
	   (c89) / (XCOUNT(c106 for c68)) as c42,
	   c88 as c43,
	   c87 as c44,
	   c86 as c45,
	   c85 as c46,
	   c84 as c47,
	   1 as c48,
	   c83 as c49,
	   c82 as c50,
	   c81 as c51,
	   1 as c52,
	   XCOUNT(c106 for c68) as c53,
	   c80 as c54,
	   c79 as c55,
	   c78 as c56,
	   c77 as c57,
	   c76 as c58,
	   c75 as c59,
	   c74 as c60,
	   c73 as c61,
	   c72 as c62,
	   c71 as c63,
	   c70 as c64,
	   c69 as c65
from 
(select (T6."Auftragsnr_" || T8."Employee No_" || T8."Time Account No_") as c68,
	   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 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) / 365 as c70,
	   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 c71,
	   T7."Erstzulassung" as c72,
	   CASE WHEN (T1."Interner Auftrag" = 1) THEN ('Interner Auftrag') ELSE ('Externer Auftrag') END as c73,
	   CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END as c74,
	   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 c75,
	   T8."Time Account Value" as c76,
	   T8."Time Account No_" as c77,
	   T8."Order No_" as c78,
	   T8."Current Date" as c79,
	   T8."Employee No_" as c80,
	   (((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 c81,
	   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 c82,
	   (cdate((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) as c83,
	   T6."Kundengruppencode" as c84,
	   CASE WHEN (T6."Markencode" IN ('OPEL','RENAULT','SKODA','VOLKSWAGEN','VW')) THEN (T6."Markencode") ELSE ('FREMDMARKE') END as c85,
	   CASE WHEN (T6."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T6."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END as c86,
	   T9."Belegnr_" as c87,
	   T6."Auftragsnr_" as c88,
	   cast_float(T8."Time Account Value") as c89,
	   CASE WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('11','14','10')) THEN ('1') WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('20')) THEN ('2') WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('30','31','32','33')) THEN ('6') WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('40','41','42')) THEN ('3') WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('43')) THEN ('4') WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('44')) THEN ('5') WHEN ((CASE WHEN (T6."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T6."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T6."Kostenstellencode") END) IN ('51')) THEN ('7') ELSE null END as c90,
	   CASE WHEN (T6."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T6."Kostenstellencode" from 3 for 2))) END as c91,
	   T6."Fahrgestellnummer" || ' - ' || T7."Modell" as c92,
	   T7."Modell" as c93,
	   T6."Markencode" as c94,
	   (CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T5."Name" || ' - ' || T1."Rech_ an Deb_-Nr_" as c95,
	   (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) as c96,
	   T5."Nr_" || ' - ' || T5."Name" as c97,
	   T1."Serviceberaternr_" || ' - ' || T4."Vorname" || ' ' || T4."Nachname" as c98,
	   T4."Nachname" as c99,
	   T4."Vorname" as c100,
	   T3."Code" as c101,
	   T3."Bezeichnung" as c102,
	   CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END as c103,
	   CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END as c104,
	   CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END as c105,
	   CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END as c106,
	   CASE WHEN (T1."Belegart" IS NULL) THEN (T2."Belegart") ELSE (T1."Belegart") END as c107,
	   T2."Filialcode" as c108,
	   T1."Interne Belegnr_" as c109,
	   T1."Interner Auftrag" as c110,
	   T2."Interne Belegnr_" as c111,
	   T2."Buchungsdatum" as c112,
	   T2."Auftragsdatum" as c113,
	   T2."Rech_ an Deb_-Nr_" as c114,
	   T2."Verk_ an Deb_-Nr_" as c115,
	   T2."Nr_" as c116,
	   T2."Belegart" as c117,
	   T1."Filialcode" as c118,
	   T1."Buchungsdatum" as c119,
	   T1."Auftragsdatum" as c120,
	   T1."Rech_ an Deb_-Nr_" as c121,
	   T1."Verk_ an Deb_-Nr_" as c122,
	   T1."Nr_" as c123,
	   T1."Belegart" as c124
from (((((((("DE0682"."dbo"."Tretter Kandel$Werkstattposten" T6 full outer join "DE0682"."dbo"."Tretter Kandel$Archiv_ Werkstattkopf" T1 on T6."Auftragsnr_" = T1."Nr_") full outer join "DE0682"."dbo"."Tretter Kandel$Werkstattkopf" T2 on T2."Nr_" = T6."Auftragsnr_") left outer join "DE0682"."dbo"."Tretter Kandel$Filialbezeichnung" T3 on T3."Code" = T6."Filialcode") left outer join "DE0682"."dbo"."Tretter Kandel$Employee" T4 on T4."Nr_" = T1."Serviceberaternr_") left outer join "DE0682"."dbo"."Tretter Kandel$Debitor" T5 on T6."Herkunftsnr_" = T5."Nr_") left outer join "DE0682"."dbo"."Tretter Kandel$Fahrzeug" T7 on T6."Fahrgestellnummer" = T7."Fahrgestellnummer") left outer join "DE0682"."dbo"."Tretter Kandel$Time Entry_T" T8 on T1."Nr_" = T8."Order No_") left outer join "DE0682"."dbo"."Tretter Kandel$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,c57 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,Kostenstellecode_zwischen
COLUMN,60,Umsatzart_Auftrag
COLUMN,61,Erstzulassung
COLUMN,62,Fahrzeugalter_Tage
COLUMN,63,Fahrzeugalter_Jahr
COLUMN,64,FZG-Altersstaffel