C11/System/CARLO/IQD/NW/NW_GW_EDS_FIBU_Verkauf_KER.sql

select "Nr" as "Nr",
	   "Name" as "Name",
	   "Kontoart" as "Kontoart",
	   "Guv Bilanz" as "Guv Bilanz",
	   "Soll Haben" as "Soll Haben",
	   "Kostenstellen Buchung" as "Kostenstellen Buchung",
	   "Lfd Nr" as "Lfd Nr",
	   "Sachkontonr" as "Sachkontonr",
	   "Buchungsdatum" as "Buchungsdatum",
	   "Belegart" as "Belegart",
	   "Belegnr" as "Belegnr",
	   "Beschreibung" as "Beschreibung",
	   "Gegenkontonr" as "Gegenkontonr",
	   "Betrag_ori" as "Betrag_ori",
	   "Kostenstellencode" as "Kostenstellencode",
	   "Markencode" as "Markencode",
	   "Benutzer Id" as "Benutzer Id",
	   "Herkunftscode" as "Herkunftscode",
	   "Nachbuchung" as "Nachbuchung",
	   "Menge_ori" as "Menge_ori",
	   "Mwst Betrag" as "Mwst Betrag",
	   "Buchungsart" as "Buchungsart",
	   "Geschäftsbuchungsgruppe" as "Geschäftsbuchungsgruppe",
	   "Produktbuchungsgruppe_FIBU" as "Produktbuchungsgruppe_FIBU",
	   "Gegenkontoart" as "Gegenkontoart",
	   "Transaktionsnr" as "Transaktionsnr",
	   "Sollbetrag" as "Sollbetrag",
	   "Habenbetrag" as "Habenbetrag",
	   "Belegdatum" as "Belegdatum",
	   "Externe Belegnummer" as "Externe Belegnummer",
	   "Herkunftsart" as "Herkunftsart",
	   "Herkunftsnr" as "Herkunftsnr",
	   "Filialcode" as "Filialcode",
	   "Hauptbereich" as "Hauptbereich",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Buchnummer" as "Buchnummer",
	   "Fahrzeug-kz" as "Fahrzeug-kz",
	   "Umgebucht" as "Umgebucht",
	   "Storniert" as "Storniert",
	   "Fahrzeugklassecode" as "Fahrzeugklassecode",
	   "Code" as "Code",
	   "Name" as "Name",
	   "Code" as "Code",
	   "Bezeichnung" as "Bezeichnung",
	   "Betrag_1" as "Betrag_1",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort_alt" as "Standort_alt",
	   "Betrieb_1" as "Betrieb_1",
	   "Marke_ori" as "Marke_ori",
	   "Mandant" as "Mandant",
	   "Menge_1" as "Menge_1",
	   "Bookkeep Date" as "Bookkeep Date",
	   "Text" as "Text",
	   "KST_aus_Code" as "KST_aus_Code",
	   "Kostenstelle_ori" as "Kostenstelle_ori",
	   "KST_1" as "KST_1",
	   "KST_2" as "KST_2",
	   "Acct Nr" as "Acct Nr",
	   "Betrag" as "Betrag",
	   "Menge" as "Menge",
	   "Betrieb" as "Betrieb",
	   "Benutzer" as "Benutzer",
	   "Fabrikat" as "Fabrikat",
	   "Fahrzeugstatus" as "Fahrzeugstatus",
	   "Model" as "Model",
	   "Fahrzeugart" as "Fahrzeugart",
	   "Konto" as "Konto",
	   "Verkaufsdatum" as "Verkaufsdatum",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "Fahrzeugtyp" as "Fahrzeugtyp",
	   "FZG" as "FZG",
	   "Fahrgestellnummer_FZG" as "Fahrgestellnummer_FZG",
	   "Fahrzeugtyp_1" as "Fahrzeugtyp_1",
	   "Einkaufslieferdatum_FZG" as "Einkaufslieferdatum_FZG",
	   "Heute" as "Heute",
	   "Standtage" as "Standtage",
	   "Standtagestaffel" as "Standtagestaffel",
	   "Ausstattungscode" as "Ausstattungscode",
	   "Ausstattungskennzeichen" as "Ausstattungskennzeichen",
	   "Beschreibung_Ausstattung" as "Beschreibung_Ausstattung",
	   "Farbe" as "Farbe",
	   "Standort_1" as "Standort_1",
	   "Lagerortcode" as "Lagerortcode",
	   "Markencode_FZG" as "Markencode_FZG",
	   "FZG_KZ_Summe" as "FZG_KZ_Summe",
	   "Verkäufer Nr Verkauf" as "Verkäufer Nr Verkauf",
	   "Code_Verkäufer" as "Code_Verkäufer",
	   "Name_Verkäufer" as "Name_Verkäufer",
	   "Verkäufer" as "Verkäufer",
	   "Debitorennr_FZG" as "Debitorennr_FZG",
	   "Nr_Deb" as "Nr_Deb",
	   "Name_Deb" as "Name_Deb",
	   "Kunde" as "Kunde",
	   "Kundenart" as "Kundenart",
	   "Anzahl Vorbesitzer" as "Anzahl Vorbesitzer",
	   "Edv Nummer" as "Edv Nummer",
	   "Aktuelle Filialbuchnummer" as "Aktuelle Filialbuchnummer",
	   "Alter Fahrzeugstatus" as "Alter Fahrzeugstatus",
	   "Alte Debitorennr" as "Alte Debitorennr",
	   "Altes Verkaufsdatum" as "Altes Verkaufsdatum",
	   "Alter Eigenverkauf" as "Alter Eigenverkauf",
	   "Alte Buchnummer" as "Alte Buchnummer",
	   "Alte Filialbuchnummer" as "Alte Filialbuchnummer",
	   "Aktuelle Buchnummer" as "Aktuelle Buchnummer",
	   "Einkaufscode" as "Einkaufscode",
	   "Tage Buchungs_Verkaufsdatum" as "Tage Buchungs_Verkaufsdatum",
	   "Fahrzeugart_Konto" as "Fahrzeugart_Konto",
	   "zwei_mal_verkauft" as "zwei_mal_verkauft",
	   "raus?" as "raus?",
	   "Gesamterlöse" as "Gesamterlöse",
	   "Erlös FZG" as "Erlös FZG",
	   "Einsatz Gesamt" as "Einsatz Gesamt",
	   "Einsatz FZG" as "Einsatz FZG",
	   "Erlös Garantie" as "Erlös Garantie",
	   "Erlös Provision" as "Erlös Provision",
	   "Erlös Sonstiges" as "Erlös Sonstiges",
	   "Einsatz Sonstiges" as "Einsatz Sonstiges",
	   "Invoice Date" as "Invoice Date",
	   "Instands. Werkstatt" as "Instands. Werkstatt",
	   "Instands. Teile" as "Instands. Teile",
	   "Instandsetzung ges." as "Instandsetzung ges.",
	   "Alte Debitorennr" as "Alte Debitorennr",
	   "Name_Vorbesitzer" as "Name_Vorbesitzer",
	   "Vorbesitzername" as "Vorbesitzername",
	   "Verkäufernr Einkauf" as "Verkäufernr Einkauf",
	   "Code_Einkäufer" as "Code_Einkäufer",
	   "Name_Einkäufer" as "Name_Einkäufer",
	   "Einkäufer/Vorbesitzer" as "Einkäufer/Vorbesitzer",
	   "FZG_Detail" as "FZG_Detail",
	   "Einsatz Garantie" as "Einsatz Garantie",
	   "Summe Erlös FZG" as "Summe Erlös FZG",
	   "Plz Code_Deb" as "Plz Code_Deb",
	   "PLZ_1" as "PLZ_1",
	   "PLZ_2" as "PLZ_2",
	   "PLZ_3" as "PLZ_3",
	   "PLZ_4" as "PLZ_4",
	   "Modell_Beschreibung" as "Modell_Beschreibung",
	   "Nr_Lieferant" as "Nr_Lieferant",
	   "Name_Lieferant_ori" as "Name_Lieferant_ori",
	   "Name_Lieferant" as "Name_Lieferant",
	   "DB1" as "DB1",
	   "Summe DB1" as "Summe DB1",
	   "DB1 < 0" as "DB1 < 0",
	   "Standort" as "Standort",
	   "Mb Makecode" as "Mb Makecode",
	   "Mb Locationcode" as "Mb Locationcode",
	   "Mb Costcentercode" as "Mb Costcentercode",
	   "Mb Distributionchannelcode" as "Mb Distributionchannelcode",
	   "Mb Costunitcode" as "Mb Costunitcode",
	   "Modelllinie" as "Modelllinie",
	   "Mb Makecode" as "Mb Makecode",
	   "Code_Cost_Unit" as "Code_Cost_Unit",
	   "Description_Cost_Unit" as "Description_Cost_Unit",
	   "umsatzabh. Kosten" as "umsatzabh. Kosten",
	   "dir. Kosten" as "dir. Kosten"
from 
(select c287 as "Nr",
	   c286 as "Name",
	   c285 as "Kontoart",
	   c284 as "Guv Bilanz",
	   c283 as "Soll Haben",
	   c282 as "Kostenstellen Buchung",
	   c281 as "Lfd Nr",
	   c240 as "Sachkontonr",
	   c280 as "Buchungsdatum",
	   c279 as "Belegart",
	   c278 as "Belegnr",
	   c277 as "Beschreibung",
	   c276 as "Gegenkontonr",
	   c250 as "Betrag_ori",
	   c275 as "Kostenstellencode",
	   c274 as "Markencode",
	   c237 as "Benutzer Id",
	   c273 as "Herkunftscode",
	   c272 as "Nachbuchung",
	   c246 as "Menge_ori",
	   c271 as "Mwst Betrag",
	   c270 as "Buchungsart",
	   c269 as "Geschäftsbuchungsgruppe",
	   c268 as "Produktbuchungsgruppe_FIBU",
	   c267 as "Gegenkontoart",
	   c266 as "Transaktionsnr",
	   c265 as "Sollbetrag",
	   c264 as "Habenbetrag",
	   c263 as "Belegdatum",
	   c262 as "Externe Belegnummer",
	   c261 as "Herkunftsart",
	   c260 as "Herkunftsnr",
	   c238 as "Filialcode",
	   c259 as "Hauptbereich",
	   c162 as "Fahrgestellnummer",
	   c258 as "Buchnummer",
	   c257 as "Fahrzeug-kz",
	   c256 as "Umgebucht",
	   c255 as "Storniert",
	   c254 as "Fahrzeugklassecode",
	   c253 as "Code",
	   c252 as "Name",
	   c251 as "Code",
	   c249 as "Bezeichnung",
	   c250 as "Betrag_1",
	   '1' as "Hauptbetrieb",
	   c166 as "Standort_alt",
	   c249 as "Betrieb_1",
	   c248 as "Marke_ori",
	   c247 as "Mandant",
	   c246 as "Menge_1",
	   c245 as "Bookkeep Date",
	   c244 as "Text",
	   c242 as "KST_aus_Code",
	   c243 as "Kostenstelle_ori",
	   c242 as "KST_1",
	   c241 as "KST_2",
	   c240 as "Acct Nr",
	   c239 as "Betrag",
	   1 / (COUNT(c287) OVER (partition by c162)) as "Menge",
	   c238 as "Betrieb",
	   c237 as "Benutzer",
	   c219 as "Fabrikat",
	   c236 as "Fahrzeugstatus",
	   c235 as "Model",
	   c234 as "Fahrzeugart",
	   c233 as "Konto",
	   c188 as "Verkaufsdatum",
	   c229 as "Produktbuchungsgruppe",
	   c232 as "Fahrzeugtyp",
	   c231 as "FZG",
	   c230 as "Fahrgestellnummer_FZG",
	   c229 as "Fahrzeugtyp_1",
	   c228 as "Einkaufslieferdatum_FZG",
	   c227 as "Heute",
	   c226 as "Standtage",
	   c225 as "Standtagestaffel",
	   c224 as "Ausstattungscode",
	   c223 as "Ausstattungskennzeichen",
	   c222 as "Beschreibung_Ausstattung",
	   c221 as "Farbe",
	   c220 as "Standort_1",
	   c220 as "Lagerortcode",
	   c219 as "Markencode_FZG",
	   SUM(c257) OVER (partition by c162) as "FZG_KZ_Summe",
	   c218 as "Verkäufer Nr Verkauf",
	   c217 as "Code_Verkäufer",
	   c216 as "Name_Verkäufer",
	   c215 as "Verkäufer",
	   c214 as "Debitorennr_FZG",
	   c213 as "Nr_Deb",
	   c212 as "Name_Deb",
	   c211 as "Kunde",
	   c210 as "Kundenart",
	   c209 as "Anzahl Vorbesitzer",
	   c208 as "Edv Nummer",
	   c207 as "Aktuelle Filialbuchnummer",
	   c206 as "Alter Fahrzeugstatus",
	   c185 as "Alte Debitorennr",
	   c205 as "Altes Verkaufsdatum",
	   c204 as "Alter Eigenverkauf",
	   c203 as "Alte Buchnummer",
	   c202 as "Alte Filialbuchnummer",
	   c201 as "Aktuelle Buchnummer",
	   c200 as "Einkaufscode",
	   c199 as "Tage Buchungs_Verkaufsdatum",
	   c198 as "Fahrzeugart_Konto",
	   c197 as "zwei_mal_verkauft",
	   c196 as "raus?",
	   c195 as "Gesamterlöse",
	   c194 as "Erlös FZG",
	   c193 as "Einsatz Gesamt",
	   c192 as "Einsatz FZG",
	   c191 as "Erlös Garantie",
	   c190 as "Erlös Provision",
	   c189 as "Erlös Sonstiges",
	   (0) as "Einsatz Sonstiges",
	   c188 as "Invoice Date",
	   c187 as "Instands. Werkstatt",
	   0 as "Instands. Teile",
	   c186 as "Instandsetzung ges.",
	   c185 as "Alte Debitorennr",
	   c184 as "Name_Vorbesitzer",
	   c183 as "Vorbesitzername",
	   c182 as "Verkäufernr Einkauf",
	   c181 as "Code_Einkäufer",
	   c180 as "Name_Einkäufer",
	   c179 as "Einkäufer/Vorbesitzer",
	   c178 as "FZG_Detail",
	   c177 as "Einsatz Garantie",
	   SUM(c194) OVER (partition by c162) as "Summe Erlös FZG",
	   c176 as "Plz Code_Deb",
	   c175 as "PLZ_1",
	   c174 as "PLZ_2",
	   c173 as "PLZ_3",
	   c172 as "PLZ_4",
	   c171 as "Modell_Beschreibung",
	   c170 as "Nr_Lieferant",
	   c169 as "Name_Lieferant_ori",
	   c168 as "Name_Lieferant",
	   c167 as "DB1",
	   SUM(c167) OVER (partition by c162) as "Summe DB1",
	   CASE WHEN ((SUM(c167) OVER (partition by c162)) < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as "DB1 < 0",
	   c166 as "Standort",
	   '' as "Mb Makecode",
	   '' as "Mb Locationcode",
	   '' as "Mb Costcentercode",
	   '' as "Mb Distributionchannelcode",
	   '' as "Mb Costunitcode",
	   c165 as "Modelllinie",
	   '' as "Mb Makecode",
	   '' as "Code_Cost_Unit",
	   '' as "Description_Cost_Unit",
	   c164 as "umsatzabh. Kosten",
	   c163 as "dir. Kosten"
from 
(select T2."Fahrgestellnummer" as c162,
	   CASE WHEN (((left(T1."Nr_",4)) IN ('4500','4501','4502','4503','4550','4622','5501')) or ((left(T1."Nr_",6)) IN ('479000'))) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c163,
	   CASE WHEN (((left(T1."Nr_",4)) IN ('4300','4530','4540','4560','4570','4580','4590','5002','5003','5004','5005')) or ((left(T1."Nr_",6)) IN ('479010'))) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c164,
	   T5."Modelllinie" as c165,
	   ((left(T5."Lagerortcode",2))) as c166,
	   (CASE WHEN ((left(T1."Nr_",4)) IN ('8000','8001','8010','8011','8100','8110','8111','8510','8220','8928','8934','5002','8112','8030','8200')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END) - (CASE WHEN ((left(T1."Nr_",4)) IN ('7000','7010','7100','7101','7110','7510','7850','7800','7120','7111','7200','7030')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END) as c167,
	   CASE WHEN (T12."Nr_" IN ('704884','704889','704868','704913','704891','704890')) THEN ('VW Leasing GmbH') WHEN (T12."Nr_" IN ('0015000058','0015000955','704707','705009','706734')) THEN ('GMAC Leasing GmbH') WHEN (T12."Nr_" IN ('707019')) THEN ('Junge Opel') WHEN (T12."Nr_" IN ('706832','706962')) THEN ('Fleet4sale') ELSE (T12."Name") END as c168,
	   T12."Name" as c169,
	   T12."Nr_" as c170,
	   T6."Beschreibung" as c171,
	   (left(T7."PLZ Code",4)) as c172,
	   (left(T7."PLZ Code",3)) as c173,
	   (left(T7."PLZ Code",2)) as c174,
	   (left(T7."PLZ Code",1)) as c175,
	   T7."PLZ Code" as c176,
	   CASE WHEN (T1."Nr_" IN ('72740','72750','71650')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c177,
	   CASE WHEN ((day((now()) - T5."Verkaufsdatum")) <= 365) THEN (T2."Fahrgestellnummer" + ' - ' + (((left(T5."Lagerortcode",2)))) + ' - ' + T5."Produktbuchungsgruppe" + ' - ' + (CASE WHEN (T9."Name" IS NULL) THEN ('VB fehlt') ELSE (T9."Name") END) + ' / ' + (T7."Nr_" + ' - ' + T7."Name") + ' - ' + (convert(varchar(50), year(T5."Verkaufsdatum")) + '-' + convert(varchar(50), month(T5."Verkaufsdatum")) + '-' + convert(varchar(50), day(T5."Verkaufsdatum")))) ELSE ('älter 365 Tage') END as c178,
	   CASE WHEN ((CASE WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_D')) THEN ('GW') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_R')) THEN ('GW') WHEN (T5."Produktbuchungsgruppe" IN ('F_MIETW')) THEN ('Mietwagen') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.','Mietwagen','Gebrauchtwagen','GW')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_D')) THEN ('GW') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_R')) THEN ('GW') WHEN (T5."Produktbuchungsgruppe" IN ('F_MIETW')) THEN ('Mietwagen') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen')) THEN ('Neuwagen') ELSE null END) = 'Gebrauchtwagen') THEN (T11."Name" + ' - ' + T12."Name") ELSE null END as c179,
	   T11."Name" as c180,
	   T11."Code" as c181,
	   T5."Verkäufernr Einkauf" as c182,
	   T5."Vorbesitzername" as c183,
	   T10."Name" as c184,
	   T5."Alte Debitorennr_" as c185,
	   CASE WHEN (((left(T1."Nr_",1)) IN ('5')) and (not T1."Nr_" IN ('500200'))) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c186,
	   CASE WHEN ((left(T1."Nr_",2)) IN ('57','58','59')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c187,
	   T5."Verkaufsdatum" as c188,
	   CASE WHEN ((left(T1."Nr_",4)) IN ('8900')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c189,
	   CASE WHEN ((left(T1."Nr_",4)) IN ('8810','8800','8801','8820','8830','8840','8850','8860','8870','8880')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c190,
	   CASE WHEN ((left(T1."Nr_",4)) IN ('8210')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c191,
	   CASE WHEN ((left(T1."Nr_",4)) IN ('7000','7010','7100','7101','7110','7510','7850','7800','7120','7111','7200','7030')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c192,
	   CASE WHEN ((left(T1."Nr_",1)) = '7') THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c193,
	   CASE WHEN ((left(T1."Nr_",4)) IN ('8000','8001','8010','8011','8100','8110','8111','8510','8220','8928','8934','5002','8112','8030','8200')) THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c194,
	   CASE WHEN ((left(T1."Nr_",1)) = '8') THEN ((CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END)) ELSE (0) END as c195,
	   CASE WHEN (((CASE WHEN ((T5."Altes Verkaufsdatum" <> convert(datetime, '1753-01-01 00:00:00.000')) and (T5."Alte Debitorennr_" <> '13000')) THEN ('ja') ELSE ('nein') END) = 'ja') and (((day(T5."Verkaufsdatum" - T2."Buchungsdatum"))) > 200)) THEN ('ja') WHEN ((T2."Fahrgestellnummer" IN ('W0VZM8EP2M1007537','VXKUPHMHDL4381854','W0V7D9EB2L4416085','W0V7D9ED5L4441628')) and (T5."Verkaufsdatum" BETWEEN convert(datetime, '2021-01-01 00:00:00.000') AND convert(datetime, '2021-01-31 00:00:00.000'))) THEN ('ja') ELSE ('nein') END as c196,
	   CASE WHEN ((T5."Altes Verkaufsdatum" <> convert(datetime, '1753-01-01 00:00:00.000')) and (T5."Alte Debitorennr_" <> '13000')) THEN ('ja') ELSE ('nein') END as c197,
	   CASE WHEN ((left(T1."Nr_",2)) IN ('71','81')) THEN ('Neuwagen') WHEN ((left(T1."Nr_",2)) IN ('72','82')) THEN ('Gebrauchtwagen') ELSE null END as c198,
	   (day(T5."Verkaufsdatum" - T2."Buchungsdatum")) as c199,
	   T5."Einkaufscode" as c200,
	   T5."Aktuelle Buchnummer" as c201,
	   T5."Alte Filialbuchnummer" as c202,
	   T5."Alte Buchnummer" as c203,
	   T5."Alter Eigenverkauf" as c204,
	   T5."Altes Verkaufsdatum" as c205,
	   T5."Alter Fahrzeugstatus" as c206,
	   T5."Aktuelle Filialbuchnummer" as c207,
	   T5."EDV Nummer" as c208,
	   T5."Anzahl Vorbesitzer" as c209,
	   T7."Debitorengruppencode" as c210,
	   T7."Nr_" + ' - ' + T7."Name" as c211,
	   T7."Name" as c212,
	   T7."Nr_" as c213,
	   T5."Debitorennr_" as c214,
	   CASE WHEN (T9."Name" IS NULL) THEN ('VB fehlt') ELSE (T9."Name") END as c215,
	   T9."Name" as c216,
	   T9."Code" as c217,
	   T5."Verkäufer Nr_ Verkauf" as c218,
	   T5."Markencode" as c219,
	   T5."Lagerortcode" as c220,
	   T8."Ausstattungscode" + ' - ' + T8."Beschreibung" as c221,
	   T8."Beschreibung" as c222,
	   T8."Ausstattungskennzeichen" as c223,
	   T8."Ausstattungscode" as c224,
	   CASE WHEN (((day(T5."Verkaufsdatum" - T5."Einkaufslieferdatum"))) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN (((day(T5."Verkaufsdatum" - T5."Einkaufslieferdatum"))) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN (((day(T5."Verkaufsdatum" - T5."Einkaufslieferdatum"))) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN (((day(T5."Verkaufsdatum" - T5."Einkaufslieferdatum"))) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN (((day(T5."Verkaufsdatum" - T5."Einkaufslieferdatum"))) > 180) THEN ('> 180 Tage') ELSE null END as c225,
	   (day(T5."Verkaufsdatum" - T5."Einkaufslieferdatum")) as c226,
	   (now()) as c227,
	   T5."Einkaufslieferdatum" as c228,
	   T5."Produktbuchungsgruppe" as c229,
	   T5."Fahrgestellnummer" as c230,
	   T2."Fahrgestellnummer" + ' - ' + T5."Produktbuchungsgruppe" + ' - ' + (T7."Nr_" + ' - ' + T7."Name") as c231,
	   CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_D')) THEN ('GW') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_R')) THEN ('GW') WHEN (T5."Produktbuchungsgruppe" IN ('F_MIETW')) THEN ('Mietwagen') ELSE ('Neuwagen') END as c232,
	   T1."Nr_" + ' - ' + T1."Name" as c233,
	   CASE WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_D')) THEN ('GW') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_R')) THEN ('GW') WHEN (T5."Produktbuchungsgruppe" IN ('F_MIETW')) THEN ('Mietwagen') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.','Mietwagen','Gebrauchtwagen','GW')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_D')) THEN ('GW') WHEN ((left(T5."Produktbuchungsgruppe",6)) IN ('F_GW_R')) THEN ('GW') WHEN (T5."Produktbuchungsgruppe" IN ('F_MIETW')) THEN ('Mietwagen') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen')) THEN ('Neuwagen') ELSE null END as c234,
	   (left((ucase(T6."Beschreibung")),4)) as c235,
	   T5."Fahrzeugstatus" as c236,
	   T2."Benutzer ID" as c237,
	   T2."Filialcode" as c238,
	   CASE WHEN (((left(T2."Sachkontonr_",1)) IN ('8')) or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((T2."Betrag")) * -1) ELSE (((T2."Betrag"))) END as c239,
	   T2."Sachkontonr_" as c240,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) + ' - ' + T3."Name" as c241,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) as c242,
	   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 c243,
	   T2."Belegnr_" + ' - ' + T2."Beschreibung" + ' - ' + T2."Benutzer ID" as c244,
	   CASE WHEN (((T5."Verkaufsdatum" = convert(datetime, '1753-01-01 00:00:00.000')) or (T5."Verkaufsdatum" IS NULL)) or (T5."Verkaufsdatum" >= convert(datetime, '2010-01-01 00:00:00.000'))) THEN (T2."Buchungsdatum") ELSE (T5."Verkaufsdatum") END as c245,
	   (T2."Menge") as c246,
	   (database()) as c247,
	   CASE WHEN (T2."Markencode" = '') THEN ('fehlt') ELSE (T2."Markencode") END as c248,
	   T4."Bezeichnung" as c249,
	   (T2."Betrag") as c250,
	   T4."Code" as c251,
	   T3."Name" as c252,
	   T3."Code" as c253,
	   T2."Fahrzeugklassecode" as c254,
	   T2."Storniert" as c255,
	   T2."Umgebucht" as c256,
	   T2."Fahrzeug-Kz" as c257,
	   T2."Buchnummer" as c258,
	   T2."Hauptbereich" as c259,
	   T2."Herkunftsnr_" as c260,
	   T2."Herkunftsart" as c261,
	   T2."Externe Belegnummer" as c262,
	   T2."Belegdatum" as c263,
	   T2."Habenbetrag" as c264,
	   T2."Sollbetrag" as c265,
	   T2."Transaktionsnr_" as c266,
	   T2."Gegenkontoart" as c267,
	   T2."Produktbuchungsgruppe" as c268,
	   T2."Geschäftsbuchungsgruppe" as c269,
	   T2."Buchungsart" as c270,
	   T2."MWSt Betrag" as c271,
	   T2."Nachbuchung" as c272,
	   T2."Herkunftscode" as c273,
	   T2."Markencode" as c274,
	   T2."Kostenstellencode" as c275,
	   T2."Gegenkontonr_" as c276,
	   T2."Beschreibung" as c277,
	   T2."Belegnr_" as c278,
	   T2."Belegart" as c279,
	   T2."Buchungsdatum" as c280,
	   T2."Lfd_ Nr_" as c281,
	   T1."Kostenstellen Buchung" as c282,
	   T1."Soll_Haben" as c283,
	   T1."GuV_Bilanz" as c284,
	   T1."Kontoart" as c285,
	   T1."Name" as c286,
	   T1."Nr_" as c287
from "CARLO"."import"."Sachkonto" T1,
	"CARLO"."import"."Fahrzeug_Ausstattung" T6,
	"CARLO"."import"."Debitor" T7,
	(("CARLO"."import"."Sachposten" T2 left outer join "CARLO"."import"."Kostenstelle" T3 on (T3."Code" = T2."Kostenstellencode") and (T3."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T4 on (T2."Filialcode" = T4."Code") and (T2."Client_DB" = T4."Client_DB")),
	((((("CARLO"."import"."Fahrzeug" T5 left outer join "CARLO"."import"."Fahrzeug_Ausstattung" T8 on ((T5."Fahrgestellnummer" = T8."Fahrgestellnummer") and (T8."Ausstattungskennzeichen" = 1)) and (T5."Client_DB" = T8."Client_DB")) left outer join "CARLO"."import"."Verkaeufer_Einkaeufer" T9 on (T5."Verkäufer Nr_ Verkauf" = T9."Code") and (T5."Client_DB" = T9."Client_DB")) left outer join "CARLO"."import"."Debitor" T10 on (T5."Alte Debitorennr_" = T10."Nr_") and (T5."Client_DB" = T10."Client_DB")) left outer join "CARLO"."import"."Verkaeufer_Einkaeufer" T11 on (T5."Verkäufernr Einkauf" = T11."Code") and (T5."Client_DB" = T11."Client_DB")) left outer join "CARLO"."import"."Kreditor" T12 on (T12."Nr_" = T5."Kreditorennr_") and (T12."Client_DB" = T5."Client_DB"))
where ((T1."Nr_" = T2."Sachkontonr_") and (T1."Client_DB" = T2."Client_DB")) and ((T2."Fahrgestellnummer" = T5."Fahrgestellnummer") and (T2."Client_DB" = T5."Client_DB")) and (((T6."Fahrgestellnummer" = T5."Fahrgestellnummer") and (T6."Zeilennr_" = 10000)) and (T5."Client_DB" = T6."Client_DB")) and ((T5."Debitorennr_" = T7."Nr_") and (T5."Client_DB" = T7."Client_DB"))
and ((((((((((T1."Nr_" BETWEEN '700000' AND '729900') or (T1."Nr_" BETWEEN '800000' AND '829900')) or (T1."Nr_" BETWEEN '851000' AND '890007')) or (T1."Nr_" BETWEEN '751000' AND '789900')) or (T1."Nr_" IN ('89500','89600','79500','79600','84430','73280','15350','211100','211200'))) or (T1."Nr_" BETWEEN '410000' AND '499999')) or ((T1."Nr_" BETWEEN '500000' AND '589999') and (T2."Kostenstellencode" IN ('10','11','13','14','20','21','22','23','24','25','26','29')))) and ((T2."Buchnummer" = T5."Aktuelle Buchnummer") or (T2."Buchnummer" = ' '))) and ((CASE WHEN (((CASE WHEN ((T5."Altes Verkaufsdatum" <> convert(datetime, '1753-01-01 00:00:00.000')) and (T5."Alte Debitorennr_" <> '13000')) THEN ('ja') ELSE ('nein') END) = 'ja') and (((day(T5."Verkaufsdatum" - T2."Buchungsdatum"))) > 200)) THEN ('ja') WHEN ((T2."Fahrgestellnummer" IN ('W0VZM8EP2M1007537','VXKUPHMHDL4381854','W0V7D9EB2L4416085','W0V7D9ED5L4441628')) and (T5."Verkaufsdatum" BETWEEN convert(datetime, '2021-01-01 00:00:00.000') AND convert(datetime, '2021-01-31 00:00:00.000'))) THEN ('ja') ELSE ('nein') END) = 'nein')) and (T5."Verkaufsdatum" >= convert(datetime, '2018-01-01 00:00:00.000')))
) D2
) D1
where ("Summe Erlös FZG" <> "Einsatz Sonstiges")
-- order by "Fahrgestellnummer" asc,"Bookkeep Date" asc