Entfernt Zeilen, die für einen gegebenen Schlüssel null sind

Die meisten AR / Abrechnungssysteme behandeln "Gutschriften" (den negativen Betrag) ähnlich wie Bargeld, wobei in diesem Fall die -73,19 auf die 73,19 LineBalance genauso angewandt würde, als wenn der Kunde diesen Betrag bezahlt hätte ein Guthaben von $ 0.

OPTION 1:

Gehen Sie in diesem System mit Kassenbons und Anwendungen um? Wenn dies der Fall ist, können Sie möglicherweise Daten aus diesen Geldanwendungstabellen abrufen, um die Verbindung zwischen SysInvNum 3344296 und 3341758 anzuzeigen.

OPTION 2:

Ich gehe davon aus, dass die Spalte PayAdjust verwendet wird, um den Saldo zu reduzieren, nachdem ein Kunde bezahlt hat, und dass LineBalance eine berechnete Spalte ist, die Charges + PayAdjust ist.

In den meisten Fällen ist die AR-Abteilung dafür verantwortlich, die Gutschrift auf eine offene Rechnung anzuwenden, sodass die Spalte " PayAdjust " zwischen den beiden Zeilen einen Wert von $ 0 ergibt, was zu LineBalance führt. um auch $ 0 auf jeder der 2 Reihen zu sein. Es kann nur ein Trainingsproblem für das System sein, das verwendet wird.

Dies würde dazu führen, dass die drei fraglichen Zeilen so aussehen, dass Sie kein Problem haben. Sie würden die Zeilen einfach ausschließen, indem Sie where LineBalance <> 0 zu Ihrer Abfrage hinzufügen, seit die AR-Abteilung (die das Guthaben angewendet hat) beginnt mit und so weiß die Antwort auf diese Frage) explizit angegeben, welche LineBalance der Kredit gilt für:

Option 2 Bevorzugte Datenstruktur:

OPTION 3:

Ohne diese Daten aus Option 1 oder 2 haben Sie viele Annahmen getroffen und laufen Gefahr, versehentlich die falschen Zeilen zu verbergen.

Hier ist eine Abfrage, die versucht, das zu tun, was Sie fragen, aber ich würde sehr empfehlen, mit der AR-Abteilung zu überprüfen, ob sie "PayAdjust" für diese Datensätze aktualisieren können.

Ich habe mehrere Testfälle von Szenarien hinzugefügt, die Probleme verursachen könnten, aber dies deckt möglicherweise nicht alle Grundlagen ab.

Diese Abfrage blendet nur Zeilen aus, in denen ein bestimmter passender negativer Wert für einen positiven Wert gefunden wird, für denselben REF1 und denselben Fälligkeitsdatum. Außerdem wird sichergestellt, dass die ursprüngliche Rechnungsrechnungs-ID vor der Gutschrift liegt, da angenommen werden kann, dass vor der tatsächlichen Belastung keine Gutschrift erfolgt (Testfall 6 zeigt beide Zeilen, da die Gutschrift vor der Belastung eine SysInvNum aufweist) ). Wenn mehr als eine Übereinstimmung pro REF1, DueDate, and LineBalance gefunden wird, werden die entsprechenden Gebühren- und Kreditlinien (Testfälle 2 und 4) nicht ausgeblendet. Testfall 3 summiert sich insgesamt auf 0, zeigt aber immer noch alle 3 Zeilen an, da die LineBalance-Werte nicht exakt übereinstimmen. Dies sind alles Annahmen, die ich getroffen habe, um Randfälle zu behandeln, so dass sie nach Bedarf angepasst werden können.

Ich würde ein Feld hinzufügen, das ein explizites Flag enthält, das besagt, dass eine bestimmte Ladung ein Fehler / eine Umkehrung eines Fehlers war, und dann ist es einfach, solche Zeilen herauszufiltern. Wenn Sie es im laufenden Betrieb tun, könnten Ihre Berichte eher langsam werden.

Aber um das gegebene Problem zu lösen, können wir es so machen. Die Lösung geht davon aus, dass SysInvNum eindeutig ist.

Erstellen Sie eine Tabelle mit Beispieldaten

Ich habe einige weitere Fälle hinzugefügt, die mehrere Fehler enthalten.

Zeilen nummerieren und zählen

Dies ist die Ergebnismenge:

Sie können sehen, dass Zeilen mit Fehlern zählen & gt; 1. Auch Fehlerpaare haben die gleiche Zeilennummer. Also müssen wir die Zeilen mit count & gt; 1 und diejenigen, die zwei gleiche Zeilennummern haben.

Ermitteln Sie die zu entfernenden Zeilen

Dies ist ein weiteres Zwischenergebnis:

Nun, wir haben das alles zusammengefügt.

Letzte Abfrage

Ergebnis:

Beachten Sie, dass das Endergebnis keine Zeilen mit REF = 300 enthält, da zwei korrigierte Fehler aufgetreten sind, die sich vollständig ausgeglichen haben.

Es ist klobig, aber damit können Sie die passenden negativen Straftäter identifizieren. Sie müssen das sysInvNum in beiden Spalten von Ihrem Resultset ausschließen

Diese Lösung funktioniert nur für SQL Server 2012 , entschied sich jedoch, sie hier zu belassen, wenn jemand in der Zukunft etwas Ähnliches tun muss, weil es ziemlich einfach ist.

....

Dies sollte nur zwei Transaktionen für bestimmte Empfänger entfernen, wenn ihre Summe 0 ist und es keine anderen Transaktionen zwischen ihnen gibt (und die negative Transaktion erfolgt nach positiver Transaktion). Es ist konservativer und sicherer.

Es sollte Informationen über die Reihenfolge der Transaktionen geben. So etwas wie Datumsspalte. Dann sollten Sie anstelle der PRIMARY KEY-Spalte in der PARTITION BY-Anweisung nach dieser Spalte sortieren.