C11/System/CARLO/IQD/OP/op_eds.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" as "Betrag",
	   "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_Verkauf" as "Sel Name_Verkauf",
	   "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_ori" as "Sel Name_ori",
	   "Bereich" as "Bereich",
	   SUM(c62 at "Nr") OVER (partition by "Nr") as "Gesamtsaldo Kunde",
	   "Nachname" as "Nachname",
	   "Vorname" as "Vorname",
	   "Sel Name" as "Sel Name",
	   "Abwarten" as "Abwarten",
	   "Forderungsart" as "Forderungsart"
from 
(select c65 as "Nr",
	   c120 as "Name",
	   c119 as "Lfd Nr",
	   c118 as "Debitorennr",
	   c117 as "Buchungsdatum",
	   c116 as "Belegart",
	   c115 as "Belegnr",
	   c114 as "Beschreibung",
	   c113 as "Betrag",
	   c112 as "Restbetrag",
	   c111 as "Kostenstellencode",
	   c110 as "Markencode",
	   c109 as "Verkäufercode",
	   c72 as "Benutzer Id",
	   c108 as "Herkunftscode",
	   c107 as "Offen",
	   c92 as "Fälligkeitsdatum",
	   c106 as "Gegenkontoart",
	   c105 as "Gegenkontonr",
	   c104 as "Transaktionsnr",
	   c103 as "Sollbetrag",
	   c102 as "Habenbetrag",
	   c101 as "Belegdatum",
	   c100 as "Externe Belegnummer",
	   c99 as "Filialcode",
	   c98 as "Hauptbereich",
	   c97 as "Kundengruppencode",
	   c96 as "Fahrgestellnummer",
	   c95 as "Kassenbelegnr",
	   c94 as "Bemerkung",
	   c93 as "Hauptbetrieb",
	   c89 as "Betrieb",
	   c92 as "Bookkeep Date",
	   c91 as "Filial-Code",
	   c90 as "Filialbezeichnung",
	   c89 as "Standort",
	   c88 as "Verkäufer-Code",
	   c87 as "Verkäufer-Name",
	   c87 as "Sel Name_Verkauf",
	   c86 as "Kunde",
	   c85 as "Fälligkeit_Zeichen",
	   c84 as "Beleg",
	   c83 as "KST-Code",
	   c82 as "KST-Name",
	   c81 as "KST_1",
	   c80 as "Kostenstelle",
	   c79 as "Heute",
	   c78 as "Tage",
	   c77 as "Staffel",
	   c76 as "offen",
	   c75 as "Rechnungsbetrag",
	   c74 as "KD-Gruppen-Code",
	   c73 as "KD-Gruppe-Beschreibung",
	   c72 as "Sel Name_ori",
	   c71 as "Bereich",
	   c70 as "Nachname",
	   c69 as "Vorname",
	   c68 as "Sel Name",
	   c67 as "Abwarten",
	   c66 as "Forderungsart",
	   SUM(c76) OVER (partition by c65) as c62
from 
(select T1."Nr_" as c65,
	   CASE WHEN (T2."Abwarten" LIKE 'V%') THEN ('Versicherung') WHEN (T2."Abwarten" = 'ANW') THEN ('Anwalt') WHEN (T2."Abwarten" = 'STB') THEN ('Steuerbüro') WHEN (T2."Abwarten" = 'KSI') THEN ('KSI') WHEN (T2."Abwarten" = 'DAR') THEN ('Darlehen') WHEN ((T2."Abwarten" = 'GWL') or (T1."Nr_" IN ('660814','555555','566666','115500'))) THEN ('GWL') WHEN ((T2."Abwarten" = 'RZV') or (T2."Beschreibung" LIKE '%RZV%')) THEN ('Ratenzahlung') ELSE ('Kundenforderung') END as c66,
	   T2."Abwarten" as c67,
	   CASE WHEN ((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) = 'Fahrzeuge') THEN (T5."Name") ELSE (T2."Benutzer ID") END as c68,
	   T1."Vorname" as c69,
	   T1."Nachname" as c70,
	   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 c71,
	   T2."Benutzer ID" as c72,
	   T7."Beschreibung" as c73,
	   T7."Code" as c74,
	   (convert(float, T2."Betrag")) as c75,
	   (convert(float, T2."Restbetrag")) as c76,
	   CASE WHEN (((day(((now())) - T2."Fälligkeitsdatum"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((day(((now())) - T2."Fälligkeitsdatum"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((day(((now())) - T2."Fälligkeitsdatum"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((day(((now())) - T2."Fälligkeitsdatum"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((day(((now())) - T2."Fälligkeitsdatum"))) > 84) THEN ('> 12 Wochen') WHEN (((day(((now())) - T2."Fälligkeitsdatum"))) < 0) THEN ('noch nicht fällig') ELSE null END as c77,
	   (day(((now())) - T2."Fälligkeitsdatum")) as c78,
	   (now()) as c79,
	   CASE WHEN ((left(((od_right(T6."Code",2))),1)) = '1') THEN ('1') WHEN ((left(((od_right(T6."Code",2))),1)) = '2') THEN ('2') WHEN ((left(((od_right(T6."Code",2))),1)) = '3') THEN ('6') WHEN ((left(((od_right(T6."Code",2))),2)) = '41') THEN ('3') WHEN ((left(((od_right(T6."Code",2))),2)) = '44') THEN ('4') WHEN ((left(((od_right(T6."Code",2))),2)) = '45') THEN ('5') WHEN ((left(((od_right(T6."Code",2))),1)) = '5') THEN ('7') WHEN (((left(((od_right(T6."Code",2))),1)) = '0') or ((left(((od_right(T6."Code",2))),1)) = '9')) THEN ('0') ELSE ('nicht zuzuordnen') END as c80,
	   (od_right(T6."Code",2)) as c81,
	   T6."Name" as c82,
	   T6."Code" as c83,
	   T2."Belegnr_" + ' - ' + T2."Beschreibung" + ' - ' + ((convert(varchar(50), year(T2."Fälligkeitsdatum")) + '-' + convert(varchar(50), month(T2."Fälligkeitsdatum")) + '-' + convert(varchar(50), day(T2."Fälligkeitsdatum")))) + ' - ' + T2."Abwarten" as c84,
	   (convert(varchar(50), year(T2."Fälligkeitsdatum")) + '-' + convert(varchar(50), month(T2."Fälligkeitsdatum")) + '-' + convert(varchar(50), day(T2."Fälligkeitsdatum"))) as c85,
	   T1."Nachname" + ' ' + T1."Vorname" + ' - ' + T1."Nr_" as c86,
	   T5."Name" as c87,
	   T5."Code" as c88,
	   CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END as c89,
	   T4."Bezeichnung" as c90,
	   T4."Code" as c91,
	   T2."Fälligkeitsdatum" as c92,
	   T2."Client_DB" as c93,
	   T3."Geschäftsbuchungsgruppe" as c94,
	   (left(T3."Rech_ an Name",20)) as c95,
	   T2."Fahrgestellnummer" as c96,
	   T2."Kundengruppencode" as c97,
	   T2."Hauptbereich" as c98,
	   T2."Filialcode" as c99,
	   T2."Externe Belegnummer" as c100,
	   T2."Belegdatum" as c101,
	   T2."Habenbetrag" as c102,
	   T2."Sollbetrag" as c103,
	   T2."Transaktionsnr_" as c104,
	   T2."Gegenkontonr_" as c105,
	   T2."Gegenkontoart" as c106,
	   T2."Offen" as c107,
	   T2."Herkunftscode" as c108,
	   T2."Verkäufercode" as c109,
	   T2."Markencode" as c110,
	   T2."Kostenstellencode" as c111,
	   T2."Restbetrag" as c112,
	   T2."Betrag" as c113,
	   T2."Beschreibung" as c114,
	   T2."Belegnr_" as c115,
	   T2."Belegart" as c116,
	   T2."Buchungsdatum" as c117,
	   T2."Debitorennr_" as c118,
	   T2."Lfd_ Nr_" as c119,
	   T1."Name" as c120
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"."Verkaufsrechnungskopf" T3 on T3."Nr_" = T2."Belegnr_") left outer join "CARLO"."import"."Filialbezeichnung" T4 on (T2."Filialcode" = T4."Code") and (T2."Client_DB" = T4."Client_DB")) left outer join "CARLO"."import"."Verkaeufer_Einkaeufer" T5 on (T2."Verkäufercode" = T5."Code") and (T2."Client_DB" = T5."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T6 on (T2."Kostenstellencode" = T6."Code") and (T2."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Debitorgruppe" T7 on (T2."Kundengruppencode" = T7."Code") and (T2."Client_DB" = T7."Client_DB"))
where (T2."Offen" = 1)
-- order by c65 asc,c120 asc,c119 asc,c118 asc,c117 asc,c116 asc,c115 asc,c114 asc,c113 asc,c112 asc,c111 asc,c110 asc,c109 asc,c72 asc,c108 asc,c107 asc,c92 asc,c106 asc,c105 asc,c104 asc,c103 asc,c102 asc,c101 asc,c100 asc,c99 asc,c98 asc,c97 asc,c96 asc,c91 asc,c90 asc,c88 asc,c87 asc,c83 asc,c82 asc,c74 asc,c73 asc,c85 asc,c81 asc,c80 asc,c79 asc,c78 asc,c77 asc,c76 asc,c75 asc,c71 asc,c86 asc,c93 asc,c89 asc,c84 asc,c95 asc,c94 asc
) D2
) D1