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

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