C11/System/CARLO/IQD/Serv_Teile/auftraege_eds_intern_c11.sql

select c290 as "A_Belegart",
	   c289 as "A_Nr",
	   c288 as "A_Verk An Deb -nr",
	   c213 as "A_Rech An Deb -nr",
	   c287 as "A_Auftragsdatum",
	   c286 as "A_Buchungsdatum",
	   c285 as "A_Filialcode",
	   c284 as "A_Interner Auftrag",
	   c283 as "A_Interne Belegnr",
	   c282 as "Belegart",
	   c281 as "Nr",
	   c280 as "Verk An Deb -nr",
	   c279 as "Auftragsdatum",
	   c208 as "Buchungsdatum",
	   c207 as "Filialcode",
	   c278 as "Belegnr",
	   c277 as "Art",
	   c276 as "APO_TEILE_Nr",
	   c275 as "Beschreibung",
	   c250 as "Menge_ori",
	   c274 as "Ek-preis",
	   c273 as "Einstandspreis",
	   c249 as "Einstandsbetrag_ori",
	   c272 as "Vk-preis",
	   c271 as "Verkaufsbetrag_ori",
	   c270 as "Werkstattbuchungsgruppe",
	   c269 as "Kostenstellencode",
	   c199 as "Markencode",
	   c268 as "Benutzer Id",
	   c267 as "Herkunftscode",
	   c197 as "Geschäftsbuchungsgruppe",
	   c266 as "Produktbuchungsgruppe",
	   c265 as "Belegdatum",
	   c264 as "Nummernserie",
	   c263 as "Fahrgestellnummer",
	   c236 as "Menge Pro Stunde",
	   c235 as "Menge (stunde)",
	   c262 as "Artikelgruppencode",
	   c261 as "Fahrzeugartencode",
	   c234 as "Vorgabezeit",
	   c260 as "Herkunftsnr",
	   c259 as "Auftragszeilennr",
	   c258 as "Serviceberaternr",
	   c257 as "Monteur Nr 1",
	   c232 as "Aw Mont 1",
	   c256 as "Monteur Nr 2",
	   c231 as "Aw Mont 2",
	   '1' as "Hauptbetrieb",
	   c253 as "Standort",
	   c255 as "FIL-Code",
	   c254 as "FIL-Bezeichnung",
	   c253 as "Standort_1_ori",
	   c252 as "Vorname",
	   c251 as "Nachname",
	   c206 as "Serviceberater",
	   c250 as "Menge",
	   c249 as "Einstandsbetrag",
	   c248 as "Verkaufsbetrag",
	   c247 as "Rech An Deb -nr",
	   c246 as "Umsatzart_ori",
	   c245 as "KD_Nr",
	   c244 as "KD_Name",
	   c196 as "Kunde",
	   c208 as "Invoice Date",
	   c243 as "Order Number",
	   c199 as "Fabrikat",
	   c242 as "Modell",
	   c242 as "Model",
	   c241 as "Fahrzeug",
	   c240 as "Kostenstelle",
	   'Service' as "Auftragsart",
	   c239 as "Lines Net Value",
	   c178 as "Umsatz Lohn",
	   c177 as "Umsatz Teile Service",
	   c175 as "Umsatz Sonstiges",
	   c238 as "Einsatz Teile Service_ori",
	   c176 as "Einsatz Teile Service",
	   c237 as "Einsatz Sonstiges",
	   c236 as "Menge_pro_Stunde",
	   c235 as "Menge_stunde",
	   c234 as "Sollzeit",
	   c233 as "Sollzeit_Art",
	   c232 as "AW_Mont_1",
	   c231 as "AW_Mont_2",
	   c174 as "verk. Stunden",
	   c230 as "Soll-Stunden",
	   c229 as "benutzte Zeit_ori",
	   c172 as "Auftragsnr",
	   c228 as "Archivierungsdatum",
	   c205 as "Umsatzart",
	   c200 as "Marke",
	   c227 as "Kundenart",
	   1 as "DG_1",
	   c226 as "DB Teile",
	   c225 as "Verr_Betrag",
	   c181 as "Nr",
	   c224 as "Beschreibung",
	   c223 as "Basiseinheitencode",
	   c222 as "Vk-preis",
	   c221 as "Einstandspreis_ART",
	   c220 as "Ek-preis (neuester)_ART",
	   c219 as "Einstandspreis (durchschn )_ART",
	   c221 as "Einstandspreis_Art",
	   c220 as "EK_preis_neu_Art",
	   c219 as "Einstandspreis_Durch_Art",
	   c218 as "Heute",
	   c217 as "Tag",
	   c216 as "Buchungsdatum_Datum",
	   c203 as "Rechnungsausgang",
	   c215 as "Order_Number_RG_Ausg",
	   1 as "DG_1",
	   COUNT(c281) OVER (partition by c172) as "DG_2",
	   1 / (COUNT(c281) OVER (partition by c172)) as "Durchgänge",
	   ('Serviceberater') as "Standort_1",
	   c214 as "Tage bis Rechnung_ori",
	   (c214) / (COUNT(c281) OVER (partition by c172)) as "Tage bis Rechnung",
	   c213 as "Rech An Deb -nr",
	   c212 as "Rech An Name",
	   c211 as "Rechnunsgkunde",
	   c210 as "Funktion",
	   c209 as "Servicberater / sonst. MA",
	   c208 as "Date",
	   '1' as "Company_ID",
	   c207 as "Department_ID",
	   'Serviceberater' as "Employee_Function",
	   c206 as "Service_Advisor_Name",
	   c205 as "Turnover_Type_Desc",
	   c204 as "Order_Desc_100",
	   '' as "Invoice_Desc_100",
	   c203 as "Order_Desc_30",
	   c202 as "Invoice_Desc_30",
	   c201 as "Cost_Centre_ID",
	   c200 as "Make_Desc",
	   c199 as "Make_Group",
	   c198 as "Model_Desc",
	   c197 as "Customer_Group_Owner",
	   c196 as "Customer_Name_Owner",
	   c195 as "Ländercode",
	   c194 as "Land",
	   c194 as "Cust_Zipcode_Country",
	   c189 as "Plz Code",
	   c193 as "Cust_Zipcode_First",
	   c192 as "Cust_Zipcode_Second",
	   c191 as "Cust_Zipcode_Third",
	   c190 as "Cust_Zipcode_Fourth",
	   c189 as "Cust_Zipcode",
	   c188 as "Erstzulassung",
	   c187 as "Fahrzeugalter_Tage",
	   c186 as "Fahrzeugalter",
	   c185 as "FZG_Altersstaffel",
	   c185 as "Car_Age",
	   c181 as "Nr_Artikel",
	   c184 as "Gmpd Code_Artikel",
	   c183 as "Gmpd Subcode_Artikel",
	   c182 as "GMPD_Group",
	   c181 as "Parts_Number",
	   c180 as "Repair_Group_Desc",
	   'Rechnung' as "Invoice_Credit_Note",
	   c179 as "Sign_DB1",
	   'abgerechnet' as "Order_Status",
	   'Service' as "Order_Type_Desc",
	   c178 as "Job_Amount",
	   c177 as "Parts_Amount",
	   c176 as "Parts_Purch_Amount",
	   c175 as "Misc_Amount",
	   c174 as "Invoiced_Time",
	   c173 as "Quantity",
	   (1 / (COUNT(c281) OVER (partition by c172))) as "Order_Count"
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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day((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,
	   (left(T6."PLZ Code",4)) as c190,
	   (left(T6."PLZ Code",3)) as c191,
	   (left(T6."PLZ Code",2)) as c192,
	   (left(T6."PLZ Code",1)) as c193,
	   (CASE WHEN (((T11."Ländercode" IS NULL) or (T11."Ländercode" = ' ')) or (T11."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,
	   (left((ucase(T7."Modell")),3)) as c198,
	   T3."Markencode" as c199,
	   (CASE WHEN (T3."Markencode" IN ('OPEL','VW')) THEN (T3."Markencode") ELSE ('Fremdfabrikat') END) as c200,
	   T3."Kostenstellencode" + ' - ' + T10."Name" as c201,
	   CASE WHEN ((day((now()) - ((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 (T1."Serviceberaternr_" + ' - ' + T5."Vorname" + ' ' + T5."Nachname") ELSE ('SB fehlt') END) + ' - ' + (T6."Nr_" + ' - ' + T6."Name")) ELSE ('Rechnungen älter 30 Tage') END as c202,
	   CASE WHEN ((day((now()) - (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 ((day((now()) - ((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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) as c205,
	   (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T1."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 (T9."Funktion" IN ('Service Berater','Serviceberater','Serviceverater')) THEN ('Serviceberater') ELSE ('sonstige MA') END as c209,
	   T9."Funktion" as c210,
	   T3."Herkunftsnr_(Zahlung)" as c211,
	   T1."Rech_ an Name" as c212,
	   T1."Rech_ an Deb_-Nr_" as c213,
	   (day(((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 (T1."Serviceberaternr_" + ' - ' + T5."Vorname" + ' ' + T5."Nachname") ELSE ('SB fehlt') END) + ' - ' + (convert(varchar(50), year((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) + '-' + convert(varchar(50), month((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) + '-' + convert(varchar(50), day((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))))) as c215,
	   (((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) as c216,
	   (dayofweek(((now())))) as c217,
	   (now()) as c218,
	   (T8."Einstandspreis (durchschn_)") as c219,
	   (T8."EK-Preis (neuester)") as c220,
	   (T8."Einstandspreis") as c221,
	   T8."VK-Preis" as c222,
	   T8."Basiseinheitencode" as c223,
	   T8."Beschreibung" as c224,
	   (T3."Verrechnungsbetrag") as c225,
	   (CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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 c226,
	   T3."Kundengruppencode" as c227,
	   T1."Archivierungsdatum" as c228,
	   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 c229,
	   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 c230,
	   (T3."AW Mont_ 2") as c231,
	   (T3."AW Mont_ 1") as c232,
	   T3."Vorgabezeitart" as c233,
	   (T3."Vorgabezeit") as c234,
	   (T3."Menge (Stunde)") as c235,
	   (T3."Menge pro Stunde") as c236,
	   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 c237,
	   CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END as c238,
	   CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END as c239,
	   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 c240,
	   T3."Fahrgestellnummer" + ' - ' + T7."Modell" as c241,
	   T7."Modell" as c242,
	   (CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) + ' - ' + T6."Name" as c243,
	   T6."Name" as c244,
	   T6."Nr_" as c245,
	   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 c246,
	   CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END as c247,
	   CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END as c248,
	   (T3."Einstandsbetrag") as c249,
	   (T3."Menge") as c250,
	   T5."Nachname" as c251,
	   T5."Vorname" as c252,
	   (CASE WHEN ('1' = '2') THEN ('20') ELSE ((CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END)) END) as c253,
	   T4."Bezeichnung" as c254,
	   T4."Code" as c255,
	   T3."Monteur Nr_ 2" as c256,
	   T3."Monteur Nr_ 1" as c257,
	   T1."Serviceberaternr_" as c258,
	   T3."Auftragszeilennr_" as c259,
	   T3."Herkunftsnr_" as c260,
	   T3."Fahrzeugartencode" as c261,
	   T3."Artikelgruppencode" as c262,
	   T3."Fahrgestellnummer" as c263,
	   T3."Nummernserie" as c264,
	   T3."Belegdatum" as c265,
	   T3."Produktbuchungsgruppe" as c266,
	   T3."Herkunftscode" as c267,
	   T3."Benutzer ID" as c268,
	   T3."Kostenstellencode" as c269,
	   T3."Werkstattbuchungsgruppe" as c270,
	   T3."Verkaufsbetrag" as c271,
	   T3."VK-Preis" as c272,
	   T3."Einstandspreis" as c273,
	   T3."EK-Preis" as c274,
	   T3."Beschreibung" as c275,
	   T3."Nr_" as c276,
	   T3."Art" as c277,
	   T3."Belegnr_" as c278,
	   CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END as c279,
	   CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END as c280,
	   CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END as c281,
	   CASE WHEN (T1."Belegart" IS NULL) THEN (T2."Belegart") ELSE (T1."Belegart") END as c282,
	   T1."Interne Belegnr_" as c283,
	   T1."Interner Auftrag" as c284,
	   T1."Filialcode" as c285,
	   T1."Buchungsdatum" as c286,
	   T1."Auftragsdatum" as c287,
	   T1."Verk_ an Deb_-Nr_" as c288,
	   T1."Nr_" as c289,
	   T1."Belegart" as c290
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 (T1."Serviceberaternr_" = T5."Nr_") and (T1."Client_DB" = T5."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"."Employee" T9 on (T9."Nr_" = T3."Serviceberaternr_") and (T9."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T10 on (T3."Kostenstellencode" = T10."Code") and (T3."Client_DB" = T10."Client_DB")) full outer join "CARLO"."import"."Verkaufsrechnungskopf" T12 on (T12."Nr_" = T3."Belegnr_") and (T12."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Debitor" T11 on (T12."Verk_ an Deb_-Nr_" = T11."Nr_") and (T12."Client_DB" = T11."Client_DB"))
where (((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') 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)) >= convert(datetime, '2018-01-01 00:00:00.000'))))
) D1
-- order by "Nr" asc