COGNOS QUERY STRUCTURE,1,1 DATABASE,GC_CARLO DATASOURCENAME,C:\GlobalCube\System\CARLO\IQD\serv_teile\auftraege_eds_intern_c11.imr TITLE,auftraege_eds_intern_c11.imr BEGIN SQL select c291 as c1, c290 as c2, c289 as c3, c213 as c4, c288 as c5, c287 as c6, c286 as c7, c285 as c8, c284 as c9, c283 as c10, c282 as c11, c281 as c12, c280 as c13, c208 as c14, c207 as c15, c279 as c16, c278 as c17, c277 as c18, c276 as c19, c251 as c20, c275 as c21, c274 as c22, c250 as c23, c273 as c24, c272 as c25, c271 as c26, c270 as c27, c199 as c28, c269 as c29, c268 as c30, c197 as c31, c267 as c32, c266 as c33, c265 as c34, c264 as c35, c237 as c36, c236 as c37, c263 as c38, c262 as c39, c235 as c40, c261 as c41, c260 as c42, c259 as c43, c258 as c44, c233 as c45, c257 as c46, c232 as c47, '1' as c48, c254 as c49, c256 as c50, c255 as c51, c254 as c52, c253 as c53, c252 as c54, c206 as c55, c251 as c56, c250 as c57, c249 as c58, c248 as c59, c247 as c60, c246 as c61, c245 as c62, c196 as c63, c208 as c64, c244 as c65, c199 as c66, c243 as c67, c243 as c68, c242 as c69, c241 as c70, 'Service' as c71, c240 as c72, c178 as c73, c177 as c74, c175 as c75, c239 as c76, c176 as c77, c238 as c78, c237 as c79, c236 as c80, c235 as c81, c234 as c82, c233 as c83, c232 as c84, c174 as c85, c231 as c86, c230 as c87, c172 as c88, c229 as c89, c205 as c90, c200 as c91, c228 as c92, 1 as c93, c227 as c94, c226 as c95, c181 as c96, c225 as c97, c224 as c98, c223 as c99, c222 as c100, c221 as c101, c220 as c102, c222 as c103, c221 as c104, c220 as c105, c219 as c106, c218 as c107, c217 as c108, c216 as c109, c215 as c110, 1 as c111, XCOUNT(c282 for c172) as c112, 1 / (XCOUNT(c282 for c172)) as c113, ('Serviceberater') as c114, c214 as c115, (c214) / (XCOUNT(c282 for c172)) as c116, c213 as c117, c212 as c118, c211 as c119, c210 as c120, c209 as c121, c208 as c122, '1' as c123, c207 as c124, 'Serviceberater' as c125, c206 as c126, c205 as c127, c204 as c128, '' as c129, c203 as c130, c202 as c131, c201 as c132, c200 as c133, c199 as c134, c198 as c135, c197 as c136, c196 as c137, c195 as c138, c194 as c139, c194 as c140, c189 as c141, c193 as c142, c192 as c143, c191 as c144, c190 as c145, c189 as c146, c188 as c147, c187 as c148, c186 as c149, c185 as c150, c185 as c151, c181 as c152, c184 as c153, c183 as c154, c182 as c155, c181 as c156, c180 as c157, 'Rechnung' as c158, c179 as c159, 'abgerechnet' as c160, 'Service' as c161, c178 as c162, c177 as c163, c176 as c164, c175 as c165, c174 as c166, c173 as c167, (1 / (XCOUNT(c282 for c172))) as c168 from (select T3."Auftragsnr_" as c172, CASE WHEN (T3."Art" = 1) THEN (((T3."Menge"))) ELSE (0) END as c173, (CASE WHEN (((not T3."Nr_" LIKE 'HU-K%') and (not T3."Produktbuchungsgruppe" IN ('W_DP'))) and (not T3."Nr_" IN ('E002540'))) THEN (((T3."Menge (Stunde)"))) ELSE (0) END) as c174, (CASE WHEN (((not T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (not T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T3."Nr_" = 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) as c175, (CASE WHEN ((((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END) = 0) and ((CASE WHEN ((T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) = 0)) and ((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) <> 0)) THEN (((T3."Menge")) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END)) END) as c176, (CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) as c177, (CASE WHEN ((T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) as c178, CASE WHEN (((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) - (CASE WHEN ((((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END) = 0) and ((CASE WHEN ((T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) = 0)) and ((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) <> 0)) THEN (((T3."Menge")) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END)) END)) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as c179, CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%LOHN%') THEN (T3."Produktbuchungsgruppe") ELSE null END as c180, T8."Nr_" as c181, T8."GMPD Code" || T8."GMPD Subcode" as c182, T8."GMPD Subcode" as c183, T8."GMPD Code" as c184, (CASE WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 c185, (CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 c186, CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-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 c187, T7."Erstzulassung" as c188, T6."PLZ Code" as c189, (substring(T6."PLZ Code" from 1 for 4)) as c190, (substring(T6."PLZ Code" from 1 for 3)) as c191, (substring(T6."PLZ Code" from 1 for 2)) as c192, (substring(T6."PLZ Code" from 1 for 1)) as c193, (CASE WHEN (((T10."Ländercode" IS NULL) or (T10."Ländercode" = ' ')) or (T10."Ländercode" IN ('D','DE'))) THEN ('Deutschland') ELSE ('Ausland') END) as c194, T6."Ländercode" as c195, (T6."Nr_" || ' - ' || T6."Name") as c196, T3."Geschäftsbuchungsgruppe" as c197, (substring((upper(T7."Modell")) from 1 for 3)) as c198, T3."Markencode" as c199, (CASE WHEN (T3."Markencode" IN ('OPEL','VW')) THEN (T3."Markencode") ELSE ('Fremdfabrikat') END) as c200, T3."Kostenstellencode" || ' - ' || T9."Name" as c201, CASE WHEN ((extract(DAY FROM (getdate()) - ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)))) <= 30) THEN ((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T3."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) || ' - ' || (T6."Nr_" || ' - ' || T6."Name")) ELSE ('Rechnungen älter 30 Tage') END as c202, CASE WHEN ((extract(DAY FROM (getdate()) - (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) <= 30) THEN ('Intern') ELSE ('Aufträge älter 30 Tage') END as c203, CASE WHEN ((extract(DAY FROM (getdate()) - ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)))) <= 100) THEN (((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name")) ELSE ('Aufträge älter 100 Tage') END as c204, (CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) as c205, (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T3."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) as c206, (CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END) as c207, ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) as c208, CASE WHEN (T5."Funktion" IN ('Service Berater','Serviceberater','Serviceverater')) THEN ('Serviceberater') ELSE ('sonstige MA') END as c209, T5."Funktion" as c210, T3."Herkunftsnr_(Zahlung)" as c211, T1."Rech_ an Name" as c212, T1."Rech_ an Deb_-Nr_" as c213, (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 c214, (((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name") || ' - ' || (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T3."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) || ' - ' || (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 c215, CASE WHEN ((((datepart({weekday},((getdate()))))) = 1) and ((extract(DAY FROM ((getdate())) - (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") || ' - ' || (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T3."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) || ' - ' || (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 ((((datepart({weekday},((getdate()))))) IN (2,3,4,5,6,7)) and ((extract(DAY FROM ((getdate())) - (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") || ' - ' || (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T3."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) || ' - ' || (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 c216, (cdate((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) as c217, (datepart({weekday},((getdate())))) as c218, (getdate()) as c219, (T8."Einstandspreis (durchschn_)") as c220, (T8."EK-Preis (neuester)") as c221, (T8."Einstandspreis") as c222, T8."VK-Preis" as c223, T8."Basiseinheitencode" as c224, T8."Beschreibung" as c225, (T3."Verrechnungsbetrag") as c226, (CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) - (CASE WHEN ((((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END) = 0) and ((CASE WHEN ((T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) = 0)) and ((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) <> 0)) THEN (((T3."Menge")) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END)) END) as c227, T3."Kundengruppencode" as c228, T1."Archivierungsdatum" as c229, CASE WHEN (((T3."Menge pro Stunde")) <> 0) THEN ((((T3."AW Mont_ 1")) + ((T3."AW Mont_ 2"))) / ((T3."Menge pro Stunde"))) ELSE (((T3."AW Mont_ 1")) + ((T3."AW Mont_ 2"))) END as c230, CASE WHEN (((((T3."Menge pro Stunde")) <> 0) and ((CASE WHEN (((not T3."Nr_" LIKE 'HU-K%') and (not T3."Produktbuchungsgruppe" IN ('W_DP'))) and (not T3."Nr_" IN ('E002540'))) THEN (((T3."Menge (Stunde)"))) ELSE (0) END) >= 0)) and (not T3."Nr_" LIKE 'HU-K%')) THEN (((T3."Vorgabezeit")) / ((T3."Menge pro Stunde"))) WHEN (((((T3."Menge pro Stunde")) <> 0) and ((CASE WHEN (((not T3."Nr_" LIKE 'HU-K%') and (not T3."Produktbuchungsgruppe" IN ('W_DP'))) and (not T3."Nr_" IN ('E002540'))) THEN (((T3."Menge (Stunde)"))) ELSE (0) END) < 0)) and (not T3."Nr_" LIKE 'HU-K%')) THEN (((T3."Vorgabezeit")) / ((T3."Menge pro Stunde")) * -1) ELSE (((T3."Vorgabezeit"))) END as c231, (T3."AW Mont_ 2") as c232, (T3."AW Mont_ 1") as c233, T3."Vorgabezeitart" as c234, (T3."Vorgabezeit") as c235, (T3."Menge (Stunde)") as c236, (T3."Menge pro Stunde") as c237, CASE WHEN (((not T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE')) and (not T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T3."Nr_" = 'E002540')) THEN (((T3."Einstandsbetrag"))) ELSE (0) END as c238, CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END as c239, CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END as c240, CASE WHEN (T3."Kostenstellencode" LIKE '1%') THEN ('1') WHEN (T3."Kostenstellencode" LIKE '2%') THEN ('2') WHEN (T3."Kostenstellencode" IN ('30','31','60')) THEN ('6') WHEN (T3."Kostenstellencode" IN ('40','62')) THEN ('3') WHEN (T3."Kostenstellencode" = '41') THEN ('4') WHEN (T3."Kostenstellencode" = '42') THEN ('5') WHEN (T3."Kostenstellencode" LIKE '7%') THEN ('7') WHEN (T3."Kostenstellencode" LIKE '9%') THEN ('0') ELSE null END as c241, T3."Fahrgestellnummer" || ' - ' || T7."Modell" as c242, T7."Modell" as c243, (CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name" as c244, T6."Name" as c245, T6."Nr_" as c246, 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 c247, CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END as c248, CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END as c249, (T3."Einstandsbetrag") as c250, (T3."Menge") as c251, T5."Nachname" as c252, T5."Vorname" as c253, (CASE WHEN ('1' = '2') THEN ('20') ELSE ((CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END)) END) as c254, T4."Bezeichnung" as c255, T4."Code" as c256, T3."Monteur Nr_ 2" as c257, T3."Monteur Nr_ 1" as c258, T3."Serviceberaternr_" as c259, T3."Auftragszeilennr_" as c260, T3."Herkunftsnr_" as c261, T3."Fahrzeugartencode" as c262, T3."Artikelgruppencode" as c263, T3."Fahrgestellnummer" as c264, T3."Nummernserie" as c265, T3."Belegdatum" as c266, T3."Produktbuchungsgruppe" as c267, T3."Herkunftscode" as c268, T3."Benutzer ID" as c269, T3."Kostenstellencode" as c270, T3."Werkstattbuchungsgruppe" as c271, T3."Verkaufsbetrag" as c272, T3."VK-Preis" as c273, T3."Einstandspreis" as c274, T3."EK-Preis" as c275, T3."Beschreibung" as c276, T3."Nr_" as c277, T3."Art" as c278, T3."Belegnr_" as c279, CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END as c280, CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END as c281, CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END as c282, CASE WHEN (T1."Belegart" IS NULL) THEN (T2."Belegart") ELSE (T1."Belegart") END as c283, T1."Interne Belegnr_" as c284, T1."Interner Auftrag" as c285, T1."Filialcode" as c286, T1."Buchungsdatum" as c287, T1."Auftragsdatum" as c288, T1."Verk_ an Deb_-Nr_" as c289, T1."Nr_" as c290, T1."Belegart" as c291 from (((((((((("CARLO"."import"."Werkstattposten" T3 full outer join "CARLO"."import"."Archiv_Werkstattkopf" T1 on (T3."Auftragsnr_" = T1."Nr_") and (T3."Client_DB" = T1."Client_DB")) full outer join "CARLO"."import"."Werkstattkopf" T2 on (T2."Nr_" = T3."Auftragsnr_") and (T2."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T4 on (T4."Code" = T3."Filialcode") and (T4."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Employee" T5 on (T5."Nr_" = T3."Serviceberaternr_") and (T5."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Debitor" T6 on (T3."Herkunftsnr_" = T6."Nr_") and (T3."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T3."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T3."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."Artikel" T8 on (T3."Nr_" = T8."Nr_") and (T3."Client_DB" = T8."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T9 on (T3."Kostenstellencode" = T9."Code") and (T3."Client_DB" = T9."Client_DB")) full outer join "CARLO"."import"."Verkaufsrechnungskopf" T11 on (T11."Nr_" = T3."Belegnr_") and (T11."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Debitor" T10 on (T11."Verk_ an Deb_-Nr_" = T10."Nr_") and (T11."Client_DB" = T10."Client_DB")) where (((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_INTERN','INTERN','P_INTERN')) THEN ('Intern') WHEN (T3."Geschäftsbuchungsgruppe" IN ('C_GWL_EIG','C_GWL_LEIS','GEWL','GEWL_NSTB','P_GWL_EIG','P_GWL_LEIS')) THEN ('GWL') ELSE ('Extern') END) = 'Intern') and ((((((((((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."Belegnr_" LIKE 'WVGL%')))) 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 '2018-01-01 00:00:00.000'))) ) D1 order by c11 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,A_Interner Auftrag COLUMN,8,A_Interne Belegnr COLUMN,9,Belegart COLUMN,10,Nr COLUMN,11,Verk An Deb -nr COLUMN,12,Auftragsdatum COLUMN,13,Buchungsdatum COLUMN,14,Filialcode COLUMN,15,Belegnr COLUMN,16,Art COLUMN,17,APO_TEILE_Nr COLUMN,18,Beschreibung COLUMN,19,Menge_ori COLUMN,20,Ek-preis COLUMN,21,Einstandspreis COLUMN,22,Einstandsbetrag_ori COLUMN,23,Vk-preis COLUMN,24,Verkaufsbetrag_ori COLUMN,25,Werkstattbuchungsgruppe COLUMN,26,Kostenstellencode COLUMN,27,Markencode COLUMN,28,Benutzer Id COLUMN,29,Herkunftscode COLUMN,30,Geschäftsbuchungsgruppe COLUMN,31,Produktbuchungsgruppe COLUMN,32,Belegdatum COLUMN,33,Nummernserie COLUMN,34,Fahrgestellnummer COLUMN,35,Menge Pro Stunde COLUMN,36,Menge (stunde) COLUMN,37,Artikelgruppencode COLUMN,38,Fahrzeugartencode COLUMN,39,Vorgabezeit COLUMN,40,Herkunftsnr COLUMN,41,Auftragszeilennr COLUMN,42,Serviceberaternr COLUMN,43,Monteur Nr 1 COLUMN,44,Aw Mont 1 COLUMN,45,Monteur Nr 2 COLUMN,46,Aw Mont 2 COLUMN,47,Hauptbetrieb COLUMN,48,Standort COLUMN,49,FIL-Code COLUMN,50,FIL-Bezeichnung COLUMN,51,Standort_1_ori COLUMN,52,Vorname COLUMN,53,Nachname COLUMN,54,Serviceberater COLUMN,55,Menge COLUMN,56,Einstandsbetrag COLUMN,57,Verkaufsbetrag COLUMN,58,Rech An Deb -nr COLUMN,59,Umsatzart_ori COLUMN,60,KD_Nr COLUMN,61,KD_Name COLUMN,62,Kunde COLUMN,63,Invoice Date COLUMN,64,Order Number COLUMN,65,Fabrikat COLUMN,66,Modell COLUMN,67,Model COLUMN,68,Fahrzeug COLUMN,69,Kostenstelle COLUMN,70,Auftragsart COLUMN,71,Lines Net Value COLUMN,72,Umsatz Lohn COLUMN,73,Umsatz Teile Service COLUMN,74,Umsatz Sonstiges COLUMN,75,Einsatz Teile Service_ori COLUMN,76,Einsatz Teile Service COLUMN,77,Einsatz Sonstiges COLUMN,78,Menge_pro_Stunde COLUMN,79,Menge_stunde COLUMN,80,Sollzeit COLUMN,81,Sollzeit_Art COLUMN,82,AW_Mont_1 COLUMN,83,AW_Mont_2 COLUMN,84,verk. Stunden COLUMN,85,Soll-Stunden COLUMN,86,benutzte Zeit_ori COLUMN,87,Auftragsnr COLUMN,88,Archivierungsdatum COLUMN,89,Umsatzart COLUMN,90,Marke COLUMN,91,Kundenart COLUMN,92,DG_1 COLUMN,93,DB Teile COLUMN,94,Verr_Betrag COLUMN,95,Nr COLUMN,96,Beschreibung COLUMN,97,Basiseinheitencode COLUMN,98,Vk-preis COLUMN,99,Einstandspreis_ART COLUMN,100,Ek-preis (neuester)_ART COLUMN,101,Einstandspreis (durchschn )_ART COLUMN,102,Einstandspreis_Art COLUMN,103,EK_preis_neu_Art COLUMN,104,Einstandspreis_Durch_Art COLUMN,105,Heute COLUMN,106,Tag COLUMN,107,Buchungsdatum_Datum COLUMN,108,Rechnungsausgang COLUMN,109,Order_Number_RG_Ausg COLUMN,110,DG_1 COLUMN,111,DG_2 COLUMN,112,Durchgänge COLUMN,113,Standort_1 COLUMN,114,Tage bis Rechnung_ori COLUMN,115,Tage bis Rechnung COLUMN,116,Rech An Deb -nr COLUMN,117,Rech An Name COLUMN,118,Rechnunsgkunde COLUMN,119,Funktion COLUMN,120,Servicberater / sonst. MA COLUMN,121,Date COLUMN,122,Company_ID COLUMN,123,Department_ID COLUMN,124,Employee_Function COLUMN,125,Service_Advisor_Name COLUMN,126,Turnover_Type_Desc COLUMN,127,Order_Desc_100 COLUMN,128,Invoice_Desc_100 COLUMN,129,Order_Desc_30 COLUMN,130,Invoice_Desc_30 COLUMN,131,Cost_Centre_ID COLUMN,132,Make_Desc COLUMN,133,Make_Group COLUMN,134,Model_Desc COLUMN,135,Customer_Group_Owner COLUMN,136,Customer_Name_Owner COLUMN,137,Ländercode COLUMN,138,Land COLUMN,139,Cust_Zipcode_Country COLUMN,140,Plz Code COLUMN,141,Cust_Zipcode_First COLUMN,142,Cust_Zipcode_Second COLUMN,143,Cust_Zipcode_Third COLUMN,144,Cust_Zipcode_Fourth COLUMN,145,Cust_Zipcode COLUMN,146,Erstzulassung COLUMN,147,Fahrzeugalter_Tage COLUMN,148,Fahrzeugalter COLUMN,149,FZG_Altersstaffel COLUMN,150,Car_Age COLUMN,151,Nr_Artikel COLUMN,152,Gmpd Code_Artikel COLUMN,153,Gmpd Subcode_Artikel COLUMN,154,GMPD_Group COLUMN,155,Parts_Number COLUMN,156,Repair_Group_Desc COLUMN,157,Invoice_Credit_Note COLUMN,158,Sign_DB1 COLUMN,159,Order_Status COLUMN,160,Order_Type_Desc COLUMN,161,Job_Amount COLUMN,162,Parts_Amount COLUMN,163,Parts_Purch_Amount COLUMN,164,Misc_Amount COLUMN,165,Invoiced_Time COLUMN,166,Quantity COLUMN,167,Order_Count