aktuelles Mwst.-Problem [SQL]

[Hansa]

Hi,

die Mwst. wird wohl erhöht (egal von wem) und das ergibt bei mir ein Problem. Und zwar speichere ich die Umsätze aus verschiedenen Gründen netto. Die zugehörige Mwst. wird im Atikel hinterlegt (0,7,16% aktuell). Die Netto-Umsätze werden hierbei in einer separaten Tabelle gespeichert (netto). Nun muß ich eine Auswertung über mehrere Monate erstellen. Das bedeutet

• für jeden Artikel seine Mwst. aufaddieren.
• den Änderungszeitraum der Mwst. berücksichtigen.

Hierbei ist das Problem aufgetaucht, daß der Zeitraum mitten im Jahr liegen kann. 1998 wurde so was ähnliches zum 1.4. durchgeführt. Dies bedeutet, daß selbst bei Einschränkung auf ein Kalenderjahr das hier nicht möglich ist :

SQL-Code:

SELECT A.ID, A.NR, A.BEZ,
A.MWSTSATZ,');
SUM(K.UMSATZ) AS MONATSUMSATZ,
FROM ART A RIGHT JOIN KUARTSTAT K ON (A.ID = K.ID_ART)');
WHERE (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT) AND (JAHR = :JAHR)
GROUP BY A.ID, A.Nr, A.BEZ, A.MWSTSATZ...

Das ist jetzt stark verkürzt. Wie kann ich nun da in Abhängigkeit vom Artikel und dem Zeitraum die korrekte (sich ändernde) Mwst. ermitteln ? Programmseitig könnte ich Monat für Monat genau ausrechnen, aber das müßte doch auch die DB packen ?

P.S.: das Ergebnis soll der Brutto-Umsatz sein ! Und zwar unabhängig von der gerade in dem Monat gültigen Mwst.

[dizzy]

Was mir auf die Schnelle einfiele:
- Eine separate Tabelle die die Mehrwertssteuersätze auflistet incl. ihrem Gültigkeitszeitraum (z.B. 2 Date-Werte)
- Programmseitig für jeden Umsatz prüfen in welchen Raum er gehört, und mit dem entspr. Satz verrechnet in eine temp. Tabelle scheiben
- Einträge der temp. Tabelle summieren

Das ist allerdings ab einer größeren Menge an Umsatzdaten nicht mehr allzu performant :?. Mir ist aber auch keine built-in Funktion bekannt die das leisten kann. U.U. wäre hier der Einsatz einer Stored Procedure möglich/hilfreich, damit kenne ich mich aber leider nicht aus. Es würde, wenn möglich, aber die Zahl der Zugriffe auf die DB immens senken im Vergleich zu oben genanntem.

Gruss,
Fabian

\\edit: Oder mit der o.g. Mwst.-Tabelle den SELECT aufteilen, so dass du für jeden in der Tabelle befindlichen Zeitraum einen eigenen SELECT machst, der die Daten gleich mit dem zugehörigen Satz verrechnet. Das alles dann in eine Zieltabelle, und fertig.
Ist nur im Anfang etwas mehr Arbeit, da du erst die Anzahl der existierenden Zeiträume aus der DB ermitteln, und dann mit einer Schleife die SELECTs schön variabel raushauen musst.
Das wäre aber weit besser als die erste Idee, und (zumindest für jemanden wie mich der davon keine Ahnung hat) einfacher als eine Stored Procedure ;).

[merlin17]

Schau' dir einmal die Union-Option bei SQL an.


;-) thomas

[r_kerber]

Zitat:

Zitat von Hansa

Hi,

die Mwst. wird wohl erhöht (egal von wem) und das ergibt bei mir ein Problem. Und zwar speichere ich die Umsätze aus verschiedenen Gründen netto. Die zugehörige Mwst. wird im Atikel hinterlegt (0,7,16% aktuell). Die Netto-Umsätze werden hierbei in einer separaten Tabelle gespeichert (netto).

Ist zwar jetzt eventuell zu spät, aber denke mal drüber nach, ob Du den Mehrwert-Steuersatz zusätzlich bei den Positionen sprich Umsätzen speicherst. Ich weiß, das ist eine etwas höhere Redundanz aber bei MwSt-Änderungen sehr hilfreich.

[muenster]

Hi Hansa,
da ich sehr oft mit dieser Problematik zu tun habe kann ich den Tip von r_kerber bestätigen.
Da Du aber in Deinen Tabellen auch mit Umsätzen arbeitest denke auch an Gutschriften. Diese können auch später (manchmal Jahre) mit einem alten Steuersatz abzurechnen sein. Oder - als Beispiel - Lieferung am letzten vor Änderung, Rechnungsschreibung am ersten Tag danach = der alte Satz ist anzuwenden.
Wenn Änderung dann aber richtig. Also Datenbank aufbohren und die entsprechenden Felder ergänzen.

[alex517]

Hallo,

ich würde eine MWSt-Tabelle mit den folgenden Felder

ID, Kz, Steuersatz, Gueltig_ab

erstellen.

Werte im Kz zB:
0 - keine Mwst
1 - volle Mwst
2 - halbe Mwst

Im Artikel wird nur das Kz gespeichert.
Die tatsächliche MWSt kann man dann über eine Stored Procedure
berechnen der man als Parameter das Kz und das Datum des
realisierten Umsatzes übergibt.

alex

[Hansa]

Uff, rege Beteiligung. :mrgreen: Eine Mwst.-Tabelle ist natürlich vorhanden:

SQL-Code:

CREATE TABLE MWST (
    ID               INTEGER NOT NULL,
    ABDATUM          DATE,
    MWSTSATZ         DECIMAL(15,2),
    ANGELEGT         TIMESTAMP,
    LETZTEAENDERUNG  TIMESTAMP
);

Den Mwst.-Satz gleich mitschleppen ? Gut, das ginge mit Sicherheit. Meine Überlegungen gehen allerdings mehr in die Richtung, eine SP sagen wir mal "BRUTTO" zu benutzen, um diesen Wert auszurechnen. Dann bräuchte ich den Rückgabewert aber in der Ergebnismenge. Würde das gehen ? Bzw. wie ? Ist da nicht das JOIN hinderlich ?

[jensw_2000]

Kennt Firebird UDF's ?
Ich kann dir leider nur ein kleines Beispiel in MSSQL Syntax geben. Das bekommst du ja schnell angepasst ..

SQL-Code:

CREATE FUNCTION GetBrutto (
   @Vorgangsdatum DATE = NULL, 
   @Netto NUMERIC = 0,
   @ID_STEUERSATZ INT = NULL )
RETURNS NUMERIC
BEGIN
  DECLARE @Steuersatz NUMERIC
  DECLARE @Brutto NUMERIC

  
  IF @Vorgangsdatum < 2006-01-01 
  BEGIN
    SELECT @Steuersatz = Steuersatz FROM MWST_BIS_2006 WHERE ID = @ID_STEUERSATZ
  END
  ELSE BEGIN
    SELECT @Steuersatz = Steuersatz FROM MWST_AB_2006 WHERE ID = @ID_STEUERSATZ
  END

  SELECT @BRUTTO = @Netto + (@Netto *@Steuersatz / 100)

  RETURN @BRUTTO
END

Aufruf: (korrigiert)

SQL-Code:

SELECT P.NETTO,GetBrutto(P.Rechnungsdatum, P.Netto, P.ID_Steuersatz) AS [Brutto] FROM Postitionen P

[Kompon Ente]

Zitat:

Zitat von merlin17

Schau' dir einmal die Union-Option bei SQL an.


;-) thomas

Extra von der CDU entworfen oder was? :D

[Jelly]

Genau wie Jens wollt ich irgendwas in Richtung UDF vorschlagen. Und um die Frage gleich zu beantworten,ja Firebird versteht UDFs.

[Hansa]

@Kompon Ente : und sonst gehts dir noch gut ? :mrgreen:

Die Überlegungen gehen in die Richtung, tatsächlich den Mwstsatz pro Position zu speichern. Also 0,7,16 (seit 1.4.1998) als integer. Die nächsten Mwst.-DS wären dann eben 0,7,18 (ab 1.??.2006). Nur taucht da ein Problem auf : was mache ich z.B. mit dem momentanen Monat 8/2005 ? Angenommen ein Artikel hat Mwst.Satz Nr. 2, also jetzt 16% und später 18%. In der DB steht jetzt die 2 als Mwstsatz. Wie kann ich nun auf 16% kommen ? Das Datum liegt ja dazwischen. Ich müßte dann ja in der Mwst.Tabelle nach kleinerem und/oder größerem Wert nachgucken in Abhängigkeit vom Datum. Nur wie am besten ?

[Jelly]

Ich würde (und habe auch schon) die MWSt. direkt in die Rechnungsposten mit reinschreiben. Genauso verfahre ich mit dem Postenpreis, der sich ja auch im Laufe der Zeit ändern kann. Für ältere Rechnungen gilt dies natürlich nicht, die bleiben unberührt. Somit ist hier nicht direkt von Redundanz die Rede, weil jeder Rechnungsposten für sich genommen, einzigartig ist.

[alex517]

ich hab mal ein kleines Beispiel erstellt (IBExpert sein Dank).
Es wird in der Verkaufsdatei nur das aktuelle MWSt-Kennzeichen mit geführt.
NICHT die ID des MWSt-Datensatzes. In der MWSt-Datei können mehrere
Sätze mit dem gleichen Kennzeichen aber mit jeweils verschiedenen
Gültigkeitsdaten existieren. Die SP_GET_MWST gibt dann den jeweils
zum Verkaufstag gültigen MWSt-Satz passend zum Kz zurück.

alex

SQL-Code:

SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CONNECT 'D:\TestDB\test_datenbank.fdb' USER 'SYSDBA' PASSWORD 'masterkey';

CREATE GENERATOR GEN_MWST_ID;
SET GENERATOR GEN_MWST_ID TO 5;

CREATE GENERATOR GEN_VERKAUF_ID;
SET GENERATOR GEN_VERKAUF_ID TO 0;

SET TERM ^ ;
CREATE PROCEDURE SP_GET_MWST (
    KZ SMALLINT,
    VK_DATUM DATE)
RETURNS (
    MWSTSATZ DECIMAL(15,2))
AS
BEGIN
  EXIT;
END^
SET TERM ; ^


CREATE TABLE MWST (
    ID               INTEGER NOT NULL,
    KZ               SMALLINT DEFAULT 0 NOT NULL,
    ABDATUM          DATE,
    MWSTSATZ         DECIMAL(15,2),
    ANGELEGT         TIMESTAMP,
    LETZTEAENDERUNG  TIMESTAMP
);


CREATE TABLE VERKAUF (
    ID          INTEGER NOT NULL,
    ARTIKEL_ID  INTEGER,
    BEZ         VARCHAR(50),
    MWST_KZ     SMALLINT,
    VK_NETTO    DECIMAL(15,2),
    VK_DATUM    DATE
);


INSERT INTO MWST (ID, KZ, ABDATUM, MWSTSATZ, ANGELEGT, LETZTEAENDERUNG) VALUES (1, 0, '1900-01-01', 0, NULL, NULL);
REINSERT (3, 2, '1900-01-01', 0.14, NULL, NULL);
REINSERT (2, 1, '1900-01-01', 0.07, NULL, NULL);
REINSERT (4, 2, '1998-04-01', 0.16, NULL, NULL);

COMMIT WORK;

INSERT INTO VERKAUF (ID, ARTIKEL_ID, BEZ, MWST_KZ, VK_NETTO, VK_DATUM) VALUES (1, 1, 'Teil1', 2, 40, '1996-05-25');
REINSERT (2, 333, 'Teil1', 2, 40, '2005-05-25');
REINSERT (3, 444, 'Teil2', 1, 100, '1996-05-25');
REINSERT (4, 44, 'Teil2', 1, 100, '1996-05-25');

COMMIT WORK;

ALTER TABLE MWST ADD CONSTRAINT PK_MWST PRIMARY KEY (ID);
ALTER TABLE VERKAUF ADD CONSTRAINT PK_VERKAUF PRIMARY KEY (ID);

SET TERM ^ ;
CREATE TRIGGER MWST_BI FOR MWST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_MWST_ID,1);
END
^

CREATE TRIGGER VERKAUF_BI FOR VERKAUF
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_VERKAUF_ID,1);
END
^
SET TERM ; ^

SET TERM ^ ;

ALTER PROCEDURE SP_GET_MWST (
    KZ SMALLINT,
    VK_DATUM DATE)
RETURNS (
    MWSTSATZ DECIMAL(15,2))
AS
BEGIN
  SELECT FIRST 1
    MWSTSATZ
  FROM
    MWST
  WHERE
    (KZ=:KZ) AND (ABDATUM <= :VK_DATUM)
  ORDER BY
    ABDATUM DESC
  INTO
    :MWSTSATZ;
  IF (MWSTSATZ is null) then MWSTSATZ = 0;
  SUSPEND;
END
^
SET TERM ; ^

Abfrage:

SQL-Code:

select V.*, M.MWSTSATZ, V.VK_NETTO*(1+M.MWSTSATZ) as Brutto
from
  VERKAUF V
  left JOIN SP_GET_MWST(V.MWST_KZ, V.VK_DATUM) M on (1=1)

[Hansa]

Alex, der Tip ist echt gut ! Interessant ist insbesondere das hier :

SQL-Code:

ALTER PROCEDURE SP_GET_MWST ( 
    KZ SMALLINT, 
    VK_DATUM DATE) 
RETURNS ( 
    MWSTSATZ DECIMAL(15,2)) 
AS 
BEGIN 
  SELECT FIRST 1 
    MWSTSATZ 
  FROM

Könnte fast von mir sein. :lol: Erkläre mir mal bitte noch einer was da genau gemacht wird, bzw. das "First" und die "1". Habe das angepaßt, eingebaut und es geht so.

Allerdings stellt sich noch eine Frage : wie verwende ich das im Zusammenhang mit SUM ? Was, wenn ich eine Brutto-Liste brauche von 10/2005 bis 3/2006 und ab 1.1. gilt 18 % ? :shock: Siehe Source weiter oben.

[alex517]

Mit First 1 erhält man nur genau den ersten Datensatz einer
Ergebnismenge.

Das ist notwendig, da es mehrere Sätze mit dem gleichen MWSt-Kz geben kann.
Von denen ist aber nur der mit dem jüngsten Datum das kleiner gleich
dem Verkaufsdatum ist der Richtige.
Man beachte die Sortierung!

SQL-Code:

SELECT FIRST 1 
    MWSTSATZ 
  FROM 
    MWST 
  WHERE 
    (KZ=:KZ) AND (ABDATUM <= :VK_DATUM) 
  ORDER BY 
    ABDATUM DESC

So bekommt man auch Summen.

SQL-Code:

select
  EXTRACT(YEAR from V.vk_datum) as YY,
  EXTRACT(Month from V.vk_datum) as MM,
  M.MWSTSATZ,
  sum(V.VK_NETTO*(1+M.MWSTSATZ)) as Brutto
from 
  VERKAUF V 
  left JOIN SP_GET_MWST(V.MWST_KZ, V.VK_DATUM) M on (1=1)
group by 1,2,3

alles klar?

alex

[Hansa]

Was hat das hier zu bedeuten :

SQL-Code:

ON (1=1)

:shock: Und das

SQL-Code:

GROUP BY 1,2,3

ist mir auch nicht ganz klar. Prinzipiell geht es ancheinend schon. Es ist aber immer interessant zu wissen, warum genau. :mrgreen:

[Jelly]

Zitat:

Zitat von Hansa

Und das

SQL-Code:

GROUP BY 1,2,3

Das bedeutet nichts anderes als dass nach der 1., 2. und 3. Spalte deines Resultsets gruppiert wird. Hat den gleiche Effekt, als wenn du die Spaltennamen hinschreibst. Ich ziehe jedenfalls die Spaltennamen vor, denn wenn du was im Select änderst, kann dein group by falsch werden.

Das Ganze funktioniert übrigens auch bei "order by", wo das dann auch mehr Sinn macht. Hast du berechnete Felder in deinem Select, so kannst du so leichter nach diesen Feldern sortieren lassen, statt den ganzen berechnenden Ausdruck nochmals in den "order by" Teil zu kopieren.

[alex517]

Zitat:

Zitat von Jelly

Ich ziehe jedenfalls die Spaltennamen vor, denn wenn du was im Select änderst, kann dein group by falsch werden.

Ich eigentlich auch. In diesem Beispiel wolle ich nur nicht so viel schreiben.
Vorteil für hansa, er hat was dazugelernt. :wink:
Aber manchmal ist es aber auch nicht möglich Namen zu verwenden z.B. in einer UNION.

Zitat:

Zitat von hansa

Was hat das hier zu bedeuten :

SQL-Code:

ON (1=1)

Da die Stored Procedure über ein Join eingebunden wird und dieses Join
von der Syntax am Ende ein "ON (logische Ausdruck)" verlangt,
hat es dieses mit "ON (1=1)" auch bekommen. :zwinker:

alex

[Hansa]

Es gibt noch ein Problem :

SQL-Code:

  SELECT MWSTWERT FROM MWSTWERTSP (:MWSTSATZ,:MONAT,:JAHR) INTO :VAR_MWSTWERT;
  VAR_UMSATZBRUTTO = :UMSATZ * (1 + :VAR_MWSTWERT / 100);

VAR_UMSATZBRUTTO ist immer 0. Das ganze ist innerhalb einer SP. Ich rufe innerhalb dieser SP also die proc. MWSTWERTSP auf. Lasse ich in IBExpert nur diese Mwst-Proz. laufen, so stimmt das Ergebnis schon. Ein NULL Wert kann nicht entstehen, da so was in einem vorgeschalteten Trigger abgefangen wird. Wer weiß mehr ?

[alex517]

Hi,

zeig doch mal die SP's komplett, damit man sich ein Bild machen kann.

Doppelpunkte werden nur in einer SP bei SELECT, UPDATE usw. verwendet
wenn innerhalb des Statements auf Variablen zugegriffen werden soll.

Zitat:

Zitat von Hansa

SQL-Code:

  VAR_UMSATZBRUTTO = :UMSATZ * (1 + :VAR_MWSTWERT / 100);

Hier, glaube ich, sind die Doppelpunkte fehl am Platz.

alex

[Hansa]

SQL-Code:

CREATE PROCEDURE KUARTSTATSP (
    ID_KUNDE INTEGER,
    ID_ART INTEGER,
    MONAT SMALLINT,
    JAHR SMALLINT,
    MWSTSATZ SMALLINT,
    UMSATZ DECIMAL(15,2)
AS
DECLARE VARIABLE VAR_MWSTWERT DECIMAL(15,2);
DECLARE VARIABLE VAR_UMSATZBRUTTO DECIMAL(15,2);
begin
  SELECT MWSTWERT FROM MWSTWERTSP8 (:MWSTSATZ,:MONAT,:JAHR) INTO :VAR_MWSTWERT;
  VAR_UMSATZBRUTTO = :UMSATZ * (1 + VAR_MWSTWERT / 100);
    insert into KUARTSTAT8 (MONAT,JAHR,MWSTSATZ,ID_KUNDE,ID_ART,UMSATZ,UMSATZBRUTTO)
           values (:MONAT,:JAHR,:MWSTSATZ,:ID_KUNDE,:ID_ART,:UMSATZ,:VAR_UMSATZBRUTTO);
  end
  suspend;
end

Das ist jetzt stark verkürzt, aber der Hund ist da begraben. 8) Seltsamerweise ist noch folgender Effekt zu bemerken, wenn ich das Insert durch ein Update ersetze :

SQL-Code:

    update KUARTSTAT set  UMSATZ=UMSATZ+ :UMSATZ,
                          UMSATZBRUTTO=UMSATZBRUTTO + :VAR_UMSATZBRUTTO,
                where (MONAT= :MONAT) and
                      (JAHR= :JAHR) and
                      (ID_ART= :ID_ART) and
                      (ID_KUNDE= :ID_KUNDE);

Sofern ich im Insert einen festen Wert einsetze, dann steht der drin. Nach dem Update ist er aber trotz BI-Trigger NULL. An dem Wert ist trotz (zwar vorhandenem) AU-Trigger nichts geändert worden.

[bernau]

Und noch ein kleiner Tipp zu Rundungsfehlern, die man immer berücksichtigen sollte:


Ein bestimmter Artikel kostet 3,33 EUR zzgl. 16%MwSt (0,53EUR MwSt) = 3,86 EUR Brutto

Im Monat werden 100 Rechnungen mit diesem Artikel geschrieben. 100 * 3,86EUR = 386.00 EUR

Wenn aber für alle 100 Artikel zuerst der Gesamt-Netto-Betrag berechnet wird ( = 333 EUR) und daraus die MwSt ( 53,28 EUR) berechnet wird, erhält man 386,28 EUR

Da hat man also für 0,28 EUR weniger auf dem Konto, als man denkt ;-)

Nur so zur Info.



Gerd

[Hansa]

Zitat:

Zitat von bernau

...Nur so zur Info.

1+1=2 ? Jo, das weiß ich. :mrgreen: Das Problem ist damit aber nicht gelöst !

[marabu]

Hallo hansa,

der von dir gepostete code für die stored procedure KUARTSTATSP compiliert nicht und kann also nicht der bei dir funktionierende code sein. Aber das weißt du sicher und willst nur mal abwarten, ob es jemand merkt - stimmt's?

Grüße vom marabu

[Hansa]

Was soll das ? Ich poste absichtlich keinen Code der sich nicht mal compilieren läßt. Allerdings ist er ja verkürzt und da ist ganz klar zu sehen, daß hinter dem UMSATZ DECIMAL (15,2) eine ) fehlt !! Die vom Create. Habe das nicht gesehen, aber für DB-Profis dürfte das kein Hindernis sein. In der Fehlermeldung wird sogar auf das fehlende ) hingewiesen. 8)

[marabu]

Hansa, das ist sehr nett von dir, dass du den Code um eine schließende Klammer kürzt, damit mein download schmaler wird. Hast auch gleich noch ein begin und ein paar weitere Zeilen weggelöscht. Das letzte mal, als du Hilfe gesucht hast, da lag der Fehler in dem Teil des codes, den wir nicht sehen konnten. Ich vermute, das du in den weggekürzten Zeilen die Variable Umsatz auf 0 setzt.

marabu

[Robert_G]

Hallo marabu,
ich würde es schade finden, wenn du dich durch Hansas arrogante Ansichten[1] hier fehl am Platz fühlen würdest.
Mache es wie ich und ignoriere das einfach, du hast dich ja schon oft genug über ihn geärgert. ;)

[1]Er denkt wirklich, dass seine 20 Zeilen PSQL so komplex sind, dass er ihn schützen muss :wall:
Und er denkt wirklich, dass er sich proffessionell verhält...

[Hansa]

Hi Robert. 8) Ignorieren ist für mich was anderes als hier rumzustänkern ! Damit meine ich jetzt Dich, sonst keinen !

Ich teste mit folgender Table :

SQL-Code:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/

CREATE GENERATOR GEN_KUARTSTAT_ID;

CREATE TABLE KUARTSTAT (
    ID            INTEGER NOT NULL,
    ID_KUNDE      INTEGER NOT NULL,
    ID_ART        INTEGER NOT NULL,
    MONAT         SMALLINT NOT NULL,
    JAHR          SMALLINT NOT NULL,
    MWSTSATZ      SMALLINT,
    UMSATZ        DECIMAL(15,2),
    UMSATZBRUTTO  DECIMAL(15,2)
);

/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE KUARTSTAT ADD CONSTRAINT UNQ_KUARTSTAT UNIQUE (ID_KUNDE, ID_ART, MONAT, JAHR);

/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE KUARTSTAT ADD CONSTRAINT PK_KUARTSTAT PRIMARY KEY (ID);

/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE KUARTSTAT ADD CONSTRAINT FK_ART_KUARTSTAT FOREIGN KEY (ID_ART) REFERENCES ART8 (ID) ON DELETE CASCADE;
ALTER TABLE KUARTSTAT ADD CONSTRAINT FK_KUNDE_KUARTSTAT FOREIGN KEY (ID_KUNDE) REFERENCES KUNDE8 (ID) ON DELETE CASCADE;


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: KUARTSTAT_BI0 */
CREATE TRIGGER KUARTSTAT_BI0 FOR KUARTSTAT
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.ID is null) then
    new.ID = gen_id(GEN_KUARTSTAT_ID, 1);
  if (NEW.UMSATZ IS NULL) then
    NEW.UMSATZ = 0;
end
^

Die aufrufende Prozedur ist die hier :

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE KUARTSTATSP (
    ID_KUNDE INTEGER,
    ID_ART INTEGER,
    MONAT SMALLINT,
    JAHR SMALLINT,
    MWSTSATZ SMALLINT,
    UMSATZ DECIMAL(15,2))
AS
DECLARE VARIABLE VAR_MWSTWERT DECIMAL(15,2);
DECLARE VARIABLE VAR_UMSATZBRUTTO DECIMAL(15,2);
begin
  SELECT MWSTWERT FROM MWSTWERTSP8 (:MWSTSATZ,:MONAT,:JAHR) INTO :VAR_MWSTWERT;
  VAR_UMSATZBRUTTO = :UMSATZ * (1 + VAR_MWSTWERT / 100); 
  insert into KUARTSTAT (MONAT,JAHR,MWSTSATZ,ID_KUNDE,ID_ART,UMSATZ,UMSATZBRUTTO)
           values (:MONAT,:JAHR,:MWSTSATZ,:ID_KUNDE,:ID_ART,:UMSATZ,:VAR_UMSATZBRUTTO); 
  suspend;
end
^

SET TERM ; ^

Die Proc. innerhalb der SP ist die hier:

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE MWSTWERTSP8 (
    MONAT SMALLINT,
    JAHR SMALLINT,
    MWSTSATZ SMALLINT)
RETURNS (
    MWSTWERT NUMERIC(9,2))
AS
DECLARE VARIABLE DATUM DATE;
BEGIN 
  DATUM =  CAST('01.'|| MONAT || '.' || JAHR AS DATE);
  SELECT FIRST 1 
    MWSTWERT
  FROM 
    MWST8
  WHERE 
    (MWSTSATZ=:MWSTSATZ) AND (ABDATUM <= :DATUM)
  ORDER BY 
    ABDATUM DESC
  INTO 
    :MWSTWERT;
  IF (MWSTSATZ is null) THEN
    MWSTSATZ = 0;
  SUSPEND;
END
^

SET TERM ; ^

Thx an Alex, daß die tatsächlich geht. Die ermittelt die Mwst. abhängig vom Datum. Wer sieht jetzt da den Fehler ? Rumlamentieren hilft da jetzt eben nichts. 8)

Edit 2 :

Die Mwst.-Table fehlt noch :

SQL-Code:

/******************************************************************************/
/****              Generated by IBExpert 08.09.2005 00:10:40               ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GEN_MWST8_ID;

CREATE TABLE MWST8 (
    ID               INTEGER NOT NULL,
    ABDATUM          DATE,
    MWSTSATZ         SMALLINT,
    MWSTWERT         DECIMAL(15,2),
    ANGELEGT         TIMESTAMP,
    LETZTEAENDERUNG  TIMESTAMP
);




/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: MWST8_AUO */
CREATE TRIGGER MWST8_AUO FOR MWST8
ACTIVE AFTER UPDATE POSITION 0
as
begin
  update MWST8 set
    LETZTEAENDERUNG = current_timestamp
  where (ID = old.ID) and ((LETZTEAENDERUNG is null) or
        (LETZTEAENDERUNG <> current_timestamp));
end
^


/* Trigger: MWST8_BI0 */
CREATE TRIGGER MWST8_BI0 FOR MWST8
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.ID is null) then
    new.ID = gen_id(GEN_MWST8_ID, 1);
  if (new.angelegt is null) then
    new.ANGELEGT = current_timestamp;
end
^


SET TERM ; ^

Reicht das jetzt ? :mrgreen: Aber wie gesagt : vieles fehlt, was ich normalerweise noch brauche.

[marabu]

Hallo Hansa,

relevant für deinen Fehler "VAR_UMSATZBRUTTO ist immer 0" ist von deinem Riesen-Posting nur die Prozedur KUARTSTATSP. Offensichtlich veränderst du die Variable Umsatz in dieser Prozedur nicht, so dass ich meinen Hinweis von weiter oben abändere und behaupte, dass dieser Parameter schon beim Aufruf mit 0 belegt ist. Wie sonst sollte die Berechnung innerhalb der Prozedur zum Ergebnis 0 kommen?

marabu

[alex517]

Hallo Hansa,

ich kann mich nur nochmal wiederholen.
Entferne die Doppelpunkte. Begründung siehe oben.

Zitat:

hansa

SQL-Code:

CREATE PROCEDURE KUARTSTATSP ( 
...
  VAR_UMSATZBRUTTO = :UMSATZ * (1 + VAR_MWSTWERT / 100); 
...
end

alex

[marabu]

Hallo Alex,

mit oder ohne colon ist in IB6 egal - ist das beim FB15 anders?

Mit colon ist irgenwie konsistenter, er signalisiert ja sowas wie eine Dereferenzierung - den Zugriff auf den Inhalt.

Freundliche Grüße vom marabu

[Hansa]

An den Doppelpunkten liegts echt nicht. Ich habe den Fehler eingekreist :

SQL-Code:

CREATE TABLE MWST8 (
    ID               INTEGER NOT NULL,
    ABDATUM          DATE,
    MWSTSATZ         SMALLINT,
    MWSTWERT         DECIMAL(15,2),
    ANGELEGT         TIMESTAMP,
    LETZTEAENDERUNG  TIMESTAMP
);


INSERT INTO MWST8 (ID, ABDATUM, MWSTSATZ, MWSTWERT, ANGELEGT, LETZTEAENDERUNG) VALUES (1, '1998-04-01', 2, 16, '2005-09-03 12:26:31', NULL);
INSERT INTO MWST8 (ID, ABDATUM, MWSTSATZ, MWSTWERT, ANGELEGT, LETZTEAENDERUNG) VALUES (2, '1998-04-01', 1, 7, '2005-09-03 12:26:47', NULL);
INSERT INTO MWST8 (ID, ABDATUM, MWSTSATZ, MWSTWERT, ANGELEGT, LETZTEAENDERUNG) VALUES (3, '2005-04-01', 2, 18, '2005-04-01 00:00:00', '2005-09-04 16:50:21');
INSERT INTO MWST8 (ID, ABDATUM, MWSTSATZ, MWSTWERT, ANGELEGT, LETZTEAENDERUNG) VALUES (4, '1900-01-01', 2, 15, '2005-09-03 13:55:15', NULL);

COMMIT WORK;



/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE MWSTWERTSP8 (
    MONAT SMALLINT,
    JAHR SMALLINT,
    MWSTSATZ SMALLINT)
RETURNS (
    MWSTWERT NUMERIC(9,2))
AS
DECLARE VARIABLE DATUM DATE;
BEGIN 
  DATUM =  CAST('01.'|| MONAT || '.' || JAHR AS DATE);
  SELECT FIRST 1 
    MWSTWERT
  FROM 
    MWST8
  WHERE 
    (MWSTSATZ=:MWSTSATZ) AND (ABDATUM <= :DATUM)
  ORDER BY 
    ABDATUM DESC
  INTO 
    :MWSTWERT;
  IF (MWSTSATZ is null) THEN
    MWSTSATZ = 0;
MWSTWERT = 10;  <-------------------------------------------
  SUSPEND;
END
^

Setze ich den MWSTWERT von Hand auf 10 (siehe oben), dann stimmt auch der Bruttoumsatz auf der Grundlage von 10 % Mwst. Die verwendeten Testdaten sind auch dabei. Gebe ich 100 ein so kommt 110 dabei raus. Mache ich das nochmals dann ist es 220. Also richtig. Lasse ich den MWSTWERT von der SP ermitteln, ohne diese Zeile, dann gehts nicht. 8)

[alex517]

Hi marabu,

Zitat:

Zitat von marabu

mit oder ohne colon ist in IB6 egal - ist das beim FB15 anders?

Beim Zugriff auf Variablen in assignment statements einer SP wird laut Manual IB6
colon nicht gesetzt.
Im Gegensatz zu SELECT/UPDATE/INSERT.. -statements.

Zitat:

DataDef.pdf, CHAPTER 9 WORKING WITH STORED PROCEDURES INTERBASE 6

Using assignment statements
---------------------------
A procedure can assign values to variables with the syntax:
variable = expression;
where expression is any valid combination of variables, operators, and expressions, and
can include user-defined functions (UDFs) and generators.
A colon need not precede the variable name in an assignment statement. For example,
the following statement assigns a value of zero to the local variable, ANY_SALES:
any_sales = 0;

Variables should be assigned values of the datatype that they are declared to be. Numeric
variables should be assigned numeric values, and character variables assigned character
values. InterBase provides automatic type conversion. For example, a character variable
can be assigned a numeric value, and the numeric value is automatically converted to a
string. For more information on type conversion, see the Embedded SQL Guide.



Using SELECT statements
-----------------------
In a stored procedure, use the SELECT statement with an INTO clause to retrieve a single
row value from the database and assign it to a host variable. The SELECT statement must
return at most one row from the database, like a standard singleton SELECT. The INTO
clause is required and must be the last clause in the statement.
For example, the following statement is a standard singleton SELECT statement in an
application:
EXEC SQL
SELECT SUM(BUDGET), AVG(BUDGET)
INTO :tot_budget, :avg_budget
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept;

To use this SELECT statement in a procedure, move the INTO clause to the end as follows:
SELECT SUM(BUDGET), AVG(BUDGET)
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept
INTO :tot_budget, :avg_budget;

For a complete discussion of SELECT statement syntax, see the Language Reference.

Die SP läßt sich zwar im Falle von Hansa compilieren, aber bei Verwendung des
Doppelpunktes ist ":UMSATZ" gleich NULL.
Damit ist auch das Ergebnis der Zuweisung gleich NULL.

VAR_UMSATZBRUTTO = NULL * (1 + VAR_MWSTWERT / 100) --> NULL


alex

[marabu]

Alex, da steht "need not" und nicht "must not". Ich habe mir die Verwendung des colon auch in assignments aus dem angegebenen Grund angewöhnt. Sobald der Parser geändert wird, werde ich es mir wieder abgewöhnen müssen, aber bis dahin: es funktioniert bei mir.

marabu

[marabu]

Hansa, du solltest vielleicht Namensüberdeckungen vermeiden:

SQL-Code:

CREATE PROCEDURE MWSTWERTSP8 (
  iMONAT SMALLINT,
  iJAHR SMALLINT,
  iMWSTSATZ SMALLINT )
RETURNS (
  oMWSTWERT NUMERIC(9,2) )
AS
  DECLARE VARIABLE vDATUM DATE;
  DECLARE VARIABLE vMWSTWERT NUMERIC(9,2);
BEGIN
  vDATUM = CAST('01.'|| iMONAT || '.' || iJAHR AS DATE);
  SELECT FIRST 1 mwstwert
    FROM mwst8
    WHERE (mwstsatz = :iMWSTSATZ) AND (abdatum <= :vDATUM)
    ORDER BY abdatum DESC
    INTO :vMWSTWERT;
  oMWSTWERT = :vMWSTWERT;
  SUSPEND;
END

marabu (getippt und nicht getestet)

[alex517]

Zitat:

da steht "need not" und nicht "must not".

Das habe ich gelesen. Und aus diesem Grund colon in assignments auch nicht verwendet. ;-)
In FB ist scheinbar aus dieser "Empfehlung" ein Muß geworden.

alex

[marabu]

Alex, wenn das stimmt, dass der FB Parser an der Stelle nicht mehr kompatibel zum IB Parser ist, dann frage ich mich, wie Hansa getestet hat. FB hat er offensichtlich genommen, denn sonst wäre er ja über FIRST gestolpert.

Zitat:

Zitat von Hansa

An den Doppelpunkten liegts echt nicht

marabu

[alex517]

Hi Hansa, hi marabu,

Ihr habt beide Recht an den Doppelpunkten liegts nicht.

Hansa, sieh dir doch mal die Parameterreihenfolge der
MWSTWERTSP8-Deklaration und beim Aufruf der MWSTWERTSP8
an:

SQL-Code:

CREATE PROCEDURE MWSTWERTSP8 (
    MONAT SMALLINT,
    JAHR SMALLINT,
    MWSTSATZ SMALLINT)
RETURNS (
    MWSTWERT NUMERIC(9,2))

SQL-Code:

  SELECT MWSTWERT FROM MWSTWERTSP8 (:MWSTSATZ,:MONAT,:JAHR) INTO :VAR_MWSTWERT;

alex