Query gesucht

[Kai]

Hallo,

irgendwie läufts grad nicht recht, daher muß ich Eure Hilfe in ANSPRUCH NEHMEN:

Ich habe 2 Tabellen.In der ersten nehme ich Rechnungen auf, die ich erhalte. In der 2. Tabelle nehme ich von mir geleistete Zahlungen auf. Bide sind über die ID miteinander verbunden.

Tabelle Rechnungen
ID(int,6)| Lieferant(int,6)| Datum(date)| Betrag(dez,8-2)| mwst(dez,3-2)| Tage_bis_faellig(int,3)| bezahlt(int,1)

Tabelle Zahlungen
ID(int,6)| Nettozahlung(dez,8-2)| Bruttozahlung(dez,8-2)| USt(dez,3-2)| Skonto(dez,3-2)

Jetzt würde ich gerne eine Liste aller noch offenen Rechnungsbeträge bis zu einem Stichtag X als Liste erhalten.

Kann ich das in einer einzigen Abfrage leisten? Danach suche ich jetzt schon seit einigen Stunden, aber inzwischen bin ich nicht mehr sicher, ob man das überhaupt kann?

Kai

P.S: Das ich in Tabelle 2 weder Bruttozahlung noch USt. bräuchte, ist mir klar. Die beiden Spalten habe ich nur zu Entwicklungszwecken in der DB.

[dedlfix]

Tach!

Tabelle Rechnungen
ID(int,6)| Lieferant(int,6)| Datum(date)| Betrag(dez,8-2)| mwst(dez,3-2)| Tage_bis_faellig(int,3)| bezahlt(int,1)

Tabelle Zahlungen
ID(int,6)| Nettozahlung(dez,8-2)| Bruttozahlung(dez,8-2)| USt(dez,3-2)| Skonto(dez,3-2)

Jetzt würde ich gerne eine Liste aller noch offenen Rechnungsbeträge bis zu einem Stichtag X als Liste erhalten.

Definiere "offener Rechnungsbetrag". Im einfachsten Fall brauchst du ja nur das bezahlt-Flag auszuwerten und ein wenig Datumsarithmetik zu betreiben. Aber dann würdest du ja die zweite Tabelle nicht mit in die Aufgabenstellung bringen ...

Ein paar Beispieldaten und das erwartete Ergebnis wären nett.

dedlfix.

[Kai]

Tach!

Tabelle Rechnungen
ID(int,6)| Lieferant(int,6)| Datum(date)| Betrag(dez,8-2)| mwst(dez,3-2)| Tage_bis_faellig(int,3)| bezahlt(int,1)

Tabelle Zahlungen
ID(int,6)| Nettozahlung(dez,8-2)| Bruttozahlung(dez,8-2)| USt(dez,3-2)| Skonto(dez,3-2)

Jetzt würde ich gerne eine Liste aller noch offenen Rechnungsbeträge bis zu einem Stichtag X als Liste erhalten.

Hi dedlfix,

Definiere "offener Rechnungsbetrag". Im einfachsten Fall brauchst du ja nur das bezahlt-Flag auszuwerten und ein wenig Datumsarithmetik zu betreiben. Aber dann würdest du ja die zweite Tabelle nicht mit in die Aufgabenstellung bringen ...

So wars eigentlich gedacht.
Andererseits hast Du natürlich recht.
Nur, wenn ich das wirklich auch buchhalterisch korrekt machen will, müßte ich ja auch noch eventuelle Gutschriften berücksichtigen. Ok, das könnte ich über ein Flag in Tabelle 1 noch machen.

Ein paar Beispieldaten und das erwartete Ergebnis wären nett.

Das erwartete Ergebnis ist sowas in der Art:

Konto: 70003 Lieferant xy GmbH
Datum       Fällig         ID     OP-Restbetrag      Buchungstext
15.10.2012  25.10.2012     14       45,22            RG-457889,Blabla
Summe 235,10
--------------------------------------------------------------------------
Konto: 70009 Lieferant yz KG
Datum       Fällig         ID     OP-Restbetrag      Buchungstext
20.10.2012  26.10.2012     20       45,22            RG-546289,Blabla
Summe 1.250,60
--------------------------------------------------------------------------
...
...
Gesamtsumme 15.120,20

Beispieldaten:

  
CREATE TABLE rechnungen (  
  RGID int(6) NOT NULL AUTO_INCREMENT,  
  Lieferant int(6) NOT NULL,  
  RGnummer varchar(50) NOT NULL,  
  Datum date NOT NULL,  
  Betrag decimal(10,2) NOT NULL,  
  Konto varchar(30) NOT NULL,  
  mwst decimal(6,2) NOT NULL DEFAULT '0.00',  
  Tage_bis_faellig int(3) NOT NULL,  
  bezahlt tinyint(1) NOT NULL DEFAULT '0',  
  PRIMARY KEY (RGID)  
) ENGINE=MyISAM;  
  
INSERT INTO rechnungen VALUES(1, 7, 'RG-123436', '2012-12-17', 500.00, 'wareneinkauf', 19.00, 10, 0);  
INSERT INTO rechnungen VALUES(2, 254, 'RG-256897', '2012-12-14', 1000.00, 'wareneinkauf', 19.00, 0, 0);  
INSERT INTO rechnungen VALUES(3, 16, 'RG-99999999', '2012-12-17', 60.00, 'tanken', 19.00, 0, 1);  
INSERT INTO rechnungen VALUES(4, 17, 'RG666666', '2012-12-17', 2000.00, 'wareneinkauf_it', 19.00, 0, 0);  
  
CREATE TABLE zahlungen (  
  ZID int(6) NOT NULL AUTO_INCREMENT,  
  RGID int(6) NOT NULL,  
  Nettog decimal(12,2) NOT NULL,  
  Bruttog decimal(12,2) NOT NULL,  
  USt decimal(4,2) NOT NULL,  
  Skonto decimal(6,2) NOT NULL,  
  Bezahlart varchar(30) NOT NULL,  
  Buchungstext varchar(255) NOT NULL,  
  PRIMARY KEY (ZID),  
  KEY RechnungenID (RGID)  
) ENGINE=MyISAM;  
  
  
INSERT INTO zahlungen VALUES(1, 1, 420.17, 500.00, 19.00, 0.00, 'Bar', '');  
INSERT INTO zahlungen VALUES(2, 4, 504.20, 600.00, 19.00, 3.00, 'Bank', 'Buchungstext1');  
INSERT INTO zahlungen VALUES(3, 4, 84.03, 100.00, 19.00, 3.00, 'Bank', 'Buchungstext2');  
INSERT INTO zahlungen VALUES(4, 2, 210.08, 250.00, 19.00, 0.00, 'Bank', 'Buchungstext3');  
  

Hierbei sind jetzt die Gutschriften (das Flag in tabelle 1) noch nicht berücksichtigt.

Viele Grüße, Kai

[dedlfix]

Tach!

Ein paar Beispieldaten und das erwartete Ergebnis wären nett.

Die sollten auch zueinander passen, damit man weiß, ob die Rechnung stimmt.

dedlfix.

[Kai]

Ein paar Beispieldaten und das erwartete Ergebnis wären nett.

Die sollten auch zueinander passen, damit man weiß, ob die Rechnung stimmt.

Hi dedlfix.

Ok, Chef. Ich mach das. Dann arbeite ich auch gleich eine Gutschrift mit ein, rechne per Hand nach und poste den Dump und das Ergebnis. Ich will ja die Hilfe, also mache ich das gerne,wenn es hierbei hilft.

Wird aber Abend werden, ich bin grad unterwegs.

Schönen Gruß, Kai

[Kai]

Wird aber Abend werden, ich bin grad unterwegs.

Puh, gar nicht so einfach, wenn man das seriös anstellt. ich hoffe (und glaube auch), dass ich mich nicht verrechnet habe.

Code:

  
CREATE TABLE rechnungen (  
  RGID int(6) NOT NULL AUTO_INCREMENT,  
  Lieferant int(6) NOT NULL,  
  RGnummer varchar(50) NOT NULL,  
  Datum date NOT NULL,  
  Betrag decimal(10,2) NOT NULL,  
  Konto varchar(30) NOT NULL,  
  mwst decimal(6,2) NOT NULL DEFAULT '0.00',  
  Faellig_in_Tagen int(3) NOT NULL,  
  bezahlt tinyint(1) NOT NULL DEFAULT '0',  
  Status varchar(2) NOT NULL DEFAULT 'RE',  
  PRIMARY KEY (RGID)  
) ENGINE=MyISAM;  
  
  
INSERT INTO rechnungen VALUES(1, 7, 'RG-123436', '2012-12-17', 500.00, 'wareneinkauf', 19.00, 10, 0, 'RE');  
INSERT INTO rechnungen VALUES(2, 254, 'RG-256897', '2012-12-14', 1000.00, 'wareneinkauf', 19.00, 7, 0, 'RE');  
INSERT INTO rechnungen VALUES(3, 16, 'RG-99999999', '2012-12-17', 60.00, 'tanken', 19.00, 0, 1, 'RE');  
INSERT INTO rechnungen VALUES(4, 17, 'RG666666', '2012-12-17', 2000.00, 'wareneinkauf_it', 19.00, 10, 0, 'RE');  
INSERT INTO rechnungen VALUES(5, 17, 'GU-12345', '2012-12-17', 210.00, 'wareneinkauf_it', 19.00, 0, 0, 'RE');  
  
  
  
CREATE TABLE zahlungen (  
  ZID int(6) NOT NULL AUTO_INCREMENT,  
  RGID int(6) NOT NULL,  
  Netto decimal(12,2) NOT NULL,  
  Brutto decimal(12,2) NOT NULL,  
  USt decimal(4,2) NOT NULL,  
  Skonto decimal(6,2) NOT NULL,  
  Bezahlart varchar(30) NOT NULL,  
  Buchungstext varchar(255) NOT NULL,  
  PRIMARY KEY (ZID),  
  KEY RechnungenID (RGID)  
) ENGINE=MyISAM;  
  
  
INSERT INTO zahlungen VALUES(1, 1, 420.17, 500.00, 19.00, 0.00, 'Bank', '');  
INSERT INTO zahlungen VALUES(2, 4, 504.20, 600.00, 19.00, 3.00, 'Bank', 'Buchungstext1');  
INSERT INTO zahlungen VALUES(3, 4, 84.03, 100.00, 19.00, 3.00, 'Bank', 'Buchungstext2');  
INSERT INTO zahlungen VALUES(4, 2, 210.08, 250.00, 19.00, 0.00, 'Bank', 'Buchungstext3');  
INSERT INTO zahlungen VALUES(6, 5, 176.47, 210.00, 19.00, 0.00, 'Bank', '');  
  
  

Gewünschtes Ergebniss:

Konto: 254
Datum       Fällig         ID     Betrag      Buchungstext
14.12.2012  21.12.2012      2     1000        RG-256897
15.12.2012                        -250        Buchungstext 3

OP-Restbetrag 750 inkl. 19% MwSt.
--------------------------------------------------------------------------
Konto: 17
Datum       Fällig         ID     Betrag      Buchungstext
17.12.2012  27.12.2012      4     2000        RG666666
17.12.2012                        -600        Buchungstext 1
17.12.2012                        -100        Buchungstext 2

OP-Restbetrag 1240 inkl. 19% MwSt. (3% Skonto)
--------------------------------------------------------------------------
Konto: 17
Datum       Fällig         ID     Betrag      Buchungstext
17.12.2012  17.12.2012      4         GU      GU-12345
17.12.2012                          -250

OP-Restbetrag -250 inkl. 19% MwSt.
--------------------------------------------------------------------------

Gesamtsumme 1740 Euro inkl. 19% MwSt.

Nun bin ich mal gespannt, ob das irgendeiner schafft. Ich habe es nicht geschafft, jedenfalls nicht in einer oder 2 Abfragen. Zu Fuß über 1000 Zwischenschritte würde ich aber, glaub ich, schaffen.

Bitte, jetzt seid Ihr gefragt.

Viele Grüße, Kai

[Frank (no reg)]

Hallo,

Dieses

Gewünschte Ergebnis:

Konto: 254
Datum       Fällig         ID     Betrag      Buchungstext
14.12.2012  21.12.2012      2     1000        RG-256897
15.12.2012                        -250        Buchungstext 3

OP-Restbetrag 750 inkl. 19% MwSt.

entspricht aber nicht unbedingt einer regulaeren relationalen Datenstruktur. Und damit ist es quasi unmoeglich dieses Ergebnis als Ergebnis einer einzigen Abfrage zu produzieren.

Oder meinst du

OP-Restbetrag 750 inkl. 19% MwSt.

als Ergebnis?

Das waere dann ja noch recht gut moeglich. Zusammen mit RGID und Konto.

Am Beispiel von RGID = 4 (das mittlere Beispiel).

SELECT
   r.RGID
 , r.RGNummer
 , r.DATUM
 , DATEADD(dd, r.Faellig_in_Tagen, r.DATUM) AS Faelligkeit_Datum
 , r.BETRAG AS Betrag_Faellig
 , SUM(z.Brutto) AS Brutto_Gezahlt
 , r.BETRAG - SUM(z.Brutto) AS Noch_Offen
FROM rechnungen r
INNER JOIN zahlungen z ON r.RGID = z.RGID
WHERE r.RGID = 4
GROUP BY
   r.RGID
 , r.RGNummer
 , r.DATUM
 , DATEADD(dd, r.Faellig_in_Tagen, r.DATUM) AS Faelligkeit_Datum
 , r.BETRAG;

Was kommt da denn raus?

Deine Zahlungstabelle laesst ein Datum vermissen. Wie kommst du sonst auf den 15.12. im ersten Beispiel? Das "bezahlt" Flag is auch ueberfluessig, da du dieses herausbekommst wenn der "Noch_Offen" Betrag <= 0 ist.

Deine Skonto-Behandlung ist falsch. Grundsaetzlich wird Skonto (wenn es ueberhaupt gewaehrt wird) auf eine bestimmte Faelligkeit gewaehrt und auf den Rechnungsbetrag. Ob die Zahlungen dann mit oder ohne Skonto gemacht wurden is schnurz. Es bleibt ggf ein offener Posten wenn nur ein Teil bezahlt wurde oder (in Hoehe des Skonto) wenn die Skonto Frist ueberschritten wurde. Dazu brauchst du dann aber auch ein Datum bei zahlungen um zu sehen ob die Summe der Zahlungen innerhalb der Skontofrist auch der Rechnungssumme abzueglich Skonto entspricht.

Darueberhinaus (was auch schon festgestellt wurde) ist die Behandlung des Faelligkeitsdatums suboptimal.

Bitte, jetzt seid Ihr gefragt.

Aha .. und fuer dich heisst es jetzt: Bitte zur Kasse und zahlen. :-)

Ciao, Frank

[Kai]

Hi Frank,

entspricht aber nicht unbedingt einer regulaeren relationalen Datenstruktur. Und damit ist es quasi unmoeglich dieses Ergebnis als Ergebnis einer einzigen Abfrage zu produzieren.

Klar, das das nicht geht.
Dedlfix fragte aber nach dem gewünschten Ergebnis. Es würde mir ja reichen, den Weg dorthin zu kennen. Auch wenn es mehr als 1 Abfrage ist.

Oder meinst du

OP-Restbetrag 750 inkl. 19% MwSt.
als Ergebnis?

Das waere dann ja noch recht gut moeglich. Zusammen mit RGID und Konto.

Nein, das alleine meinte ich aber nicht.

Am Beispiel von RGID = 4 (das mittlere Beispiel).

SELECT
   r.RGID
, r.RGNummer
, r.DATUM
, DATEADD(dd, r.Faellig_in_Tagen, r.DATUM) AS Faelligkeit_Datum
, r.BETRAG AS Betrag_Faellig
, SUM(z.Brutto) AS Brutto_Gezahlt
, r.BETRAG - SUM(z.Brutto) AS Noch_Offen
FROM rechnungen r
INNER JOIN zahlungen z ON r.RGID = z.RGID
WHERE r.RGID = 4
GROUP BY
   r.RGID
, r.RGNummer
, r.DATUM
, DATEADD(dd, r.Faellig_in_Tagen, r.DATUM) AS Faelligkeit_Datum
, r.BETRAG;

Was kommt da denn raus?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Faelligkeit_Datum , r.BETRAG LIMIT 0, 30' at line 16

Deine Zahlungstabelle laesst ein Datum vermissen. Wie kommst du sonst auf den 15.12. im ersten Beispiel?

Stimmt. Das Datum habe ich im Original drin.

Das "bezahlt" Flag is auch ueberfluessig, da du dieses herausbekommst wenn der "Noch_Offen" Betrag <= 0 ist.

Das ist schon klar, das ist aber historisch gesehen noch drin.

Deine Skonto-Behandlung ist falsch. Grundsaetzlich wird Skonto (wenn es ueberhaupt gewaehrt wird) auf eine bestimmte Faelligkeit gewaehrt und auf den Rechnungsbetrag. Ob die Zahlungen dann mit oder ohne Skonto gemacht wurden is schnurz.

Ja. Aber irgendwo muß ich doch vermerken, dass ich Skonto in Anspruch nehmen will. Sonst kann  ich es nicht in  die Rechnung einbeziehen, die prüft, ob ein offener Betrag für den Beleg existiert oder  nicht.

Es bleibt ggf ein offener Posten wenn nur ein Teil bezahlt wurde oder (in Hoehe des Skonto) wenn die Skonto Frist ueberschritten wurde. Dazu brauchst du dann aber auch ein Datum bei zahlungen um zu sehen ob die Summe der Zahlungen innerhalb der Skontofrist auch der Rechnungssumme abzueglich Skonto entspricht.

Prinzipiell schon. Aber das belasse ich beim User, das korrekt anzulegen.

Bitte, jetzt seid Ihr gefragt.
Aha .. und fuer dich heisst es jetzt: Bitte zur Kasse und zahlen. :-)

Ja ok. Wenns denn hilft... weobei, es ist schon klar, dass "Bitte, jetzt seid Ihr gefragt." mehr Frage als Aufforderung war? :-)

Gruß, Kai

Ciao, Frank

[Frank (no reg)]

Klar, das das nicht geht.
Dedlfix fragte aber nach dem gewünschten Ergebnis. Es würde mir ja reichen, den Weg dorthin zu kennen. Auch wenn es mehr als 1 Abfrage ist.

Ich nehme an, dass er eher wissen wollte wie das gewünschte Abfrageergebnis (also Spalten und Zeilen) sehen wollte als das Ausgabeergebnis wie es dann irgendwann mal wo auf der Webseite oder wo auch immer stehen sollte.

Also, wenn du schon mal verstanden hast, dass es nicht in einer einzigen Abfrage gehen kann, wie sehen deine gewünschten Abfrageergebnisse aus? (Welche einzelnen Fragen in Prosa möchtest du an die Datenbank stellen?)

Oder meinst du

OP-Restbetrag 750 inkl. 19% MwSt.
als Ergebnis?

Nein, das alleine meinte ich aber nicht.

Was genau dann (noch)?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Faelligkeit_Datum , r.BETRAG LIMIT 0, 30' at line 16

Ja, genau den hab ich drin gelassen, damit du auch etwas Eigeninitiative zeigen kannst und nicht nur die Lösung vom Silbertablett nimmst.

Deine Zahlungstabelle laesst ein Datum vermissen. Wie kommst du sonst auf den 15.12. im ersten Beispiel?
Stimmt. Das Datum habe ich im Original drin.

Ist nicht ganz so ideal verfälschte Ausgangsbedingungen zu liefern um korrekte Antworten zu bekommen.

Das "bezahlt" Flag is auch ueberfluessig, da du dieses herausbekommst wenn der "Noch_Offen" Betrag <= 0 ist.
Das ist schon klar, das ist aber historisch gesehen noch drin.

Und das bedeutet genau was?

Deine Skonto-Behandlung ist falsch. Grundsaetzlich wird Skonto (wenn es ueberhaupt gewährt wird) auf eine bestimmte Faelligkeit gewaehrt und auf den Rechnungsbetrag. Ob die Zahlungen dann mit oder ohne Skonto gemacht wurden is schnurz.

Ja. Aber irgendwo muß ich doch vermerken, dass ich Skonto in Anspruch nehmen will. Sonst kann  ich es nicht in  die Rechnung einbeziehen, die prüft, ob ein offener Betrag für den Beleg existiert oder  nicht.

Du hast das Prinzip nicht verstanden.

Es bleibt ggf ein offener Posten wenn nur ein Teil bezahlt wurde oder (in Hoehe des Skonto) wenn die Skonto Frist ueberschritten wurde. Dazu brauchst du dann aber auch ein Datum bei zahlungen um zu sehen ob die Summe der Zahlungen innerhalb der Skontofrist auch der Rechnungssumme abzueglich Skonto entspricht.

Prinzipiell schon. Aber das belasse ich beim User, das korrekt anzulegen.

Fehler, "All user input is evil"! Wie kann der Benutzer etwas korrekt anlegen wenn die Grundlagen dafür falsch implementiert sind?

Gut Nacht, Frank

[Kai]

Moin,

Also, wenn du schon mal verstanden hast, dass es nicht in einer einzigen Abfrage gehen kann, wie sehen deine gewünschten Abfrageergebnisse aus? (Welche einzelnen Fragen in Prosa möchtest du an die Datenbank stellen?)

Ok, machen wir es so.

1. Suche mir alle Belege (egal ob RG oder GU), deren Belegdatum + Tage _bis_zur_Fälligkeit kleiner/gleich einem Stichtag X (Usereingabe) ist und deren Saldo aus RG-Summe und Summe aus Zahlungen*(1+Skonto/100) > Null (0) ist und gruppiere mir das ganze nach dem Lieferanten.

Dann würde ich quasi schon fast das erhalten, was ich brauche. Ab dort käme ich dann auch alleine weiter.

Oder meinst du

OP-Restbetrag 750 inkl. 19% MwSt.
als Ergebnis?

Nein, das alleine meinte ich aber nicht.

Was genau dann (noch)?

Ich überlege gerade, ob das nicht doch schon fast das ist, was ich weiter oben in prosa schrieb.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Faelligkeit_Datum , r.BETRAG LIMIT 0, 30' at line 16

Ja, genau den hab ich drin gelassen, damit du auch etwas Eigeninitiative zeigen kannst und nicht nur die Lösung vom Silbertablett nimmst.

Du hast den ;-) - Smilie vergessen ;-)

Es bleibt ggf ein offener Posten wenn nur ein Teil bezahlt wurde oder (in Hoehe des Skonto) wenn die Skonto Frist ueberschritten wurde. Dazu brauchst du dann aber auch ein Datum bei zahlungen um zu sehen ob die Summe der Zahlungen innerhalb der Skontofrist auch der Rechnungssumme abzueglich Skonto entspricht.

Ich weiß schon ganz gut, was Du meinst.
Aber dann würde ich eher die Skonto-Spalte ganz heraus nehmen und es dem User überlassen, die Rechnung trotz des OP als bezahlt zu markieren.

Prinzipiell schon. Aber das belasse ich beim User, das korrekt anzulegen.

Fehler, "All user input is evil"! Wie kann der Benutzer etwas korrekt anlegen wenn die Grundlagen dafür falsch implementiert sind?

Das sehe ich nicht ganz so, weil der User ja ich selber bin ;-)

Zudem, würde ich das so implementieren, wie Du das vorschlägst, müsste ich ja auch sämtliche, vom Lieferanten vorgegebenen Skotogewährungsfristen eintragen. So "overdressed" wollte ich das gar nicht haben.

Kai

[Kai]

Ok, machen wir es so.

1. Suche mir alle Belege (egal ob RG oder GU), deren Belegdatum + Tage _bis_zur_Fälligkeit kleiner/gleich einem Stichtag X (Usereingabe) ist und deren Saldo aus RG-Summe und Summe aus Zahlungen*(1+Skonto/100) > Null (0) ist und gruppiere mir das ganze nach dem Lieferanten.

  
SELECT RGID  
FROM rechnungen  
WHERE ADDDATE( DATUM, Faellig_in_Tagen ) < '2012-12-31' (Usereingabe)  
  
  
  
SELECT  
r.RGID,  
r.RGNummer,  
r.DATUM,  
ADDDATE( r.DATUM,r.Faellig_in_Tagen) AS Faelligkeit_Datum,  
r.BETRAG AS Betrag_Faellig,  
SUM(z.Brutto) AS Brutto_Gezahlt,  
r.BETRAG - SUM(z.Brutto) AS Noch_Offen  
FROM rechnungen r  
INNER JOIN zahlungen z ON r.RGID = z.RGID  
WHERE r.RGID = 4 (Schleife aus Query 1)  
GROUP BY  
r.RGID,  
r.RGNummer,  
r.DATUM,  
Faelligkeit_Datum,  
r.BETRAG  
  

Hast Du ne bessere Idee?

So, vielleicht noch leicht korrigiert (Skonto) käme ich an mein Ziel.

Kai

[Kai]

So, vielleicht noch leicht korrigiert (Skonto) käme ich an mein Ziel.

So richtig glücklich bin  ich nicht.
Irgendwie klappt die Gruppierung nach Lieferant nicht wirklich und dasselbe in einer Abfrage wäre einfach viel schöner...

[Kai]

So richtig glücklich bin  ich nicht.
Irgendwie klappt die Gruppierung nach Lieferant nicht wirklich und dasselbe in einer Abfrage wäre einfach viel schöner...

Was auch schon fast funktioniert, aber nur fast...

  
  
SELECT  
r.RGID,  
r.RGNummer,  
r.DATUM,  
ADDDATE( r.DATUM,r.Faellig_in_Tagen) AS Faelligkeit_Datum,  
r.BETRAG AS Betrag_Faellig,  
SUM(z.Brutto) AS Brutto_Gezahlt,  
r.BETRAG - SUM(z.Brutto) AS Noch_Offen  
FROM rechnungen r  
INNER JOIN zahlungen z ON r.RGID = z.RGID  
WHERE ADDDATE( DATUM, Faellig_in_Tagen ) < '2012-12-31' (Usereingabe)  
GROUP BY  
r.Lieferant,  
r.RGNummer  

Ich könnte ja nun auch aus der z-Tabelle alles mögliche abfragen, was auch solange funktioniert, solange nur ein einiger Eintrag aus der z-Tagelle zur r-Tabelle passt.

Was aber mache ich, wenn mehr als 1 Eintrag aus der z-Tabelle sind auf den entsprechenden r-Tabelleneintrag bezieht.

Also konkret: Was, wenn 3 zahlungen zu einer Rechnung existieren?

Wie kriege ich das in die Ursprungsquery? ich kennen nur den Weg, es über eine Schleife der Ergebnissmenge (halt in php) zu machen.

Gruß, Kai (liest hier überhaupt noch einer mit?)

[Frank (no reg)]

Gruß, Kai (liest hier überhaupt noch einer mit?)

Ja, ich lese noch mit. Ich hab aber 6 Std. Zeitunterschied und damit normale Arbeitszeit fuer mich.

1. Suche mir alle Belege (egal ob RG oder GU), deren Belegdatum + Tage _bis_zur_Fälligkeit kleiner/gleich einem Stichtag X (Usereingabe) ist und deren Saldo aus RG-Summe und Summe aus Zahlungen*(1+Skonto/100) > Null (0) ist und gruppiere mir das ganze nach dem Lieferanten.

Ist doch schon mal ein Anfang.

Teil 1: Gib mir alle Belege bis zum Stichtag

SELECT
  rg.rgid, rg.lieferant, rg.datum, ADDDATE(rg.Datum, rg.Faellig_Tage) AS faellig_datum
FROM rechnungen rg
WHERE ADDDATE(rg.Datum, rg.Faellig_Tage) <= 'stichtag_datum_user_input'

Teil 2:Summiere alle Zahlungen pro RGID die bis zum aktuellen Tag registriert sind

SELECT z.RGID, SUM(z.BRUTTO) AS BETRAG
FROM zahlungen z
WHERE z.DATUM<= 'stichtag' GROUP BY z.RGID

Teil 3 fuege beide Abfragen mithilfe eines Joins zusammen:

SELECT
  rg.rgid
, rg.lieferant
, rg.datum
, ADDDATE(rg.Datum, rg.Faellig_Tage) AS faellig_datum
, rg.Betrag - (0.03 * rg.Betrag) AS BETRAG_NACH_SKONTO
, rg.Betrag AS BETRAG
, (rg.Betrag - (0.03 * rg.Betrag)) - ISNULL(z2.BETRAG, 0.0) REST_BETRAG
FROM rechnungen rg
LEFT JOIN (SELECT z.RGID, SUM(z.BRUTTO) AS BETRAG FROM zahlungen z WHERE z.DATUM<= 'stichtag' GROUP BY z.RGID) z2 ON z2.RGID = rg.RGID
WHERE ADDDATE(rg.Datum, rg.Faellig_Tage) <= 'stichtag_datum_user_input'

Also konkret: Was, wenn 3 zahlungen zu einer Rechnung existieren?

Na dazu ist dich die SUM Aggregatsfunktion und das GROUP BY da?!!

Zur korrekten Behandlung von Skonto musst du lediglich pruefen ob alle Zahlungen innerhalb der Skonto Frist gemacht worden sind. Das kannst du recht einfach mit einem zweiten LEFT JOIN ueber das "Faelligkeitsdatum" hinbekommen.

Die Gruppierung der Werte pro Lieferant is auch sehr einfach ... SUM und GROUP BY. Alles was du aus obiger Abfrage eliminieren musst sind RGID und "DATUM". Fuer DATUM kommt dann evt noch ein MIN oder MAX in Frage.

Ich haette eher noch erwartet, dass dir der INNER JOIN ein Problem bereitet wenn es keine einzige Zahlung gibt ... aber diese ganze Abfrage ist wirklich was fuer Datenbank-Grundschule.

Ciao, Frank

[Kai]

Hi Frank,

Ja, ich lese noch mit. Ich hab aber 6 Std. Zeitunterschied und damit normale Arbeitszeit fuer mich.

Ok. Das erklärt auch Deine nächtlichen Antworten ;-)

Inzwischen bin ich alleine auch schon etwas weiter gekommen.
Trotzdem helfen Deine Antworten (bis auf die kleinen eingebauten Fehler, bei denen ich nicht weiß, ob die versehentlich sind oder ob ich die albern finden soll).

Also konkret: Was, wenn 3 zahlungen zu einer Rechnung existieren?

Na dazu ist dich die SUM Aggregatsfunktion und das GROUP BY da?!!

Nein, das meinte ich aber nicht.
Es ging mir um eine Aufzählung der 3 Zahlungen.

Zur korrekten Behandlung von Skonto musst du lediglich pruefen ob alle Zahlungen innerhalb der Skonto Frist gemacht worden sind. Das kannst du recht einfach mit einem zweiten LEFT JOIN ueber das "Faelligkeitsdatum" hinbekommen.

Ok. Das ist grad mein Thema.
Ich muß in die Query eine IF-Abfrage für die Spalte "Skonto" einbauen, die bei Wert größer 0 die Skontoregel anwendet.

Ich haette eher noch erwartet, dass dir der INNER JOIN ein Problem bereitet wenn es keine einzige Zahlung gibt ... aber diese ganze Abfrage ist wirklich was fuer Datenbank-Grundschule.

Nein, LEFT JOIN war schon klar für diesen Fall.

Gruß, Kai

[Frank (no reg)]

Von einer Yacht aus zu arbeiten, macht schon Laune.
Dass da Syntaxfehler drin sind in dem SQL was ich hier poste, liegt vor allem daran, dass ich hier Microsoft SQL Server habe und nicht dieses Krauter-Datenbanksystem mySQL. Die sprechen halt andere Dialekte. Vgl. ADDDATE(date, Interval) vs. DATEADD(Interval-type, increment, date).

Eine Aufzaehlung der 3 Zahlungen ... für die Ausgabe? Dafür brauchst du sowieso eine zweite Abfrage, dieses Ergebnis hat ja eine andere Struktur als nur den Restbetrag anzuzeigen. Aber diese Abfrage ist dann wirklich auch wieder eine sehr einfache.
SELECT wasauchimmerdufürdieausgabeanfeldernbrauchst FROM Zahlungen WHERE RGID = eingabeparameter.

Wo genau liegt dein Problem mit dem IF? Ermittle den Zahlungsbetrag inkl. Skontoabzug und vergleiche ihn mit der Summe aller Zahlungen. Für jede RGID. Danach kannst du nach Lieferant/Konto gruppieren.

Vielleicht hilft es dir, die Skontoregel erstmal wieder in Prosa zu formulieren, komplett datenbankunabhängig.

Ciao, Frank

[Kai]

Hi Frank,

Eine Aufzaehlung der 3 Zahlungen ... für die Ausgabe? Dafür brauchst du sowieso eine zweite Abfrage, dieses Ergebnis hat ja eine andere Struktur als nur den Restbetrag anzuzeigen. Aber diese Abfrage ist dann wirklich auch wieder eine sehr einfache.

Ja klar. Ich wollte nur nochmal sicher gehen, dass ich eine 2. Query hierfür brauche.

Wo genau liegt dein Problem mit dem IF? Ermittle den Zahlungsbetrag inkl. Skontoabzug und vergleiche ihn mit der Summe aller Zahlungen. Für jede RGID. Danach kannst du nach Lieferant/Konto gruppieren.

Du gehst von einem gleichbleibendem Skontosatz aus. Das ist aber praxisfremd. Der ist ja meist gestaffelt. Zudem möchte ich mit der Skontospalte auch Rabatte, Zahlungsvorbehalte und was es sonst noch so gibt, erschlagen.
Demnach lasse ich den User (mich) einfach einen %-Satz eintragen.

Vielleicht hilft es dir, die Skontoregel erstmal wieder in Prosa zu formulieren, komplett datenbankunabhängig.

Ok. Schau nach, ob die Spalte "Skonto" einen Wert enthält, der größer 0 ist. Falls ja, rechne den %-Satz * Rechnungsbetrag aus und ziehe ihn vom Rechnungsbetrag ab.
Ich habe das mit einem Case-Statement versucht, das hat nicht funktioniert. ich vermute, ein IF-Statement wäre geeigneter. Oder?

Gruß, Kai

[Frank (no reg)]

Ok. Schau nach, ob die Spalte "Skonto" einen Wert enthält, der größer 0 ist. Falls ja, rechne den %-Satz * Rechnungsbetrag aus und ziehe ihn vom Rechnungsbetrag ab.

Klingt jetzt nicht so schwierig. Auch nicht wenn Skontostaffelungen in einer separaten Tabelle wären.

Ich habe das mit einem Case-Statement versucht, das hat nicht funktioniert.

"Was" hat nicht funktioniert?

ich vermute, ein IF-Statement wäre geeigneter. Oder?

Meine allwissende Glaskugel ist noch auf der Yacht, kann ich also nicht auf dein "Oder?" antworten ohne zu wissen wie dein letzter SQL-Versuch jetzt aussieht.

Wir haben ja alle mal angefangen und eine gewisse Lernkurve gehabt, aber du scheinst etwas lernresistent zu sein.

Von deinen Versuchen mit SQL ist nicht viel zu sehen ausser die CREATE TABLE und INSERT Statements.

Von daher klinke ich mich hier aus.

Ciao, Frank

[ichbinich]

Hallo,

Kann ich das in einer einzigen Abfrage leisten? Danach suche ich jetzt schon seit einigen Stunden, aber inzwischen bin ich nicht mehr sicher, ob man das überhaupt kann?

Ja, du musst in deiner Abrage nur die Differenz zwischen Betrag in der einen und Nettozahlung in der anderen Tabelle auswerten - alles >0 ist eine offene Rechnung, 0 ist ausgeglichen und alles <0 ist überzahlt ;)

vg ichbinich

[MudGuard]

Hi,

Tabelle Rechnungen
ID(int,6)| Lieferant(int,6)| Datum(date)| Betrag(dez,8-2)| mwst(dez,3-2)| Tage_bis_faellig(int,3)| bezahlt(int,1)

Tage_bis_faellig kommt mir seltsam vor.

Wieso speicherst Du nicht direkt das Fälligkeitsdatum?
Mit "Tage_bis_faellig" mußt Du täglich ein update machen, um die Spalte eins runterzählen.

Das Fälligkeitsdatum aber bleibt konstant.

Jetzt würde ich gerne eine Liste aller noch offenen Rechnungsbeträge bis zu einem Stichtag X als Liste erhalten.

Wo ist da Dein Problem?
Du hast ein bezahlt-Flag, und in derselben Tabelle den Betrag.
Und Rechnungen, deren Datum nach Deinem Stichtag liegen, kannst Du doch auch einfach aussortieren.

cu,
Andreas

[Kai]

Hi Andreas,

Tage_bis_faellig kommt mir seltsam vor.

Wieso speicherst Du nicht direkt das Fälligkeitsdatum?
Mit "Tage_bis_faellig" mußt Du täglich ein update machen, um die Spalte eins runterzählen.

Ich dachte mir, ich laß mysql rechnen anstatt php ;-)
Wenn es einen guten Grund gibt, das an php zu geben, warum nicht?

Kai