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

select "Nr_Rechnungskunde" as "Nr_Rechnungskunde",
	   "Name_Rechnungskunde" as "Name_Rechnungskunde",
	   "Geschäftsbuchungsgruppe_Rechnungskunde" as "Geschäftsbuchungsgruppe_Rechnungskunde",
	   "Plz Code_Rechnungskunde" as "Plz Code_Rechnungskunde",
	   "Nachname_Rechnungskunde" as "Nachname_Rechnungskunde",
	   "Vorname_Rechnungskunde" as "Vorname_Rechnungskunde",
	   "Kundenart_Rechnungskunde" as "Kundenart_Rechnungskunde",
	   "Nr_Fahrer" as "Nr_Fahrer",
	   "Name_Fahrer" as "Name_Fahrer",
	   "Geschäftsbuchungsgruppe_Fahrer" as "Geschäftsbuchungsgruppe_Fahrer",
	   "Plz Code_Fahrer" as "Plz Code_Fahrer",
	   "Nachname_Fahrer" as "Nachname_Fahrer",
	   "Vorname_Fahrer" as "Vorname_Fahrer",
	   "Kundenart_Fahrer" as "Kundenart_Fahrer",
	   "Kundengruppe_Rechnungskunde" as "Kundengruppe_Rechnungskunde",
	   "Kundengruppe_Fahrer" as "Kundengruppe_Fahrer",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Nr_Employee" as "Nr_Employee",
	   "Vorname_Employee" as "Vorname_Employee",
	   "Nachname_Employee" as "Nachname_Employee",
	   "Funktion_Employee" as "Funktion_Employee",
	   "Serviceberater" as "Serviceberater",
	   "Umsatzart" as "Umsatzart",
	   "Erstzulassung" as "Erstzulassung",
	   "Fahrzeugalter_Tage" as "Fahrzeugalter_Tage",
	   "Fahrzeugalter" as "Fahrzeugalter",
	   "FZG-Altersstaffel" as "FZG-Altersstaffel",
	   "Marke" as "Marke",
	   "Fabrikat" as "Fabrikat",
	   "Rechnung_Gutschrift" as "Rechnung_Gutschrift",
	   "Auftragsstatus" as "Auftragsstatus",
	   "Auftragsart" as "Auftragsart",
	   "Ländercode_Fahrer" as "Ländercode_Fahrer",
	   "Land" as "Land",
	   "PLZ_1_Stelle" as "PLZ_1_Stelle",
	   "PLZ_2_Stelle" as "PLZ_2_Stelle",
	   "PLZ_3_Stelle" as "PLZ_3_Stelle",
	   "PLZ_4_Stelle" as "PLZ_4_Stelle",
	   "PLZ" as "PLZ",
	   "Menge_Werkstattposten" as "Menge_Werkstattposten",
	   "Einstandsbetrag_Werkstattposten" as "Einstandsbetrag_Werkstattposten",
	   "Einsatz Teile" as "Einsatz Teile",
	   "Menge_Teile" as "Menge_Teile",
	   "DG_1" as "DG_1",
	   "DG_2" as "DG_2",
	   "DG" as "DG",
	   "Nr_Artikel" as "Nr_Artikel",
	   "Gmpd Code_Artikel" as "Gmpd Code_Artikel",
	   "Gmpd Subcode_Artikel" as "Gmpd Subcode_Artikel",
	   "Bonuscode_Artikel" as "Bonuscode_Artikel",
	   "GMPD_Group" as "GMPD_Group",
	   "Repair_Group_Desc" as "Repair_Group_Desc",
	   "DB1_><_EK" as "DB1_><_EK",
	   "Date" as "Date",
	   "Company_ID" as "Company_ID",
	   "Department_ID" as "Department_ID",
	   "Employee_Function" as "Employee_Function",
	   "Service_Advisor_Name" as "Service_Advisor_Name",
	   "Turnover_Type_Desc" as "Turnover_Type_Desc",
	   "Order_Desc_100" as "Order_Desc_100",
	   "Invoice_Desc_100" as "Invoice_Desc_100",
	   "Order_Desc_30" as "Order_Desc_30",
	   "Invoice_Desc_30" as "Invoice_Desc_30",
	   "Cost_Centre_ID" as "Cost_Centre_ID",
	   "Make_Desc" as "Make_Desc",
	   "Make_Group" as "Make_Group",
	   "Model_Desc" as "Model_Desc",
	   "Customer_Group_Owner" as "Customer_Group_Owner",
	   "Customer_Name_Owner" as "Customer_Name_Owner",
	   "Cust_Zipcode_Country" as "Cust_Zipcode_Country",
	   "Cust_Zipcode_First" as "Cust_Zipcode_First",
	   "Cust_Zipcode_Second" as "Cust_Zipcode_Second",
	   "Cust_Zipcode_Third" as "Cust_Zipcode_Third",
	   "Cust_Zipcode_Fourth" as "Cust_Zipcode_Fourth",
	   "Cust_Zipcode" as "Cust_Zipcode",
	   "Car_Age" as "Car_Age",
	   "Nr_Gutschrift" as "Nr_Gutschrift",
	   "Verk An Deb -nr" as "Verk An Deb -nr",
	   "Rech An Deb -nr" as "Rech An Deb -nr",
	   "Buchungsdatum" as "Buchungsdatum",
	   "Lieferdatum" as "Lieferdatum",
	   "Buchungsbeschreibung" as "Buchungsbeschreibung",
	   "Markencode" as "Markencode",
	   "Debitorenbuchungsgruppe" as "Debitorenbuchungsgruppe",
	   "Geschäftsbuchungsgruppe" as "Geschäftsbuchungsgruppe",
	   "Belegdatum" as "Belegdatum",
	   "Benutzer Id" as "Benutzer Id",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Amtliches Kennzeichen" as "Amtliches Kennzeichen",
	   "Modell" as "Modell",
	   "Werkstattauftragsnummer" as "Werkstattauftragsnummer",
	   "Filialcode" as "Filialcode",
	   "Serviceberaternr" as "Serviceberaternr",
	   "Zeilennr" as "Zeilennr",
	   "Werkst Auftragsnr_Verk_Rg_Zeile" as "Werkst Auftragsnr_Verk_Rg_Zeile",
	   "Werkst Auftragszeilennr_Verk_Rg_Zeile" as "Werkst Auftragszeilennr_Verk_Rg_Zeile",
	   "Art" as "Art",
	   "Nr_APO_Teile" as "Nr_APO_Teile",
	   "Beschreibung" as "Beschreibung",
	   "Einheit" as "Einheit",
	   "Menge_alles" as "Menge_alles",
	   COUNT("Werkstattauftragsnummer") OVER (partition by c240) as "Einstandspreis (mw)",
	   "Betrag" as "Betrag",
	   "Kostenstellencode" as "Kostenstellencode",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "Einstandspreis" as "Einstandspreis",
	   "Umsatz Lohn" as "Umsatz Lohn",
	   "Umsatz Teile" as "Umsatz Teile",
	   "Umsatz Sonst." as "Umsatz Sonst.",
	   "verk. Std." as "verk. Std.",
	   "Ausgleich-mit Belegnr" as "Ausgleich-mit Belegnr",
	   "Ist-zeit_Arb_Wert_Posten" as "Ist-zeit_Arb_Wert_Posten",
	   "Belegnr_Arb_Wert_Posten" as "Belegnr_Arb_Wert_Posten",
	   "ben. Zeit" as "ben. Zeit",
	   "ja/nein" as "ja/nein",
	   "Parts_Number" as "Parts_Number",
	   "Invoice_Credit_Note" as "Invoice_Credit_Note",
	   "Sign_DB1" as "Sign_DB1",
	   "Order_Status" as "Order_Status",
	   "Order_Type_Desc" as "Order_Type_Desc",
	   "Job_Amount" as "Job_Amount",
	   "Parts_Amount" as "Parts_Amount",
	   "Parts_Purch_Amount" as "Parts_Purch_Amount",
	   "Misc_Amount" as "Misc_Amount",
	   "Invoiced_Time" as "Invoiced_Time",
	   "Used_Time" as "Used_Time",
	   "Quantity" as "Quantity",
	   "Order_Count" as "Order_Count",
	   "Rabatt Lohn %" as "Rabatt Lohn %",
	   "Rabatt Lohn" as "Rabatt Lohn",
	   "Rabatt Teile %" as "Rabatt Teile %",
	   "Rabatt Teile" as "Rabatt Teile",
	   "Rabatt Sonst. %" as "Rabatt Sonst. %",
	   "Rabatt Sonst." as "Rabatt Sonst."
from 
(select c239 as "Nr_Rechnungskunde",
	   c238 as "Name_Rechnungskunde",
	   c237 as "Geschäftsbuchungsgruppe_Rechnungskunde",
	   c236 as "Plz Code_Rechnungskunde",
	   c235 as "Nachname_Rechnungskunde",
	   c234 as "Vorname_Rechnungskunde",
	   c233 as "Kundenart_Rechnungskunde",
	   c232 as "Nr_Fahrer",
	   c231 as "Name_Fahrer",
	   c199 as "Geschäftsbuchungsgruppe_Fahrer",
	   c192 as "Plz Code_Fahrer",
	   c230 as "Nachname_Fahrer",
	   c229 as "Vorname_Fahrer",
	   c228 as "Kundenart_Fahrer",
	   c227 as "Kundengruppe_Rechnungskunde",
	   c226 as "Kundengruppe_Fahrer",
	   '1' as "Hauptbetrieb",
	   c209 as "Standort",
	   c225 as "Nr_Employee",
	   c224 as "Vorname_Employee",
	   c223 as "Nachname_Employee",
	   c222 as "Funktion_Employee",
	   c208 as "Serviceberater",
	   c207 as "Umsatzart",
	   c221 as "Erstzulassung",
	   c220 as "Fahrzeugalter_Tage",
	   c219 as "Fahrzeugalter",
	   c191 as "FZG-Altersstaffel",
	   c201 as "Marke",
	   c184 as "Fabrikat",
	   'Gutschrift' as "Rechnung_Gutschrift",
	   'abgerechnet' as "Auftragsstatus",
	   c156 as "Auftragsart",
	   c218 as "Ländercode_Fahrer",
	   c197 as "Land",
	   c196 as "PLZ_1_Stelle",
	   c195 as "PLZ_2_Stelle",
	   c194 as "PLZ_3_Stelle",
	   c193 as "PLZ_4_Stelle",
	   c217 as "PLZ",
	   c216 as "Menge_Werkstattposten",
	   c215 as "Einstandsbetrag_Werkstattposten",
	   c153 as "Einsatz Teile",
	   c149 as "Menge_Teile",
	   -1 as "DG_1",
	   COUNT(c172) OVER (partition by c142) as "DG_2",
	   -1 / (COUNT(c172) OVER (partition by c142)) as "DG",
	   c158 as "Nr_Artikel",
	   c214 as "Gmpd Code_Artikel",
	   c213 as "Gmpd Subcode_Artikel",
	   c212 as "Bonuscode_Artikel",
	   c211 as "GMPD_Group",
	   c210 as "Repair_Group_Desc",
	   c157 as "DB1_><_EK",
	   c187 as "Date",
	   '1' as "Company_ID",
	   c209 as "Department_ID",
	   'Serviceberater' as "Employee_Function",
	   c208 as "Service_Advisor_Name",
	   c207 as "Turnover_Type_Desc",
	   c206 as "Order_Desc_100",
	   c205 as "Invoice_Desc_100",
	   c204 as "Order_Desc_30",
	   c203 as "Invoice_Desc_30",
	   c202 as "Cost_Centre_ID",
	   c201 as "Make_Desc",
	   c184 as "Make_Group",
	   c200 as "Model_Desc",
	   c199 as "Customer_Group_Owner",
	   c198 as "Customer_Name_Owner",
	   c197 as "Cust_Zipcode_Country",
	   c196 as "Cust_Zipcode_First",
	   c195 as "Cust_Zipcode_Second",
	   c194 as "Cust_Zipcode_Third",
	   c193 as "Cust_Zipcode_Fourth",
	   c192 as "Cust_Zipcode",
	   c191 as "Car_Age",
	   c190 as "Nr_Gutschrift",
	   c189 as "Verk An Deb -nr",
	   c188 as "Rech An Deb -nr",
	   c187 as "Buchungsdatum",
	   c186 as "Lieferdatum",
	   c185 as "Buchungsbeschreibung",
	   c184 as "Markencode",
	   c183 as "Debitorenbuchungsgruppe",
	   c182 as "Geschäftsbuchungsgruppe",
	   c181 as "Belegdatum",
	   c180 as "Benutzer Id",
	   c179 as "Fahrgestellnummer",
	   c178 as "Amtliches Kennzeichen",
	   c177 as "Modell",
	   c142 as "Werkstattauftragsnummer",
	   c176 as "Filialcode",
	   c175 as "Serviceberaternr",
	   c174 as "Zeilennr",
	   c173 as "Werkst Auftragsnr_Verk_Rg_Zeile",
	   c172 as "Werkst Auftragszeilennr_Verk_Rg_Zeile",
	   c171 as "Art",
	   c170 as "Nr_APO_Teile",
	   c169 as "Beschreibung",
	   c168 as "Einheit",
	   c167 as "Menge_alles",
	   c166 as "Betrag",
	   c165 as "Kostenstellencode",
	   c164 as "Produktbuchungsgruppe",
	   c163 as "Einstandspreis",
	   c155 as "Umsatz Lohn",
	   c154 as "Umsatz Teile",
	   c152 as "Umsatz Sonst.",
	   c151 as "verk. Std.",
	   c162 as "Ausgleich-mit Belegnr",
	   c161 as "Ist-zeit_Arb_Wert_Posten",
	   c160 as "Belegnr_Arb_Wert_Posten",
	   c150 as "ben. Zeit",
	   c159 as "ja/nein",
	   c158 as "Parts_Number",
	   'Gutschrift' as "Invoice_Credit_Note",
	   c157 as "Sign_DB1",
	   'abgerechnet' as "Order_Status",
	   c156 as "Order_Type_Desc",
	   c155 as "Job_Amount",
	   c154 as "Parts_Amount",
	   c153 as "Parts_Purch_Amount",
	   c152 as "Misc_Amount",
	   c151 as "Invoiced_Time",
	   c150 as "Used_Time",
	   c149 as "Quantity",
	   (-1 / (COUNT(c172) OVER (partition by c142))) as "Order_Count",
	   c148 as "Rabatt Lohn %",
	   c147 as "Rabatt Lohn",
	   c146 as "Rabatt Teile %",
	   c145 as "Rabatt Teile",
	   c144 as "Rabatt Sonst. %",
	   c143 as "Rabatt Sonst.",
	   c141 as c240
from 
(select (T5."Nr_" + '_' + (cast_numberToString(cast_integer(T9."Zeilennr_")))) as c141,
	   T5."Werkstattauftragsnummer" as c142,
	   CASE WHEN ((CASE WHEN (((not T9."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 T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabattbetrag"))) ELSE (0) END as c143,
	   CASE WHEN ((CASE WHEN (((not T9."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 T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabatt %"))) ELSE (0) END as c144,
	   CASE WHEN ((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabattbetrag"))) ELSE (0) END as c145,
	   CASE WHEN ((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabatt %"))) ELSE (0) END as c146,
	   ((T9."Zeilenrabattbetrag")) as c147,
	   ((T9."Zeilenrabatt %")) as c148,
	   (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Menge"))))) ELSE (0) END) as c149,
	   (((T10."Ist-Zeit")) * -1) as c150,
	   (CASE WHEN ((T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE')) and (T9."Nr_" <> 'E002540')) THEN (((T9."Menge")) * -1) ELSE (0) END) as c151,
	   (CASE WHEN (((not T9."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 T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) as c152,
	   (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T8."Einstandsbetrag"))) ELSE (0) END) as c153,
	   (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) as c154,
	   (CASE WHEN ((T9."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 (T9."Nr_" <> 'E002540')) THEN ((((T9."Betrag"))) * -1) ELSE (0) END) as c155,
	   (CASE WHEN (T5."Nr_" LIKE 'T%') THEN ('Teile') WHEN (T5."Nr_" LIKE 'W%') THEN ('Service') ELSE null END) as c156,
	   (CASE WHEN ((CASE WHEN ((T9."Produktbuchungsgruppe" LIKE '%TZ%') or (T9."Produktbuchungsgruppe" LIKE '%REIF%')) THEN (((((T9."Betrag"))))) ELSE (0) END) - (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T8."Einstandsbetrag"))) ELSE (0) END) < 0) THEN ('VK < EK') ELSE ('VK > EK') END) as c157,
	   T10."Einheitencode" as c158,
	   CASE WHEN ((((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T5."Ausgleich-mit Belegnr_" = T10."Belegnr_")) or (((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T10."Belegnr_" IS NULL))) THEN ('ja') WHEN ((((CASE WHEN (((not T9."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 T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T5."Ausgleich-mit Belegnr_" = T10."Belegnr_")) or (((CASE WHEN (((not T9."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 T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T10."Belegnr_" IS NULL))) THEN ('ja') WHEN (((T9."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 ((T5."Ausgleich-mit Belegnr_" = T10."Belegnr_") or (T10."Belegnr_" IS NULL)))) THEN ('ja') WHEN (((T9."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 ((T5."Ausgleich-mit Belegnr_" = '') and (T10."Belegnr_" LIKE 'WVRG%')))) THEN ('ja') ELSE ('nein') END as c159,
	   T10."Belegnr_" as c160,
	   (T10."Ist-Zeit") as c161,
	   T5."Ausgleich-mit Belegnr_" as c162,
	   (T9."Einstandspreis") as c163,
	   T9."Produktbuchungsgruppe" as c164,
	   T9."Kostenstellencode" as c165,
	   (T9."Betrag") as c166,
	   (T9."Menge") as c167,
	   T9."Einheit" as c168,
	   T9."Beschreibung" as c169,
	   T9."Nr_" as c170,
	   T9."Art" as c171,
	   T9."Werkst_ Auftragszeilennr_" as c172,
	   T9."Werkst_ Auftragsnr_" as c173,
	   T9."Zeilennr_" as c174,
	   T5."Serviceberaternr_" as c175,
	   T5."Filialcode" as c176,
	   T5."Modell" as c177,
	   T5."Amtliches Kennzeichen" as c178,
	   T5."Fahrgestellnummer" as c179,
	   T5."Benutzer ID" as c180,
	   T5."Belegdatum" as c181,
	   T5."Geschäftsbuchungsgruppe" as c182,
	   T5."Debitorenbuchungsgruppe" as c183,
	   T5."Markencode" as c184,
	   T5."Buchungsbeschreibung" as c185,
	   T5."Lieferdatum" as c186,
	   T5."Buchungsdatum" as c187,
	   T5."Rech_ an Deb_-Nr_" as c188,
	   T5."Verk_ an Deb_-Nr_" as c189,
	   T5."Nr_" as c190,
	   (CASE WHEN ((CASE WHEN (T7."Erstzulassung" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - 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(T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END) as c191,
	   T2."PLZ Code" as c192,
	   ((left(T2."PLZ Code",4))) as c193,
	   ((left(T2."PLZ Code",3))) as c194,
	   ((left(T2."PLZ Code",2))) as c195,
	   ((left(T2."PLZ Code",1))) as c196,
	   (CASE WHEN (((T2."Ländercode" IS NULL) or (T2."Ländercode" = ' ')) or (T2."Ländercode" IN ('D','DE'))) THEN ('Deutschland') ELSE ('Ausland') END) as c197,
	   T2."Nr_" + ' - ' + T2."Name" as c198,
	   T2."Geschäftsbuchungsgruppe" as c199,
	   (left((ucase(T5."Modell")),3)) as c200,
	   (CASE WHEN (T5."Markencode" IN ('OPEL')) THEN (T5."Markencode") ELSE ('Fremdfabrikat') END) as c201,
	   T9."Kostenstellencode" + ' - ' + T12."Name" as c202,
	   CASE WHEN ((day((now()) - T5."Buchungsdatum")) <= 35) THEN (T5."Nr_" + ' - ' + (CASE WHEN (T6."Nr_" IS NOT NULL) THEN (T6."Nr_" + ' - ' + T6."Vorname" + ' ' + T6."Nachname") ELSE ('SB fehlt') END) + ' - ' + T1."Name" + ' - ' + T5."Fahrgestellnummer") ELSE ('Rechnungen älter 35 Tage') END as c203,
	   CASE WHEN ((day((now()) - T5."Buchungsdatum")) <= 35) THEN (T5."Werkstattauftragsnummer") ELSE ('Aufträge älter 35 Tage') END as c204,
	   CASE WHEN ((day((now()) - T5."Buchungsdatum")) <= 100) THEN (T5."Nr_" + ' - ' + T1."Name" + ' - ' + T5."Fahrgestellnummer") ELSE ('Rechnungen älter 100 Tage') END as c205,
	   CASE WHEN ((day((now()) - T5."Buchungsdatum")) <= 100) THEN (T5."Werkstattauftragsnummer" + ' - ' + T2."Name") ELSE ('Aufträge älter 100 Tage') END as c206,
	   (CASE WHEN (T1."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T1."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) as c207,
	   (CASE WHEN (T6."Nr_" IS NOT NULL) THEN (T6."Nr_" + ' - ' + T6."Vorname" + ' ' + T6."Nachname") ELSE ('SB fehlt') END) as c208,
	   (CASE WHEN ('1' = '2') THEN ('20') ELSE (T5."Filialcode") END) as c209,
	   CASE WHEN (T9."Produktbuchungsgruppe" LIKE '%LOHN%') THEN (T9."Produktbuchungsgruppe") ELSE null END as c210,
	   T11."GMPD Code" + T11."GMPD Subcode" as c211,
	   T11."Bonuscode" as c212,
	   T11."GMPD Subcode" as c213,
	   T11."GMPD Code" as c214,
	   (T8."Einstandsbetrag") as c215,
	   (T8."Menge") as c216,
	   (left(T2."PLZ Code",5)) as c217,
	   T2."Ländercode" as c218,
	   (CASE WHEN (T7."Erstzulassung" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 as c219,
	   CASE WHEN (T7."Erstzulassung" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END as c220,
	   T7."Erstzulassung" as c221,
	   T6."Funktion" as c222,
	   T6."Nachname" as c223,
	   T6."Vorname" as c224,
	   T6."Nr_" as c225,
	   T4."Code" + ' - ' + T4."Beschreibung" as c226,
	   T3."Code" + ' - ' + T3."Beschreibung" as c227,
	   T2."Kundenart" as c228,
	   T2."Vorname" as c229,
	   T2."Nachname" as c230,
	   T2."Name" as c231,
	   T2."Nr_" as c232,
	   T1."Kundenart" as c233,
	   T1."Vorname" as c234,
	   T1."Nachname" as c235,
	   T1."PLZ Code" as c236,
	   T1."Geschäftsbuchungsgruppe" as c237,
	   T1."Name" as c238,
	   T1."Nr_" as c239
from (((((("CARLO"."import"."Verkaufsgutschriftskopf" T5 left outer join "CARLO"."import"."Debitor" T1 on (T5."Rech_ an Deb_-Nr_" = T1."Nr_") and (T5."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Debitor" T2 on (T5."Verk_ an Deb_-Nr_" = T2."Nr_") and (T5."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T3 on (T3."Code" = T1."Geschäftsbuchungsgruppe") and (T3."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T4 on (T4."Code" = T2."Geschäftsbuchungsgruppe") and (T4."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee" T6 on (T5."Serviceberaternr_" = T6."Nr_") and (T5."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T5."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T5."Client_DB" = T7."Client_DB")),
	(((("CARLO"."import"."Verkaufsgutschriftszeile" T9 left outer join "CARLO"."import"."Werkstattposten" T8 on ((((T9."Belegnr_" = T8."Belegnr_") and (T9."Nr_" = T8."Nr_")) and (T8."Nummernserie" LIKE 'WSG%')) and (T8."Client_DB" = T9."Client_DB")) and (T9."Werkst_ Auftragszeilennr_" = T8."Auftragszeilennr_")) left outer join "CARLO"."import"."Arbeitswertposten" T10 on ((T9."Werkst_ Auftragsnr_" = T10."Werkstattauftragsnr_") and (T9."Werkst_ Auftragszeilennr_" = T10."Werkstattauftragszeilennr_")) and (T9."Client_DB" = T10."Client_DB")) left outer join "CARLO"."import"."Artikel" T11 on (T11."Nr_" = T8."Nr_") and (T11."Client_DB" = T8."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T12 on T12."Code" = T9."Kostenstellencode")
where ((T5."Nr_" = T9."Belegnr_") and (T5."Client_DB" = T9."Client_DB"))
and ((((T5."Nr_" LIKE 'W%') and ((od_year(T5."Buchungsdatum")) >= (od_year((now()))) - 2)) and (not T9."Art" IN (0,11,12))) and (T9."Produktbuchungsgruppe" <> 'DIREKT19'))
) D1
) D4
-- order by "Nr_Gutschrift" asc,"Werkst Auftragszeilennr_Verk_Rg_Zeile" asc,"Werkst Auftragsnr_Verk_Rg_Zeile" asc