SQL-Challenge

[Felix_83]

Hi zusammen,

ich stehe derzeit vor folgender Herausforderung. Im Zuge versendeter Rechnungen, versenden wir bei Nichtzahlung Zahlungserinnerungen per E-Mail. Folgendes Statement soll Auskunft darüber geben, wie der Status Quo der jeweiligen Rechnungen ist:

/* List of invoices */
SELECT tl.id AS ptid, iv.invoice_number as ReNr,
CASE WHEN iv.invoice_type_id = '1' THEN 'Supplier' WHEN iv.invoice_type_id = '2' THEN 'Company' END as 'Typ',
LEFT(iv.created_at,10) as ReDatum, ivp.`type` AS 'Anteil',
CASE WHEN ivp.`type` = 'Office' THEN ROUND((ivp.value/100),2) END as 'Betrag Global',
CASE WHEN ivp.`type` = 'Agent' THEN ROUND((ivp.value/100),2) END as 'Betrag M',
LEFT(PaymentReminderSentAt,10) as 'Z-Erinnerung', ROUND(ta.value/100,2) as 'Zahlung', LEFT(ta.`date`,10) as 'am',
c.name AS 'Payment_Z'

FROM  invoices iv
LEFT JOIN taims tl ON (iv.taim_id = tl.id)
LEFT JOIN company c ON (tl.company_id=c.id)

LEFT JOIN invoice_parts ivp ON (ivp.invoice_id = iv.id)
LEFT JOIN invoice_part_transaction ivpt ON (ivpt.invoice_part_id = ivp.id)
LEFT JOIN transactions ta ON (ta.id = ivpt.transaction_id)
LEFT JOIN (SELECT eml.taim_id, MAX(eml.sent_at) AS PaymentReminderSentAt FROM emails eml WHERE eml.deleted_at IS NULL AND eml.template LIKE 'Zahlungserinnerung%' GROUP BY taim_id) AS em ON (em.taim_id=tl.id)


WHERE iv.deleted_at IS NULL

AND iv.value != '0'

And tl.id = '72'


ORDER BY iv.invoice_number ASC

Das Statement an sich funktioniert auch und wirft eine entsprechende Übersicht aus. In oben genannten Statement habe ich, wie ihr seht, konkret auf einen Fall gefiltert, um euch das Problem besser zu schildern, welches mich umtreibt. Hier das Ergebnis:

ptid	ReNr	Typ	ReDatum	Betrag Global	Betrag M	Z-Erinnerung	Zahlung	am	company
72	8544	Company	13.12.2018		200	11.10.2019			Sample Ldt
72	8544	Company	13.12.2018	800		11.10.2019			Sample Ldt
72	8555	Supplier	16.02.2019	300		11.10.2019	300	13.03.2019	Sample Ldt

Wie ihr seht, habe ich in der Spalte Zahlungserinnerung das unterste Datum rot markiert, denn genau dieses ist falsch. Hier soll gar nichts eingetragen werden, da der Zahlungseingang bereits erfolgte und hierfür keine Zahlungserinnerung versendet wurde. Die Spalte "Z-Erinnerung" (Datum der Zahlungserinnerungs-EMail) ist für alle drei Zeilen ausgefüllt, weil die Abfrage sie über die TaimID und nicht über die Rechnung verknüpft wird.
Richtig wäre hier, wenn die obersten beiden Zeilen einen Eintrag bei Z-Erinnerung hätten, die untere Zeile jedoch nicht.

Lässt sich so eine Spalte irgendwie dynamisch gestalten, ähnlich wie bei Excel, ähnlich einer Wenn-Dann Abfrage? Hierzu müssten folgende Spalten in Abhängigkeit gebracht werden:

• Wenn Datum der Zahlungserinnerung (Spalte "Z-Erinnerung") < (kleiner) des Rechnungsdatums, dann lasse die Zelle leer
• Wenn Zahlungseingang (Spalte "am") < (kleiner) der Spalte Zahlungserinnerung und Betrag der Spalte "Zahlung" = Summe der Spalte (Betrag Global + Betrag M) dann lasse die Spalte leer
• Alle anderen Szenarien = befülle die Spalte

Ich hoffe, ich konnte alles korrekt erklären und hoffe, es gibt hierzu eine Lösung?

Besten Dank für euren Input.

LG Felix

[Christian Marquardt]

In dem du deine in der Aufzählung getätigten Bedinungen in ein case when packst und mit or verkmüpfst und richtig klammerst

case
Zahlungserinnerung (Spalte "Z-Erinnerung") < (kleiner) des Rechnungsdatums or (Zahlungseingang (Spalte "am") < (kleiner) der Spalte Zahlungserinnerung and Betrag der Spalte "Zahlung" = Summe der Spalte (Betrag Global + Betrag M) ) then
'''
else
Z-Erinnerung


Was könnte die Frage mit Entwicklertools zu tun haben?
Nicht, dass es hier eine Auswahl an DB-Foren gibt...-> verschoben

[Felix_83]

Hi Christian,

das sieht gut aus, vielen Dank.

LG Felix

[Felix_83]

Hi Christian,

jetzt habe ich noch einmal Feedback von der Abteilung bekommen und erfahren, dass die Rechnungsnummer auch in der Tabelle emails enthalten ist, auf die sich die Zahlungserinnerung bezieht. Das wusste ich vorher nicht. Sprich folgende Zeile müsste entsprechend angepasst werden, sofern möglich:

    LEFT JOIN (SELECT eml.taim_id, MAX(eml.sent_at) AS PaymentReminderSentAt FROM emails eml WHERE eml.deleted_at IS NULL AND eml.template LIKE 'Zahlungserinnerung%' GROUP BY taim_id) AS em ON (em.taim_id=tl.id)

In der Tabelle emails gibt es eine Spalte namens body, in der die Rechnungsnummer enthalten ist. Lässt sich das irgendwie dynamisch integrieren? Also in etwa so:

    LEFT JOIN (SELECT eml.taim_id, MAX(eml.sent_at) AS PaymentReminderSentAt FROM emails eml WHERE eml.deleted_at IS NULL AND eml.template LIKE 'Zahlungserinnerung%' AND eml.body enthält Rechnungsnummer GROUP BY taim_id) AS em ON (em.taim_id=tl.id)

Ginge so etwas? LG Felix

[Felix_83]

Herausforderung hierbei ist, dass die Rechnungsnummer ja in der Tabelle invoices als Spalte enthalten ist, und in der Tabelle emails nur als Teil des bodys...

[Christian Marquardt]

Sicherlich
AND body like%Rechnungsnummer%

[Felix_83]

Hi Christian,

so einfach ist es leider nicht. Kurz zur Struktur. In der Tabelle emails gibt es eine Spalte, auf die gegangen werden kann, die im template den Begriff "Zahlungserinnerung" beinhaltet. Siehe obiges Statement. Eine weitere Spalte in der Tabelle emails heißt body. In dieser ist die gesendete E-Mail und hat in etwa einen solchen Aufbau:

<strong>500€</strong><br />
<span style="font-family:Calibri,sans-serif"><span style="color:#002060"><span style="font-family:Calibri,sans-serif"><span style="color:#002060"><strong>200rs186321565 | 8544 | </strong></span></span></span></span></span></span></p>

<p style="font-size:11pt;"><span style="font-family:Calibri,sans-serif"><span style="color:#002060">Mit freundlichen Gr&uuml;&szlig;en<br />

In bold siehst du die Rechnungsnummer. Ich stehe nun vor der Herausforderung, wie ich diese Information verknüpfe. In dem unteren Beispiel siehst du, dass für die ptid 2 Rechnungen gestellt wurden. Einmal ReNr = 8544 und einmal ReNr 8555.

Ist es möglich die Information aus dem Body der Zahlungserinnerung, in der die Rechnungsnummer enthalten für die die Spalte Z-Erinnerung zu nutzen? Das aktuelle Statement schreibt die Zahlungserinnerung für alle Rechnungen, obwohl es für ReNr. 8555 keine Zahlungserinnerung gab.

ptid	ReNr	Typ	ReDatum	Betrag Global	Betrag M	Z-Erinnerung	Zahlung	am	company
72	8544	Company	13.12.2018		200	11.10.2019			Sample Ldt
72	8544	Company	13.12.2018	800		11.10.2019			Sample Ldt
72	8555	Supplier	16.02.2019	300		11.10.2019	300	13.03.2019	Sample Ldt

Ist das überhaupt machbar mit dem Aufbau?

LG Felix

[Christian Marquardt]

Doch nach deiner Anforderung

AND eml.body enthält Rechnungsnummer

ist das mit like möglich. Ansonsten erkläre was "wie ich diese Information verknüpfe." oder "für die die Spalte Z-Erinnerung zu nutzen" bedeutet

[Felix_83]

Hi Christian,

hier das angepasste Statement:

/* List of invoices */
SELECT tl.id AS ptid, iv.invoice_number as ReNr,
CASE WHEN iv.invoice_type_id = '1' THEN 'Supplier' WHEN iv.invoice_type_id = '2' THEN 'Company' END as 'Typ',
LEFT(iv.created_at,10) as ReDatum, ivp.`type` AS 'Anteil',
CASE WHEN ivp.`type` = 'Office' THEN ROUND((ivp.value/100),2) END as 'Betrag Global',
CASE WHEN ivp.`type` = 'Agent' THEN ROUND((ivp.value/100),2) END as 'Betrag M',
LEFT(PaymentReminderSentAt,10) as 'Z-Erinnerung', ROUND(ta.value/100,2) as 'Zahlung', LEFT(ta.`date`,10) as 'am',
c.name AS 'Payment_Z'

FROM  invoices iv
LEFT JOIN taims tl ON (iv.taim_id = tl.id)
LEFT JOIN company c ON (tl.company_id=c.id)

LEFT JOIN invoice_parts ivp ON (ivp.invoice_id = iv.id)
LEFT JOIN invoice_part_transaction ivpt ON (ivpt.invoice_part_id = ivp.id)
LEFT JOIN transactions ta ON (ta.id = ivpt.transaction_id)
LEFT JOIN (SELECT eml.taim_id, MAX(eml.sent_at) AS PaymentReminderSentAt FROM emails eml WHERE eml.deleted_at IS NULL AND eml.template LIKE 'Zahlungserinnerung%' AND eml.body LIKE iv.invoices GROUP BY taim_id) AS em ON (em.taim_id=tl.id)


WHERE iv.deleted_at IS NULL

AND iv.value != '0'

And tl.id = '72'


ORDER BY iv.invoice_number ASC

Hierbei bekomme ich den Fehler "Unknown column 'iv.invoice_number' in 'where clause'

Es soll die Rechnungsnummer in der Spalte body der Tabelle emails mit der Rechnungsnummer der Tabelle invoices abgeglichen werden. Sofern es hier ein Match gibt, soll die Spalte Z-Erinnerung befüllt werden, wenn nicht, dann soll sie leer bleiben.

Sorry, wenn ich mich nicht verständlich ausdrücke.

LG Felix

[Christian Marquardt]

Es wird gesagt, dass eine Spalte 'iv.invoice_number' nicht bekannt ist. Die sehe ich auch nicht.
Des Weiteren braucht ein like immer ein oder zwei %.
Ist die Spalte iv.invoices numerisch sollte es so aussehen
LIKE '%'||to_char(iv.invoices)||'%'
Umwandeln in String und mit % verbinden
(Oracle)

[Felix_83]

Hi Christian,

/* List of invoices */
SELECT tl.id AS ptid, iv.invoice_number as ReNr,
CASE WHEN iv.invoice_type_id = '1' THEN 'Supplier' WHEN iv.invoice_type_id = '2' THEN 'Company' END as 'Typ',
LEFT(iv.created_at,10) as ReDatum, ivp.`type` AS 'Anteil',
CASE WHEN ivp.`type` = 'Office' THEN ROUND((ivp.value/100),2) END as 'Betrag Global',
CASE WHEN ivp.`type` = 'Agent' THEN ROUND((ivp.value/100),2) END as 'Betrag M',
LEFT(PaymentReminderSentAt,10) as 'Z-Erinnerung', ROUND(ta.value/100,2) as 'Zahlung', LEFT(ta.`date`,10) as 'am',
c.name AS 'Payment_Z'

FROM  invoices iv
LEFT JOIN taims tl ON (iv.taim_id = tl.id)
LEFT JOIN company c ON (tl.company_id=c.id)

LEFT JOIN invoice_parts ivp ON (ivp.invoice_id = iv.id)
LEFT JOIN invoice_part_transaction ivpt ON (ivpt.invoice_part_id = ivp.id)
LEFT JOIN transactions ta ON (ta.id = ivpt.transaction_id)
LEFT JOIN (SELECT eml.taim_id, MAX(eml.sent_at) AS PaymentReminderSentAt FROM emails eml WHERE eml.deleted_at IS NULL AND eml.template LIKE 'Zahlungserinnerung%' AND eml.body LIKE '%'||to_char(iv.invoice_number)||'%' GROUP BY taim_id) AS em ON (em.taim_id=tl.id)


WHERE iv.deleted_at IS NULL

AND iv.value != '0'

And tl.id = '72'


ORDER BY iv.invoice_number ASC

Kommt folgende Fehlermeldung: SQL Fehler (1370): excecute command denied to user Felix for routine...

Mmh...Weisst du was das zu bedeuten hat? Nutzen MariaDB

[Christian Marquardt]

Wahrcheinlich wird die MariaDB nicht to_char kennen. Auch das zusammfügen wird wohl nicht mit || in der MariaDB gehen

[Felix_83]

Schade, dann komme ich hier nicht weiter. Weiß leider nicht, was man jetzt noch machen kann...

[Christian Marquardt]

??
Man könnte die entsprechenden Befehle der MariaDB benutzen
Hast du Internet
https://www.google.com/search?client...number+to+char

https://www.google.com/search?safe=a...4dUDCAo&uact=5

[Felix_83]

Hi Christian,

/* List of invoices */
SELECT tl.id AS ptid, iv.invoice_number as ReNr,
CASE WHEN iv.invoice_type_id = '1' THEN 'Supplier' WHEN iv.invoice_type_id = '2' THEN 'Company' END as 'Typ',
LEFT(iv.created_at,10) as ReDatum, ivp.`type` AS 'Anteil',
CASE WHEN ivp.`type` = 'Office' THEN ROUND((ivp.value/100),2) END as 'Betrag Global',
CASE WHEN ivp.`type` = 'Agent' THEN ROUND((ivp.value/100),2) END as 'Betrag M',
LEFT(PaymentReminderSentAt,10) as 'Z-Erinnerung', ROUND(ta.value/100,2) as 'Zahlung', LEFT(ta.`date`,10) as 'am',
c.name AS 'Payment_Z'

FROM  invoices iv
LEFT JOIN taims tl ON (iv.taim_id = tl.id)
LEFT JOIN company c ON (tl.company_id=c.id)

LEFT JOIN invoice_parts ivp ON (ivp.invoice_id = iv.id)
LEFT JOIN invoice_part_transaction ivpt ON (ivpt.invoice_part_id = ivp.id)
LEFT JOIN transactions ta ON (ta.id = ivpt.transaction_id)
LEFT JOIN (SELECT eml.taim_id, MAX(eml.sent_at) AS PaymentReminderSentAt FROM emails eml WHERE eml.deleted_at IS NULL AND eml.template LIKE 'Zahlungserinnerung%' AND eml.body LIKE char(iv.invoice_number)  GROUP BY taim_id) AS em ON (em.taim_id=tl.id)


WHERE iv.deleted_at IS NULL

AND iv.value != '0'

And tl.id = '72'


ORDER BY iv.invoice_number ASC

Fehlermeldung: Unknown column 'iv.invoice_number'

[Christian Marquardt]

Wir drehen uns im Kreis.
Die Befehle heißen convert und concat und die % sind immer noch nicht da.