select T1."Nr_" as "Nr", T1."Name" as "Name", T1."Kontoart" as "Kontoart", T1."GuV_Bilanz" as "Guv Bilanz", T1."Soll_Haben" as "Soll Haben", T1."Kostenstellen Buchung" as "Kostenstellen Buchung", T2."Lfd_ Nr_" as "Lfd Nr", T2."Sachkontonr_" as "Sachkontonr", T2."Buchungsdatum" as "Buchungsdatum", T2."Belegart" as "Belegart", T2."Belegnr_" as "Belegnr", T2."Beschreibung" as "Beschreibung", T2."Gegenkontonr_" as "Gegenkontonr", 0 as "Betrag_ori", T2."Kostenstellencode" as "Kostenstellencode", T2."Markencode" as "Markencode", T2."Benutzer ID" as "Benutzer Id", T2."Herkunftscode" as "Herkunftscode", T2."Nachbuchung" as "Nachbuchung", T2."Menge" as "Menge_ori", T2."MWSt Betrag" as "Mwst Betrag", T2."Buchungsart" as "Buchungsart", T2."Geschäftsbuchungsgruppe" as "Geschäftsbuchungsgruppe", T3."Beschreibung" as "Geschäftsbuchungsgruppe_Beschreibung", T2."Produktbuchungsgruppe" as "Produktbuchungsgruppe", T4."Beschreibung" as "Produktbuchungsgruppe_Beschreibung", T2."Gegenkontoart" as "Gegenkontoart", T2."Transaktionsnr_" as "Transaktionsnr", (T2."Sollbetrag") as "Sollbetrag", (T2."Habenbetrag") as "Habenbetrag", T2."Belegdatum" as "Belegdatum", T2."Externe Belegnummer" as "Externe Belegnummer", T2."Herkunftsart" as "Herkunftsart", T2."Herkunftsnr_" as "Herkunftsnr", T2."Filialcode" as "Filialcode", T2."Hauptbereich" as "Hauptbereich", T2."Fahrgestellnummer" as "Fahrgestellnummer", T2."Buchnummer" as "Buchnummer", T2."Fahrzeug-Kz" as "Fahrzeug-kz", T2."Umgebucht" as "Umgebucht", T2."Storniert" as "Storniert", T2."Fahrzeugklassecode" as "Fahrzeugklassecode", T5."Code" as "Code", T5."Name" as "Name", T6."Code" as "Code", T6."Bezeichnung" as "Bezeichnung", (0) as "Betrag_1", T2."Client_DB" as "Rechtseinheit", CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END as "Betrieb_Nr", T6."Bezeichnung" as "Betrieb_1", CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END as "Marke_ori", (database()) + ' - ' + 'Kandel' as "Mandant", (cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0)) as "Menge_1", T2."Buchungsdatum" as "Bookkeep Date", CASE WHEN (((day((now()) - T2."Buchungsdatum")) <= 90) and (T1."Nr_" <> '630000')) THEN (T2."Belegnr_" + ' - ' + T2."Beschreibung" + ' - ' + T2."Benutzer ID") ELSE null END as "Text", CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END as "KST_aus_Code", CASE WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '1%') THEN ('1') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '2%') THEN ('2') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '3%') THEN ('6') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) = '41') THEN ('3') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) = '44') THEN ('4') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) = '45') THEN ('5') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '5%') THEN ('7') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '9%') THEN ('0') ELSE null END as "Kostenstelle_ori", (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) as "KST_1", (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) + ' - ' + T5."Name" as "KST_2", T2."Sachkontonr_" as "Acct Nr_ori", ((CASE WHEN ((((left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END)) as "Betrag", CASE WHEN ((((left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END as "Menge", CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END as "Betrieb", T2."Benutzer ID" as "Benutzer", CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Fahrgestellnummer") ELSE null END as "Fahrgestellnummer", CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Modell") ELSE null END as "Modell_neu", CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Markencode") ELSE null END as "Markencode", CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Verkäufer Nr_ Verkauf") ELSE null END as "Verkäufer Nr Verkauf", CASE WHEN ((left(T1."Nr_",1)) IN ('5','6','7','8')) THEN ('TEK') ELSE ('Kosten/Neutral') END as "Konto KZ", CASE WHEN (((left(T1."Nr_",2)) IN ('70','71','80','81')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '1')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('880','881','890','891')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '1')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('72','82')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '2')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('882','892')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '2')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('73','83')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '3')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('883','893')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '3')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('74','75','84','85')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '4')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('884','885','894','899')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '4')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('76','86')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '5')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('896')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '5')) THEN ('nein') ELSE ('ja') END as "Plausibilität", (left(T2."Kostenstellencode",2)) as "Marke aus KST", (T2."MB_Makecode") as "Marke", (substring(T1."Nr_", 1, 1)) as "Susa", T1."GuV_Bilanz" as "GuV_Bilanz", T2."MB_Distributionchannelcode" as "Absatzkanal_Code", CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END as "KST_ori", T2."MB_Costcentercode" as "KST", T2."MB_Makecode" as "Mb Makecode", T2."MB_Locationcode" as "Mb Locationcode", T2."MB_Costcentercode" as "Mb Costcentercode", T2."MB_Distributionchannelcode" as "Mb Distributionchannelcode", T2."MB_Costunitcode" as "Mb Costunitcode", T2."MB_Taxationcode" as "Mb Taxationcode", T2."MB_Costunitcode" as "Kostenträger", T2."Sachkontonr_" + '_' + T2."Kostenstellencode" + '_STK' as "Acct Nr", T8."MB Makecode" as "Mb Makecode", T8."Code" as "Code_MB_Cost_Unit", T8."Description" as "Description_MB_Cost_Unit", CASE WHEN (T2."MB_Costunitcode" BETWEEN '01' AND '49') THEN (T2."MB_Makecode" + '-' + T2."MB_Costunitcode") ELSE (T2."MB_Costunitcode") END as "Kostenträger_FIBU" from "CARLO"."import"."Sachkonto" T1, (((((("CARLO"."import"."Sachposten" T2 left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T3 on (T2."Geschäftsbuchungsgruppe" = T3."Code") and (T2."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Produktbuchungsgrp" T4 on (T2."Produktbuchungsgruppe" = T4."Code") and (T2."Client_DB" = T4."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T5."Code" = T2."Kostenstellencode") and (T5."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T6 on (T2."Filialcode" = T6."Code") and (T2."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T2."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T2."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."MB_Costunit" T8 on ((T2."MB_Costunitcode" = T8."Code") and (T2."Client_DB" = T8."Client_DB")) and (T2."MB_Makecode" = T8."MB Makecode")) where ((T1."Nr_" = T2."Sachkontonr_") and (T1."Client_DB" = T2."Client_DB")) and (((((not T2."Beschreibung" LIKE '%Nullstellung%') and (T2."Buchungsdatum" >= convert(datetime, '2018-01-01 00:00:00.000'))) and (T1."GuV_Bilanz" = 0)) and ((T1."Nr_" BETWEEN '800000' AND '829900') or (T1."Nr_" BETWEEN '851000' AND '851007'))) and ((((CASE WHEN ((((left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END))) <> 0)) -- order by "Fahrgestellnummer" asc,"Lfd Nr" asc