SQL-Challenge

Felix_83

Benutzer
Beiträge
11
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:

HTML:
/* 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 | 9555 | Supplier | 16.02.2019 | 300 | | 11.10.2019 | 300 | 13.03.2019 | Sample Ldt | |

Wie ihr seht, habe ich in der Spalte Z-Erinnnerung das unterste Datum bold 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.

In der Tabelle emails gibt es jedoch eine Spalte namens body die wie folgt aufgebaut ist, die helfen könnte:

HTML:
<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 />

Ihr seht, dass hier mittendrin die Information der Rechnungsnummer (8544) beinhaltet ist. Ich stehe gerade auf dem Schlauch, wie man diese Information noch integrieren kann in die Abfrage. Herausforderung hier ist, dass Rechnungsnummer aus dem body, die nicht als solche bezeichnet wird, sondern nur ein nummerischer Zahlencode ist, mit der Rechnnungsnummer der Spalte iv.invoice_number aus der Tabelle invoices abgeglichen werden muss...

Kann mir hier jemand helfen?

LG Felix
 
Werbung:
  • Abfrage paßt nicht zum Result (wo ist Spalte 'Anteil' hin?, wo 'Payment_Z'?)
  • warum speicherst Du Datumsangaben als Text? (LEFT(PaymentReminderSentAt,10) as 'Z-Erinnerung')
  • was hat eine versendete eMail mit dem Eingang einer Zahlung zu tun?
  • was hat die Nummer in der eMail (8544) mit dem 'falschen' Eintrag für Nummer 9555 zu tun?

Das ist alles Murks...
 
Werbung:
Hi,

  • Abfrage paßt nicht zum Result (wo ist Spalte 'Anteil' hin?, wo 'Payment_Z'?)
sorry, du hast recht. Ich hatte das zwischenzeitlich angepasst. Sorry dafür
  • warum speicherst Du Datumsangaben als Text? (LEFT(PaymentReminderSentAt,10) as 'Z-Erinnerung')
ok, geht natürlich auch mit date(PaymentReminderSentAt) as 'Z-Erinnerung'
  • was hat eine versendete eMail mit dem Eingang einer Zahlung zu tun?
gar nichts, darum geht es nicht. Die E-Mail ist eine Zahlungserinnerung einer noch nicht verbuchten Zahlung.
  • was hat die Nummer in der eMail (8544) mit dem 'falschen' Eintrag für Nummer 9555 zu tun?
Das Grundproblem ist, dass die versendete Zahlungserinnerungs-Mail bzw. das Datum der versendeten Mail für beide Rechnungen (8544 und 9555) verwendet wird. Grund hierfür ist, dass in meinem bisherigen Statement auf die taim_id gruppiert wird:

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)

Nun habe ich herausgefunden, dass in einer weiteren Spalte namens body in der Tabelle emails in der Zahlungserinnerungsmail auch die Rechnungsnummer enthalten ist. Sprich wenn man diese Information mit in das Statement integrieren könnte, dann könnte man einen direkten Link zwischen Rechnungsnummer und Zahlungserinnerung herstellen, sofern dies denn möglich ist...
 
Zurück
Oben