COGNOS QUERY STRUCTURE,1,1 DATABASE,EDS_1 DATASOURCENAME,C:\GAPS\Portal\System\IQD\Serv_Teile\Auftraege_EDS_Kandel.imr TITLE,Auftraege_EDS_Kandel.imr BEGIN SQL select c335 as c1, c334 as c2, c333 as c3, c332 as c4, c331 as c5, c330 as c6, c329 as c7, c328 as c8, c327 as c9, c326 as c10, c325 as c11, c324 as c12, c323 as c13, c322 as c14, c321 as c15, c320 as c16, c319 as c17, c318 as c18, c317 as c19, c316 as c20, c315 as c21, c236 as c22, c314 as c23, c313 as c24, c312 as c25, c311 as c26, c310 as c27, c309 as c28, c308 as c29, c307 as c30, c306 as c31, c305 as c32, c304 as c33, c303 as c34, c302 as c35, c301 as c36, c234 as c37, c300 as c38, c299 as c39, c298 as c40, c297 as c41, c296 as c42, c295 as c43, c294 as c44, c293 as c45, c292 as c46, c291 as c47, c290 as c48, c289 as c49, c288 as c50, c287 as c51, c286 as c52, c285 as c53, c284 as c54, c283 as c55, c282 as c56, c281 as c57, c280 as c58, c279 as c59, c278 as c60, c277 as c61, c276 as c62, c275 as c63, c274 as c64, c273 as c65, c272 as c66, c271 as c67, c206 as c68, c270 as c69, c269 as c70, c219 as c71, c268 as c72, c267 as c73, c266 as c74, c265 as c75, c264 as c76, c263 as c77, c262 as c78, c261 as c79, c260 as c80, c259 as c81, c258 as c82, c257 as c83, c256 as c84, c255 as c85, c254 as c86, c253 as c87, c252 as c88, c251 as c89, c250 as c90, ('1') as c91, c248 as c92, c248 as c93, c249 as c94, c248 as c95, c247 as c96, c246 as c97, c245 as c98, c244 as c99, c243 as c100, c242 as c101, c241 as c102, c240 as c103, c239 as c104, c238 as c105, c237 as c106, c236 as c107, c235 as c108, c234 as c109, c233 as c110, c233 as c111, c232 as c112, c231 as c113, c230 as c114, 'Service' as c115, c229 as c116, c228 as c117, c227 as c118, c226 as c119, c225 as c120, c224 as c121, c223 as c122, c222 as c123, c221 as c124, c220 as c125, c219 as c126, c218 as c127, c217 as c128, c216 as c129, c215 as c130, c214 as c131, c177 as c132, c213 as c133, c212 as c134, c211 as c135, c210 as c136, c209 as c137, c208 as c138, c207 as c139, c206 as c140, 1 as c141, c205 as c142, c204 as c143, c203 as c144, c202 as c145, c201 as c146, c200 as c147, c199 as c148, c198 as c149, c197 as c150, c196 as c151, c195 as c152, c194 as c153, c193 as c154, c192 as c155, c191 as c156, c190 as c157, c189 as c158, c188 as c159, c187 as c160, 1 as c161, XCOUNT(c317 for c177) as c162, 1 / (XCOUNT(c317 for c177)) as c163, c186 as c164, c185 as c165, (c185) / (XCOUNT(c317 for c177)) as c166, c184 as c167, c183 as c168, c182 as c169, c181 as c170, c180 as c171, c179 as c172, c178 as c173 from (select T3."Auftragsnr_" as c177, CASE WHEN ((T3."Art" = 4) and (T3."Produktbuchungsgruppe" IN ('F_MIETW','W_MIET_NST','W_MIETW'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) WHEN ((T3."Art" IN (0,3)) and (T3."Produktbuchungsgruppe" IN ('F_MIETW','W_MIET_NST','W_MIETW'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) ELSE (0) END as c178, 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 c179, (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 c180, 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 c181, CASE WHEN (T1."Interner Auftrag" = 1) THEN ('Interner Auftrag') ELSE ('Externer Auftrag') END as c182, T7."Erstzulassung" as c183, CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END as c184, (extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - (CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END))) as c185, CASE WHEN ((T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."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 c186, (((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name") || ' - ' || (T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."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 c187, 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) || ' - ' || T6."Name") || ' - ' || (T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."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) || ' - ' || T6."Name") || ' - ' || (T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."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 c188, (cdate((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) as c189, (dayofweek(((now())))) as c190, (now()) as c191, (cast_float(T8."Einstandspreis (durchschn_)")) as c192, (cast_float(T8."EK-Preis (neuester)")) as c193, (cast_float(T8."Einstandspreis")) as c194, T8."Einstandspreis (durchschn_)" as c195, T8."EK-Preis (neuester)" as c196, T8."Einstandspreis" as c197, T8."VK-Preis" as c198, T8."Basiseinheitencode" as c199, T8."Beschreibung" as c200, T8."Nr_" as c201, (cast_float(T3."Verrechnungsbetrag")) as c202, (CASE WHEN ((T3."Art" = 1) and (not T3."Produktbuchungsgruppe" IN ('F_NW','F_NW_ÜF','F_VFW','W_MECH_NL'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) ELSE (0) END) - (CASE WHEN (T3."Art" = 1) THEN (((cast_float(T3."Einstandsbetrag")))) ELSE (0) END) as c203, T3."Hauptdebitor" as c204, T3."Herkunftsnr_(Zahlung)" as c205, T3."Kundengruppencode" as c206, CASE WHEN (T3."Markencode" IN ('VOLKSWAGEN','VW')) THEN ('VW') WHEN (T3."Markencode" IN ('OPEL','RENAULT','SKODA','PSA')) THEN (T3."Markencode") ELSE ('FREMDMARKE') END as c207, CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END as c208, T1."Archivierungsgrund" as c209, T1."Arch_ durch Benutzer ID" as c210, T1."Archivierungsdatum" as c211, T9."Zeilennr_" as c212, T9."Belegnr_" as c213, CASE WHEN (((cast_float(T3."Menge pro Stunde"))) <> 0) THEN ((((cast_float(T3."AW Mont_ 1"))) + ((cast_float(T3."AW Mont_ 2")))) / ((cast_float(T3."Menge pro Stunde")))) ELSE (((cast_float(T3."AW Mont_ 1"))) + ((cast_float(T3."AW Mont_ 2")))) END as c214, CASE WHEN (((((cast_float(T3."Menge pro Stunde"))) <> 0) and ((CASE WHEN ((((((cast_float(T3."AW Mont_ 1"))) <> 0) and (T3."Produktbuchungsgruppe" <> 'W_DP')) and (T3."Menge" <> 0)) or (((((cast_float(T3."AW Mont_ 2"))) <> 0) and (T3."Produktbuchungsgruppe" <> 'W_DP')) and (T3."Menge" <> 0))) THEN (((cast_float(T3."Menge (Stunde)")))) ELSE (0) END) >= 0)) and (not T3."Nr_" LIKE 'HU-K%')) THEN (((cast_float(T3."Vorgabezeit"))) / ((cast_float(T3."Menge pro Stunde")))) WHEN (((((cast_float(T3."Menge pro Stunde"))) <> 0) and ((CASE WHEN ((((((cast_float(T3."AW Mont_ 1"))) <> 0) and (T3."Produktbuchungsgruppe" <> 'W_DP')) and (T3."Menge" <> 0)) or (((((cast_float(T3."AW Mont_ 2"))) <> 0) and (T3."Produktbuchungsgruppe" <> 'W_DP')) and (T3."Menge" <> 0))) THEN (((cast_float(T3."Menge (Stunde)")))) ELSE (0) END) < 0)) and (not T3."Nr_" LIKE 'HU-K%')) THEN (((cast_float(T3."Vorgabezeit"))) / ((cast_float(T3."Menge pro Stunde"))) * -1) ELSE (((cast_float(T3."Vorgabezeit")))) END as c215, CASE WHEN ((((((cast_float(T3."AW Mont_ 1"))) <> 0) and (T3."Produktbuchungsgruppe" <> 'W_DP')) and (T3."Menge" <> 0)) or (((((cast_float(T3."AW Mont_ 2"))) <> 0) and (T3."Produktbuchungsgruppe" <> 'W_DP')) and (T3."Menge" <> 0))) THEN (((cast_float(T3."Menge (Stunde)")))) ELSE (0) END as c216, (cast_float(T3."AW Mont_ 2")) as c217, (cast_float(T3."AW Mont_ 1")) as c218, T3."Vorgabezeitart" as c219, (cast_float(T3."Vorgabezeit")) as c220, (cast_float(T3."Menge (Stunde)")) as c221, (cast_float(T3."Menge pro Stunde")) as c222, CASE WHEN (T3."Art" IN (0,3)) THEN (((cast_float(T3."Einstandsbetrag")))) ELSE (0) END as c223, CASE WHEN (((CASE WHEN (T3."Art" = 1) THEN (((cast_float(T3."Einstandsbetrag")))) ELSE (0) END) = 0) and ((CASE WHEN (((T3."Art" = 4) and (not T3."Produktbuchungsgruppe" IN ('W_DP','W_MIET_NST','W_MIETW','F_MIETW'))) or (T3."Produktbuchungsgruppe" IN ('W_MECH_NL'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) ELSE (0) END) = 0)) THEN (((cast_float(T3."Menge"))) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Art" = 1) THEN (((cast_float(T3."Einstandsbetrag")))) ELSE (0) END)) END as c224, CASE WHEN (T3."Art" = 1) THEN (((cast_float(T3."Einstandsbetrag")))) ELSE (0) END as c225, CASE WHEN ((T3."Art" IN (0,3)) or (T3."Produktbuchungsgruppe" IN ('W_DP','W_MIET_NST','W_MIETW','F_NW','F_NW_ÜF','F_VFW'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) ELSE (0) END as c226, CASE WHEN ((T3."Art" = 1) and (not T3."Produktbuchungsgruppe" IN ('F_NW','F_NW_ÜF','F_VFW','W_MECH_NL'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) ELSE (0) END as c227, CASE WHEN (((T3."Art" = 4) and (not T3."Produktbuchungsgruppe" IN ('W_DP','W_MIET_NST','W_MIETW','F_MIETW'))) or (T3."Produktbuchungsgruppe" IN ('W_MECH_NL'))) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END)) ELSE (0) END as c228, CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('GAR_NST','GAR_STB','GEW_BAR','GEW_LEAS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((cast_float(T3."Verrechnungsbetrag")))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END)) END as c229, CASE WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('11','14','10')) THEN ('1') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('20')) THEN ('2') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('30','31','32','33')) THEN ('6') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('40','41','42')) THEN ('3') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('43')) THEN ('4') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('44')) THEN ('5') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" IN ('W_KARO')) THEN ('43') WHEN (T3."Produktbuchungsgruppe" IN ('W_LACK')) THEN ('44') ELSE (T3."Kostenstellencode") END) IN ('51')) THEN ('7') ELSE null END as c230, CASE WHEN (T3."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T3."Kostenstellencode" from 3 for 2))) END as c231, T3."Fahrgestellnummer" || ' - ' || T7."Modell" as c232, T7."Modell" as c233, T3."Markencode" as c234, (CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name" as c235, (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) as c236, T6."Nr_" || ' - ' || T6."Name" as c237, T6."Name" as c238, T6."Nr_" as c239, CASE WHEN (((T3."Herkunftsnr_" LIKE '%INT%') or ((CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END) LIKE '%INT%')) or ((CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END) LIKE '%INT%')) THEN ('Intern') WHEN ((T3."Belegtyp" = 2) and (((not T3."Herkunftsnr_" LIKE '%INT%') or (not (CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END) LIKE '%INT%')) or (not (CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END) LIKE '%INT%'))) THEN ('Extern') WHEN ((T3."Belegtyp" = 3) and (((not T3."Herkunftsnr_" LIKE '%INT%') or (not (CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END) LIKE '%INT%')) or (not (CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END) LIKE '%INT%'))) THEN ('GWL') ELSE null END as c240, CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END as c241, CASE WHEN (T3."Offen" = 0) THEN ((cast_float(T3."Verkaufsbetrag" * -1))) ELSE ((cast_float(T3."Verkaufsbetrag"))) END as c242, (cast_float(T3."Einstandsbetrag")) as c243, (cast_float(T3."Menge")) as c244, T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname" as c245, T5."Nachname" as c246, T5."Vorname" as c247, T4."Code" as c248, T4."Bezeichnung" as c249, T3."AW Mont_ 2" as c250, T3."Monteur Nr_ 2" as c251, T3."AW Mont_ 1" as c252, T3."Monteur Nr_ 1" as c253, T3."Tätigkeitsartcode" as c254, T3."Mitarbeiternr_" as c255, T3."Serviceberaternr_" as c256, T3."Betrag int_ verrechnet (BW)" as c257, T3."Betrag int_ verrechnet" as c258, T3."Betrag int_ zu verrechnen (BW)" as c259, T3."Betrag int_ zu verrechnen" as c260, T3."Offen" as c261, T3."Buchungsdatum (letzte Rechn_)" as c262, T3."Auftrag erledigt" as c263, T3."Verrechnungspreis fix" as c264, T3."Interne Verrechnung erfolgt" as c265, T3."Auftragszeilennr_" as c266, T3."Herkunftsnr_" as c267, T3."Herkunftsart" as c268, T3."Vorgabezeit" as c269, T3."Ist-Menge (Stunde)" as c270, T3."Positionsgruppencode" as c271, T3."Untergruppencode" as c272, T3."Hauptgruppencode" as c273, T3."Marktsegmentcode" as c274, T3."Fahrzeugstatus" as c275, T3."Fahrzeugartencode" as c276, T3."Belegtyp" as c277, T3."Artikelgruppencode" as c278, T3."Menge (Stunde)" as c279, T3."Menge pro Stunde" as c280, T3."Fahrgestellnummer" as c281, T3."Abgeschlossen" as c282, T3."Verrechnungsbetrag (BW)" as c283, T3."Verrechnungsbetrag" as c284, T3."Verrechnungspreis" as c285, T3."Kostenstellencode Verkauf" as c286, T3."Paketnr_" as c287, T3."Fahrzeugtyp" as c288, T3."Zeitart" as c289, T3."Buchnr_" as c290, T3."Storniert" as c291, T3."Nummernserie" as c292, T3."Belegdatum" as c293, T3."Produktbuchungsgruppe" as c294, T3."Geschäftsbuchungsgruppe" as c295, T3."Positiv" as c296, T3."Postenart" as c297, T3."Herkunftscode" as c298, T3."Benutzer ID" as c299, T3."Arbeitstypencode" as c300, T3."Kostenstellencode" as c301, T3."Werkstattbuchungsgruppe" as c302, T3."Fakturierbar" as c303, T3."Verkaufsbetrag" as c304, T3."VK-Preis" as c305, T3."Einstandsbetrag" as c306, T3."Einstandspreis" as c307, T3."EK-Preis" as c308, T3."Menge" as c309, T3."Beschreibung" as c310, T3."Nr_" as c311, T3."Art" as c312, T3."Belegnr_" as c313, CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END as c314, CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END as c315, CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END as c316, CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END as c317, CASE WHEN (T1."Belegart" IS NULL) THEN (T2."Belegart") ELSE (T1."Belegart") END as c318, T2."Filialcode" as c319, T1."Interne Belegnr_" as c320, T1."Interner Auftrag" as c321, T2."Interne Belegnr_" as c322, T2."Buchungsdatum" as c323, T2."Auftragsdatum" as c324, T2."Rech_ an Deb_-Nr_" as c325, T2."Verk_ an Deb_-Nr_" as c326, T2."Nr_" as c327, T2."Belegart" as c328, T1."Filialcode" as c329, T1."Buchungsdatum" as c330, T1."Auftragsdatum" as c331, T1."Rech_ an Deb_-Nr_" as c332, T1."Verk_ an Deb_-Nr_" as c333, T1."Nr_" as c334, T1."Belegart" as c335 from (((((((("DE0682"."dbo"."Tretter Kandel$Werkstattposten" T3 full outer join "DE0682"."dbo"."Tretter Kandel$Archiv_ Werkstattkopf" T1 on T3."Auftragsnr_" = T1."Nr_") full outer join "DE0682"."dbo"."Tretter Kandel$Werkstattkopf" T2 on T2."Nr_" = T3."Auftragsnr_") left outer join "DE0682"."dbo"."Tretter Kandel$Filialbezeichnung" T4 on T4."Code" = T3."Filialcode") left outer join "DE0682"."dbo"."Tretter Kandel$Employee" T5 on T5."Nr_" = T1."Serviceberaternr_") left outer join "DE0682"."dbo"."Tretter Kandel$Debitor" T6 on T3."Herkunftsnr_" = T6."Nr_") left outer join "DE0682"."dbo"."Tretter Kandel$Fahrzeug" T7 on T3."Fahrgestellnummer" = T7."Fahrgestellnummer") left outer join "DE0682"."dbo"."Tretter Kandel$Artikel" T8 on T3."Nr_" = T8."Nr_") left outer join "DE0682"."dbo"."Tretter Kandel$Archiv_ Werkstattzeile" T9 on (T3."Auftragsnr_" = T9."Belegnr_") and (T3."Auftragszeilennr_" = T9."Zeilennr_")) where ((((((((((T3."Art" <> 2) and (not T3."Belegnr_" LIKE 'G%')) and (T3."Abgeschlossen" = 1)) and (T3."Nummernserie" <> '')) and (T3."Offen" = 0)) and ((T3."Belegnr_" LIKE 'WVRG%') or (T3."Belegnr_" LIKE 'WSGG%'))) and (T3."Nummernserie" <> 'WSLIEFG')) or ((((((T3."Art" <> 2) and (not T3."Belegnr_" LIKE 'G%')) and (T3."Abgeschlossen" = 1)) and (T3."Nummernserie" <> '')) and (T3."Offen" = 1)) and (T3."Belegnr_" LIKE 'WVLG%'))) or (((((T3."Art" <> 2) and (not T3."Belegnr_" LIKE 'G%')) and (T3."Abgeschlossen" = 1)) and (T3."Offen" = 1)) and (T3."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')) ) 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,Belegnr COLUMN,24,Art COLUMN,25,APO_TEILE_Nr COLUMN,26,Beschreibung COLUMN,27,Menge_ori COLUMN,28,Ek-preis COLUMN,29,Einstandspreis COLUMN,30,Einstandsbetrag_ori COLUMN,31,Vk-preis COLUMN,32,Verkaufsbetrag_ori COLUMN,33,Fakturierbar COLUMN,34,Werkstattbuchungsgruppe COLUMN,35,Kostenstellencode COLUMN,36,Markencode COLUMN,37,Arbeitstypencode COLUMN,38,Benutzer Id COLUMN,39,Herkunftscode COLUMN,40,Postenart COLUMN,41,Positiv COLUMN,42,Geschäftsbuchungsgruppe COLUMN,43,Produktbuchungsgruppe COLUMN,44,Belegdatum COLUMN,45,Nummernserie COLUMN,46,Storniert COLUMN,47,Buchnr COLUMN,48,Zeitart COLUMN,49,Fahrzeugtyp COLUMN,50,Paketnr COLUMN,51,Kostenstellencode Verkauf COLUMN,52,Verrechnungspreis COLUMN,53,Verrechnungsbetrag_ori COLUMN,54,Verrechnungsbetrag (bw) COLUMN,55,Abgeschlossen COLUMN,56,Fahrgestellnummer COLUMN,57,Menge Pro Stunde COLUMN,58,Menge (stunde) COLUMN,59,Artikelgruppencode COLUMN,60,Belegtyp COLUMN,61,Fahrzeugartencode COLUMN,62,Fahrzeugstatus COLUMN,63,Marktsegmentcode COLUMN,64,Hauptgruppencode COLUMN,65,Untergruppencode COLUMN,66,Positionsgruppencode COLUMN,67,Kundengruppencode COLUMN,68,Ist-menge (stunde) COLUMN,69,Vorgabezeit COLUMN,70,Vorgabezeitart COLUMN,71,Herkunftsart COLUMN,72,Herkunftsnr COLUMN,73,Auftragszeilennr COLUMN,74,Interne Verrechnung Erfolgt COLUMN,75,Verrechnungspreis Fix COLUMN,76,Auftrag Erledigt COLUMN,77,Buchungsdatum (letzte Rechn ) COLUMN,78,Offen COLUMN,79,Betrag Int Zu Verrechnen COLUMN,80,Betrag Int Zu Verrechnen (bw) COLUMN,81,Betrag Int Verrechnet COLUMN,82,Betrag Int Verrechnet (bw) COLUMN,83,Serviceberaternr COLUMN,84,Mitarbeiternr COLUMN,85,Tätigkeitsartcode COLUMN,86,Monteur Nr 1 COLUMN,87,Aw Mont 1 COLUMN,88,Monteur Nr 2 COLUMN,89,Aw Mont 2 COLUMN,90,Hauptbetrieb COLUMN,91,Standort COLUMN,92,FIL-Code COLUMN,93,FIL-Bezeichnung COLUMN,94,Standort_1_ori COLUMN,95,Vorname COLUMN,96,Nachname COLUMN,97,Serviceberater COLUMN,98,Menge COLUMN,99,Einstandsbetrag COLUMN,100,Verkaufsbetrag COLUMN,101,Rech An Deb -nr COLUMN,102,Umsatzart_ori COLUMN,103,KD_Nr COLUMN,104,KD_Name COLUMN,105,Kunde COLUMN,106,Invoice Date COLUMN,107,Order Number COLUMN,108,Fabrikat COLUMN,109,Modell COLUMN,110,Model COLUMN,111,Fahrzeug COLUMN,112,KST_aus_Code COLUMN,113,Kostenstelle COLUMN,114,Auftragsart COLUMN,115,Lines Net Value COLUMN,116,Umsatz Lohn COLUMN,117,Umsatz Teile Service COLUMN,118,Umsatz Sonstiges COLUMN,119,Einsatz Teile Service_ori COLUMN,120,Einsatz Teile Service COLUMN,121,Einsatz Sonstiges COLUMN,122,Menge_pro_Stunde COLUMN,123,Menge_stunde COLUMN,124,Sollzeit COLUMN,125,Sollzeit_Art COLUMN,126,AW_Mont_1 COLUMN,127,AW_Mont_2 COLUMN,128,verk. Stunden COLUMN,129,Soll-Stunden COLUMN,130,benutzte Zeit_ori COLUMN,131,Auftragsnr COLUMN,132,A_Belegnr COLUMN,133,A_Zeilennr COLUMN,134,Archivierungsdatum COLUMN,135,Arch Durch Benutzer Id COLUMN,136,Archivierungsgrund COLUMN,137,Umsatzart COLUMN,138,Marke COLUMN,139,Kundenart COLUMN,140,DG_1 COLUMN,141,Herkunftsnr (zahlung) COLUMN,142,Hauptdebitor COLUMN,143,DB Teile COLUMN,144,Verr_Betrag COLUMN,145,Nr COLUMN,146,Beschreibung COLUMN,147,Basiseinheitencode COLUMN,148,Vk-preis COLUMN,149,Einstandspreis_ART COLUMN,150,Ek-preis (neuester)_ART COLUMN,151,Einstandspreis (durchschn )_ART COLUMN,152,Einstandspreis_Art COLUMN,153,EK_preis_neu_Art COLUMN,154,Einstandspreis_Durch_Art COLUMN,155,Heute COLUMN,156,Tag COLUMN,157,Buchungsdatum_Datum COLUMN,158,Rechnungsausgang COLUMN,159,Order_Number_RG_Ausg COLUMN,160,DG_1 COLUMN,161,DG_2 COLUMN,162,Durchgänge COLUMN,163,Standort_1 COLUMN,164,Tage bis Rechnung_ori COLUMN,165,Tage bis Rechnung COLUMN,166,Kostenstellecode_zwischen COLUMN,167,Erstzulassung COLUMN,168,Umsatzart_Auftrag COLUMN,169,Fahrzeugalter_Tage COLUMN,170,Fahrzeugalter_Jahr COLUMN,171,FZG-Altersstaffel COLUMN,172,Umsatz Mietw.