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

select "Nr" as "Nr",
	   "Name" as "Name",
	   "Lfd Nr" as "Lfd Nr",
	   "Debitorennr" as "Debitorennr",
	   "Buchungsdatum" as "Buchungsdatum",
	   "Belegart" as "Belegart",
	   "Belegnr" as "Belegnr",
	   "Beschreibung" as "Beschreibung",
	   "Betrag_ori" as "Betrag_ori",
	   "Restbetrag" as "Restbetrag",
	   "Kostenstellencode" as "Kostenstellencode",
	   "Markencode" as "Markencode",
	   "Verkäufercode" as "Verkäufercode",
	   "Benutzer Id" as "Benutzer Id",
	   "Herkunftscode" as "Herkunftscode",
	   "Offen" as "Offen",
	   "Fälligkeitsdatum" as "Fälligkeitsdatum",
	   "Gegenkontoart" as "Gegenkontoart",
	   "Gegenkontonr" as "Gegenkontonr",
	   "Transaktionsnr" as "Transaktionsnr",
	   "Sollbetrag" as "Sollbetrag",
	   "Habenbetrag" as "Habenbetrag",
	   "Belegdatum" as "Belegdatum",
	   "Externe Belegnummer" as "Externe Belegnummer",
	   "Filialcode" as "Filialcode",
	   "Hauptbereich" as "Hauptbereich",
	   "Kundengruppencode" as "Kundengruppencode",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Kassenbelegnr" as "Kassenbelegnr",
	   "Bemerkung" as "Bemerkung",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Betrieb" as "Betrieb",
	   "Bookkeep Date" as "Bookkeep Date",
	   "Filial-Code" as "Filial-Code",
	   "Filialbezeichnung" as "Filialbezeichnung",
	   "Standort" as "Standort",
	   "Verkäufer-Code" as "Verkäufer-Code",
	   "Verkäufer-Name" as "Verkäufer-Name",
	   "Sel Name" as "Sel Name",
	   "Kunde" as "Kunde",
	   "Fälligkeit_Zeichen" as "Fälligkeit_Zeichen",
	   "Beleg" as "Beleg",
	   "KST-Code" as "KST-Code",
	   "KST-Name" as "KST-Name",
	   "KST_1" as "KST_1",
	   "Kostenstelle" as "Kostenstelle",
	   "Heute" as "Heute",
	   "Tage" as "Tage",
	   "Staffel" as "Staffel",
	   "offen" as "offen",
	   "Rechnungsbetrag" as "Rechnungsbetrag",
	   "KD-Gruppen-Code" as "KD-Gruppen-Code",
	   "KD-Gruppe-Beschreibung" as "KD-Gruppe-Beschreibung",
	   "Sel Name" as "Sel Name",
	   "Bereich" as "Bereich",
	   SUM(c72 at "Nr","Belegnr") OVER (partition by "Nr","Belegnr") as "Gesamtsaldo Kunde",
	   "Nachname" as "Nachname",
	   "Vorname" as "Vorname",
	   "Kundenart_ori" as "Kundenart_ori",
	   "Kundenart" as "Kundenart",
	   "Einsatzwert" as "Einsatzwert",
	   "Menge" as "Menge",
	   "Vk-preis" as "Vk-preis",
	   "Betrag" as "Betrag",
	   "Summe Betrag" as "Summe Betrag",
	   "Umsatz Teile" as "Umsatz Teile",
	   "Einsatz Teile" as "Einsatz Teile",
	   "Zeilennr" as "Zeilennr",
	   "Beschreibung" as "Beschreibung",
	   "Beschreibung 2" as "Beschreibung 2",
	   "Debitorengruppencode" as "Debitorengruppencode"
from 
(select "Nr" as "Nr",
	   "Name" as "Name",
	   "Lfd Nr" as "Lfd Nr",
	   "Debitorennr" as "Debitorennr",
	   "Buchungsdatum" as "Buchungsdatum",
	   "Belegart" as "Belegart",
	   "Belegnr" as "Belegnr",
	   "Beschreibung" as "Beschreibung",
	   "Betrag_ori" as "Betrag_ori",
	   "Restbetrag" as "Restbetrag",
	   "Kostenstellencode" as "Kostenstellencode",
	   "Markencode" as "Markencode",
	   "Verkäufercode" as "Verkäufercode",
	   "Benutzer Id" as "Benutzer Id",
	   "Herkunftscode" as "Herkunftscode",
	   "Offen" as "Offen",
	   "Fälligkeitsdatum" as "Fälligkeitsdatum",
	   "Gegenkontoart" as "Gegenkontoart",
	   "Gegenkontonr" as "Gegenkontonr",
	   "Transaktionsnr" as "Transaktionsnr",
	   "Sollbetrag" as "Sollbetrag",
	   "Habenbetrag" as "Habenbetrag",
	   "Belegdatum" as "Belegdatum",
	   "Externe Belegnummer" as "Externe Belegnummer",
	   "Filialcode" as "Filialcode",
	   "Hauptbereich" as "Hauptbereich",
	   "Kundengruppencode" as "Kundengruppencode",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Kassenbelegnr" as "Kassenbelegnr",
	   "Bemerkung" as "Bemerkung",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Betrieb" as "Betrieb",
	   "Bookkeep Date" as "Bookkeep Date",
	   "Filial-Code" as "Filial-Code",
	   "Filialbezeichnung" as "Filialbezeichnung",
	   "Standort" as "Standort",
	   "Verkäufer-Code" as "Verkäufer-Code",
	   "Verkäufer-Name" as "Verkäufer-Name",
	   "Sel Name" as "Sel Name",
	   "Kunde" as "Kunde",
	   "Fälligkeit_Zeichen" as "Fälligkeit_Zeichen",
	   "Beleg" as "Beleg",
	   "KST-Code" as "KST-Code",
	   "KST-Name" as "KST-Name",
	   "KST_1" as "KST_1",
	   "Kostenstelle" as "Kostenstelle",
	   "Heute" as "Heute",
	   "Tage" as "Tage",
	   "Staffel" as "Staffel",
	   "offen" as "offen",
	   "Rechnungsbetrag" as "Rechnungsbetrag",
	   "KD-Gruppen-Code" as "KD-Gruppen-Code",
	   "KD-Gruppe-Beschreibung" as "KD-Gruppe-Beschreibung",
	   "Sel Name" as "Sel Name",
	   "Bereich" as "Bereich",
	   "Nachname" as "Nachname",
	   "Vorname" as "Vorname",
	   "Kundenart_ori" as "Kundenart_ori",
	   "Kundenart" as "Kundenart",
	   "Einsatzwert" as "Einsatzwert",
	   "Menge" as "Menge",
	   "Vk-preis" as "Vk-preis",
	   "Betrag" as "Betrag",
	   SUM("Betrag") OVER (partition by "Nr","Belegnr") as "Summe Betrag",
	   "Umsatz Teile" as "Umsatz Teile",
	   "Einsatz Teile" as "Einsatz Teile",
	   "Zeilennr" as "Zeilennr",
	   "Beschreibung" as "Beschreibung",
	   "Beschreibung 2" as "Beschreibung 2",
	   "Debitorengruppencode" as "Debitorengruppencode",
	   SUM("Restbetrag") OVER (partition by "Nr","Belegnr") as c72
from 
(select c80 as "Nr",
	   c135 as "Name",
	   c134 as "Lfd Nr",
	   c133 as "Debitorennr",
	   c114 as "Buchungsdatum",
	   c132 as "Belegart",
	   c79 as "Belegnr",
	   c131 as "Beschreibung",
	   c130 as "Betrag_ori",
	   c100 as "Restbetrag",
	   c129 as "Kostenstellencode",
	   c128 as "Markencode",
	   c127 as "Verkäufercode",
	   c96 as "Benutzer Id",
	   c126 as "Herkunftscode",
	   c125 as "Offen",
	   c107 as "Fälligkeitsdatum",
	   1 / COUNT(c132) OVER (partition by c79) as "Gegenkontoart",
	   c115 as "Gegenkontonr",
	   c124 as "Transaktionsnr",
	   c123 as "Sollbetrag",
	   c122 as "Habenbetrag",
	   c121 as "Belegdatum",
	   c120 as "Externe Belegnummer",
	   c111 as "Filialcode",
	   c119 as "Hauptbereich",
	   c118 as "Kundengruppencode",
	   c117 as "Fahrgestellnummer",
	   c116 as "Kassenbelegnr",
	   c115 as "Bemerkung",
	   '1' as "Hauptbetrieb",
	   c111 as "Betrieb",
	   c114 as "Bookkeep Date",
	   c113 as "Filial-Code",
	   c112 as "Filialbezeichnung",
	   c111 as "Standort",
	   c110 as "Verkäufer-Code",
	   c109 as "Verkäufer-Name",
	   c109 as "Sel Name",
	   c108 as "Kunde",
	   c107 as "Fälligkeit_Zeichen",
	   c106 as "Beleg",
	   c105 as "KST-Code",
	   c104 as "KST-Name",
	   c103 as "KST_1",
	   c102 as "Kostenstelle",
	   c101 as "Heute",
	   0 as "Tage",
	   CASE WHEN (0 BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (0 BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (0 BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (0 BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (0 > 84) THEN ('> 12 Wochen') WHEN (0 < 0) THEN ('noch nicht fällig') ELSE null END as "Staffel",
	   c100 as "offen",
	   c99 as "Rechnungsbetrag",
	   c98 as "KD-Gruppen-Code",
	   c97 as "KD-Gruppe-Beschreibung",
	   c96 as "Sel Name",
	   c95 as "Bereich",
	   c94 as "Nachname",
	   c93 as "Vorname",
	   c92 as "Kundenart_ori",
	   c91 as "Kundenart",
	   c90 as "Einsatzwert",
	   c89 as "Menge",
	   c88 as "Vk-preis",
	   c87 as "Betrag",
	   c86 as "Umsatz Teile",
	   c85 as "Einsatz Teile",
	   c84 as "Zeilennr",
	   c83 as "Beschreibung",
	   c82 as "Beschreibung 2",
	   c81 as "Debitorengruppencode"
from 
(select T2."Belegnr_" as c79,
	   T1."Nr_" as c80,
	   T1."Debitorengruppencode" as c81,
	   T7."Beschreibung 2" as c82,
	   T7."Beschreibung" as c83,
	   T7."Zeilennr_" as c84,
	   CASE WHEN (((((T7."Menge" * T7."Einstandspreis (MW)")))) IS NULL) THEN (0) ELSE (((((T7."Menge" * T7."Einstandspreis (MW)"))))) END as c85,
	   CASE WHEN (((T7."Betrag")) IS NULL) THEN (((T2."Betrag") / 1.19) / 1.19) ELSE (((T7."Betrag"))) END as c86,
	   (T7."Betrag") as c87,
	   (T7."VK-Preis") as c88,
	   T7."Menge" as c89,
	   (((T7."Menge" * T7."Einstandspreis (MW)"))) as c90,
	   CASE WHEN (T1."Geschäftsbuchungsgruppe" LIKE 'FW%') THEN ('Fremdwerkst.') ELSE ('andere Kunden') END as c91,
	   T1."Kundenart" as c92,
	   T1."Vorname" as c93,
	   T1."Nachname" as c94,
	   CASE WHEN (T2."Hauptbereich" = 0) THEN ('Sonstige') WHEN (T2."Hauptbereich" = 1) THEN ('T & Z') WHEN (T2."Hauptbereich" = 2) THEN ('Fahrzeuge') WHEN (T2."Hauptbereich" = 3) THEN ('Service') WHEN (T2."Hauptbereich" = 5) THEN ('Kassenbuchung') ELSE ('nicht zuzuordnen') END as c95,
	   T2."Benutzer ID" as c96,
	   T6."Beschreibung" as c97,
	   T6."Code" as c98,
	   (T2."Betrag") / 1.19 as c99,
	   T2."Restbetrag" as c100,
	   (now()) as c101,
	   CASE WHEN ((left(((od_right(T5."Code",2))),1)) = '1') THEN ('1') WHEN ((left(((od_right(T5."Code",2))),1)) = '2') THEN ('2') WHEN ((left(((od_right(T5."Code",2))),1)) = '3') THEN ('6') WHEN ((left(((od_right(T5."Code",2))),2)) = '41') THEN ('3') WHEN ((left(((od_right(T5."Code",2))),2)) = '44') THEN ('4') WHEN ((left(((od_right(T5."Code",2))),2)) = '45') THEN ('5') WHEN ((left(((od_right(T5."Code",2))),1)) = '5') THEN ('7') WHEN (((left(((od_right(T5."Code",2))),1)) = '0') or ((left(((od_right(T5."Code",2))),1)) = '9')) THEN ('0') ELSE ('nicht zuzuordnen') END as c102,
	   (od_right(T5."Code",2)) as c103,
	   T5."Name" as c104,
	   T5."Code" as c105,
	   T2."Belegnr_" + ' - ' + T2."Beschreibung" as c106,
	   (T2."Fälligkeitsdatum") as c107,
	   T1."Nr_" + ' - ' + T1."Name" as c108,
	   T4."Name" as c109,
	   T4."Code" as c110,
	   (T2."Filialcode") as c111,
	   T3."Bezeichnung" as c112,
	   T3."Code" as c113,
	   T2."Buchungsdatum" as c114,
	   T1."PLZ Code" as c115,
	   T2."Kassenbelegnr_" as c116,
	   T2."Fahrgestellnummer" as c117,
	   T2."Kundengruppencode" as c118,
	   T2."Hauptbereich" as c119,
	   T2."Externe Belegnummer" as c120,
	   T2."Belegdatum" as c121,
	   T2."Habenbetrag" as c122,
	   T2."Sollbetrag" as c123,
	   T2."Transaktionsnr_" as c124,
	   T2."Offen" as c125,
	   T2."Herkunftscode" as c126,
	   T2."Verkäufercode" as c127,
	   T2."Markencode" as c128,
	   T2."Kostenstellencode" as c129,
	   T2."Betrag" as c130,
	   T2."Beschreibung" as c131,
	   T2."Belegart" as c132,
	   T2."Debitorennr_" as c133,
	   T2."Lfd_ Nr_" as c134,
	   T1."Name" as c135
from (((((("CARLO"."import"."Debitorenposten" T2 left outer join "CARLO"."import"."Debitor" T1 on (T1."Nr_" = T2."Debitorennr_") and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T3 on (T2."Filialcode" = T3."Code") and (T2."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Verkaeufer_Einkaeufer" T4 on (T2."Verkäufercode" = T4."Code") and (T2."Client_DB" = T4."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T2."Kostenstellencode" = T5."Code") and (T2."Client_DB" = T5."Client_DB")) left outer join "CARLO"."import"."Debitorgruppe" T6 on (T2."Kundengruppencode" = T6."Code") and (T2."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Verkaufsrechnungszeile" T7 on (T2."Belegnr_" = T7."Belegnr_") and (T2."Client_DB" = T7."Client_DB"))
where ((((not T1."Nr_" IN ('899993','899999')) and (T2."Buchungsdatum" >= convert(datetime, '2018-01-01 00:00:00.000'))) and (T2."Belegnr_" LIKE 'T%')) and (T2."Belegart" IN (3,2)))
) D2
) D5
-- order by "Nr" asc,"Belegnr" asc,"Name" asc,"Lfd Nr" asc,"Debitorennr" asc,"Buchungsdatum" asc,"Belegart" asc,"Beschreibung" asc,"Restbetrag" asc,"Kostenstellencode" asc,"Markencode" asc,"Verkäufercode" asc,"Benutzer Id" asc,"Herkunftscode" asc,"Offen" asc,"Fälligkeitsdatum" asc,"Transaktionsnr" asc,"Sollbetrag" asc,"Habenbetrag" asc,"Belegdatum" asc,"Externe Belegnummer" asc,"Filialcode" asc,"Hauptbereich" asc,"Kundengruppencode" asc,"Fahrgestellnummer" asc,"Kassenbelegnr" asc,"Filial-Code" asc,"Filialbezeichnung" asc,"Verkäufer-Code" asc,"Verkäufer-Name" asc,"KST-Code" asc,"KST-Name" asc,"KD-Gruppen-Code" asc,"KD-Gruppe-Beschreibung" asc,"KST_1" asc,"Kostenstelle" asc,"Heute" asc,"Bereich" asc,"Beleg" asc,"Bemerkung" asc,"Kunde" asc,"Betrag_ori" asc,"Rechnungsbetrag" asc
) D1