Ersatz für DISTINCT ??

[Hansa]

Hi,

Bin etwas am testen. Rückgabe einer stored Procedure liefert folgendes :

Delphi-Quellcode:

ID_OUT   MWSTSATZ_OUT   ABDATUM_OUT   MWSTWERT_OUT
4   0   01.01.1900   0
2   1   01.01.1980   7
5   2   01.01.1980   15
1   2   01.04.1998   16
3   2   01.01.2007   19

Die SP sieht so aus :

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (
    ABDATUM DATE)
RETURNS (
    ID_OUT INTEGER,
    MWSTSATZ_OUT INTEGER,
    ABDATUM_OUT DATE,
    MWSTWERT_OUT DECIMAL(15,2))
AS
begin
FOR
  SELECT ID,MWSTSATZ,ABDATUM, MWSTWERT FROM MWST WHERE ABDATUM <= :ABDATUM
  ORDER BY MWSTSATZ
  INTO :ID_OUT,:MWSTSATZ_OUT,:ABDATUM_OUT,:MWSTWERT_OUT
  DO
  SUSPEND;
end^

Siehe die letzten 3 Zeilen der Beispieldaten. Da steht jetzt 15,16,19, aber ich brauche nur den einen Wert, der zum Input-Parameter paßt. Das ist sicherlich einfach einzubauen, bloß wie ? :mrgreen:

[dataspider]

Hi,

in einem Statement ist es wohl nicht zu machen.
Ich würde es etwa so lösen:

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (
    abdatum date)
returns (
    id_out integer,
    mwstsatz_out integer,
    abdatum_out date,
    mwstwert_out numeric(15,2))
as
begin
for
  select distinct(mwstsatz) from mwst
         order by mwstsatz
         into :mwstsatz_out  do
  begin
    select max(abdatum) from mwst where
           mwstsatz = :mwstsatz_out and
           abdatum <= :abdatum
           into :abdatum_out;

    select id, mwstwert from mwst where
           mwstsatz = :mwstsatz_out and
           abdatum = :abdatum_out
           into :id_out, :mwstwert_out;
    suspend;
  end
end

Cu, Frank

[Sharky]

Ich arbeite zwar nicht mit FB; aber kennt dies keinen Befehl wie MAX?
Was möchtest Du denn haben? Den höchsten MwSt-Satz der ab einem bestimmten Datum zulässig ist?

Das kann aber so wie es in deiner Tabelle steht doch nicht sein oder?
Ein MwSt Satz ist solange güglich bis es einen neuen gibt. Du musst also eine Abrage machen welche Dir für ein Datum den geltenden Mehrwertsteuersatz liefert. Alles andere ist doch eher ein Fehler in der Logik. Oder verstehe ich das jetzt falsch?

[dataspider]

Zitat:

Zitat von Sharky

Ich arbeite zwar nicht mit FB; aber kennt dies keinen Befehl wie MAX?

Solange man sich auf die Politiker wenigstens dahingehend verlassen kann, dass Märchensteuer immer erhöht und nicht gesenkt wird, kann man auch ein Statement und max nehmen. :wink:

Aber man sollte sich wohl doch vorsichtshalber am Datum orientieren.

Frank

[marabu]

Hallo Hansa,

du machst dir das Leben unnötig schwer, indem du die Steuersätze einzeln speicherst. Bei der Novellierung des Umsatzsteuergesetzes wird der ermäßigte Steuersatz auch dann neu geregelt, wenn sich der Wert gar nicht ändert. Du könntest also guten Gewissens beide Steuersätze unter dem gleichen Wirk-Datum speichern und dann einfach per

SQL-Code:

SELECT FIRST 1 ... WHERE WHERE ABDATUM <= :ABDATUM ORDER BY ABDATUM DESC

den relevanten Eintrag lokalisieren - um noch eine Alternative zu Franks MAX() Funktion zu bringen.

Grüße vom marabu

[Hansa]

MAX ? Natürlich gibts das. Aber nützt das was ? :gruebel: Aber nochmals : ich habe einige Datensätze. Die 7 % wurden bei der Erhöhung von 15 auf 16 % z.B. NICHT erhöht. Insofern habe ich für 16 % (siehe Bsp.) 3 Datensätze. Für 0 oder 7 nur einen. Ich brauche nun z.B. die von heute, den 3.8.2006 und zwar für alle 3 (0,7,16). Ich brauche nicht den 19% Satz ab 1.1.2007. Ist 2007 erreicht, dann soll die SP allerdings 19 zurückliefern und nicht mehr 16.

P.S.: Für Kommentare über die dämlichen Dilletanten jegliche Coleur, die lediglich unser Geld verprassen bitte separaten Thread aufmachen. :mrgreen:

[mkinzler]

Zitat:

MAX ? Natürlich gibts das. Aber nützt das was ?

Könnte helfen denn du bekommst ja den grrößte datum, welches kleiner/gleich wie das heutige Datum ist.

[Hansa]

Unter der Voraussetzung, daß es mind. 3 Sätze gibt, geht es so :

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (
    ABDATUM DATE)
RETURNS (
    MWSTSATZ_OUT INTEGER,
    MWSTWERT_OUT DECIMAL(15,2))
AS
begin
FOR
SELECT FIRST 3 MWSTSATZ, MWSTWERT FROM MWST WHERE ABDATUM <= :ABDATUM ORDER BY MWSTSATZ,ABDATUM DESC
  INTO :MWSTSATZ_OUT,:MWSTWERT_OUT
  DO
  SUSPEND;
end^

Problem scheint also tatsächlich auf den Kern reduziert. Oder sieht jemand noch einen Fehler ?

[marabu]

Nur wenn du alle überflüssigen weil ungeänderten Werte - insbesondere den überflüssigen mit 0 - auch bei jeder Gesetzesänderung speicherst geht das hier:

SQL-Code:

... ORDER BY ABDATUM DESC, MWSTSATZ ...

Aber wie ich schon schrieb machst du dir das Leben unnötig schwer.

Gute Nacht

marabu

[Hansa]

Marabu, es geht um alle zu einem gegebenen Datum gültigen. Das sind jetzt eben 0,7,16. Ab 1.1. sind es 0,7,19. Wegen alter Sachen 2006 brauche ich den mit 16 bis 2006 trotzdem noch ! D.h., es liegen mit 0 und 7 nur ein Datensatz vor, für den Mwstsatz 2 aber 2 ! Ich test ja sogar noch mit 15 %, das wären 3. Der Code geht schon so. Ich kriege mit First 3 immer die richtigen drei Werte. Jetzt eben 0,7,16. Gebe ich für den Parameter 1.1.2007 ein, dann kommt 0,7,19. Was soll ich da noch wie vereinfachen ? :shock:

Stop ! Jetzt ich Kapito. :mrgreen: Du meinst direkt 3 integers für einen Datensatz ? Oh je. :gruebel: Brauche z.B. für Belgien : 0 3 6,5 12 und 19. Und was wäre wenn zum 1.1.2008 festgestellt wird, daß schon wieder zu viel Geld verprasst wird und man sich entschließt, nur die 7 auf 8 % anzuheben ? :wall:

[TBx]

Hallo Hansa,

versuch mal folgendes:

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (ABDATUM DATE) 
RETURNS ( 
    ID_OUT INTEGER, 
    MWSTSATZ_OUT INTEGER, 
    ABDATUM_OUT DATE, 
    MWSTWERT_OUT DECIMAL(15,2)) 
AS 
begin 
  FOR SELECT MWSTSATZ  
        FROM MWST
        WHERE ABDATUM <= :ABDATUM
        GROUP BY MWSTSATZ  /* GROUP BY statt SELECT DISTINCT verwendet, da GROUP BY indiziert arbeitet, DISTINCT nicht */
        INTO :MWSTSATZ_OUT
  DO /* fuer jeden bis :ABDATUM vorhandenen MWSTSATZ */
  BEGIN
    /* juengstes Aenderungsdatum ermitteln */
    SELECT max (ABDATUM)
      FROM MWST
      WHERE MWSTSATZ = :MWSTSATZ_OUT
        AND ABDATUM <= :ABDATUM
      INTO :ABDATUM_OUT;
    /* jetzt noch ID und Wert ermitteln */
    SELECT ID, MWSTWERT
      FROM MWST
      WHERE MWSTSATZ = :MWSTSATZ_OUT
        AND ABDATUM = :ABDATUM_OUT
      INTO :ID_OUT, :MWSTWERT_OUT;
    SUSPEND;
  END
end

Du solltest aus Geschwindigkeitsgründen in der Tabelle MWST Indexe auf

• MWSTSATZ
• MWSTWERT
• ABDATUM

eingerichtet haben.

Des weiteren bin ich davon ausgegangen, dass es einen Unique über MWSTSATZ und ABDATUM gibt.
Ansonsten könnte die letzte select in der Procedure ein
multiple rows in a singelton select
hervorrufen.

Das Ganze ist runtergetippt und nicht getestet, da ich gerade keinen DB-Server am Laufen habe.
Bei Fehlfunktion bitte nachfragen.

Hope it helps

onlinekater

[Mavarik]

Warum so eine kleinigkeit auf den SQL Server auslagern?

Wie wäre es mit?

Delphi-Quellcode:

function  GetMwStWert(D:Datumstyp):real;  // Noch aus früheren Zeiten... Da gabe es noch keine Datetime!
var
  DDD  : Datumstyp;
begin
 if D.Jahr > 92
   then begin
          ValDate('01.04.98',DDD);
          if AbsDatum(D) >= AbsDatum(DDD)
            then result := 16.0
            else result := 15.0;
        end
   else result := 14.0;
end;

Frank :coder:

PS.: Die Erweiterung für 01.01.07 ist ja ein Kinderspiel!

[marabu]

Hallo Frank,

Zitat:

Zitat von Mavarik

Warum so eine kleinigkeit auf den SQL Server auslagern?

Hansa hat sich nunmal für ein RDBMS entschieden. Er speichert bei einer Artikelposition einen USt-Schlüssel (1 für ermäßigter USt-Satz), welcher innerhalb der Datenbank aufgelöst werden muss. Deshalb gibt es eine Tabelle mit den Umsatzsteuersätzen. Und weil diese Sätze per Gesetz fortgeschrieben werden ist die Tabelle als history table ausgelegt. Bei einer Auslagerung der Steuersatzermittlung in dein Programm wären die Daten in der Datenbank nicht mehr "abgeschlossen". Eine Auswertung mit einem SQL Report-Tool wäre nicht mehr möglich, weil das Wissen um die Bedeutung der Steuersätze außerhalb der Datenbank (in deinem Programm) vorgehalten wird. Das ist unbedingt zu vermeiden.

Freundliche Grüße vom marabu

[Mavarik]

Zitat:

Zitat von marabu

Bei einer Auslagerung der Steuersatzermittlung in dein Programm wären die Daten in der Datenbank nicht mehr "abgeschlossen". Eine Auswertung mit einem SQL Report-Tool wäre nicht mehr möglich, weil das Wissen um die Bedeutung der Steuersätze außerhalb der Datenbank (in deinem Programm) vorgehalten wird. Das ist unbedingt zu vermeiden.

Ahh alles klar, habe ich nicht "drüber" nachgedacht....

Das leuchtet mit ein, danke für die Erläuterung!

Frank :coder:

[MarkusB]

Moin!

SQL-Code:

select max(MWSTWERT_OUT) from
(select MWSTWERT_OUT from Tabelle
where ABDATUM_OUT <= Datum)

Hilft das?

Viele Grüße
Markus
:gruebel:

[dataspider]

Hi Hansa,

was gefällt dir an meiner Procedure nicht. Sie liefert genau das, was du willst.
Und onlinekater ist ja genau zum gleichen Ergebnis gekommen.

Cu, Frank

[TBx]

Zitat:

Zitat von Hansa

Unter der Voraussetzung, daß es mind. 3 Sätze gibt, geht es so :

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (
    ABDATUM DATE)
RETURNS (
    MWSTSATZ_OUT INTEGER,
    MWSTWERT_OUT DECIMAL(15,2))
AS
begin
FOR
SELECT FIRST 3 MWSTSATZ, MWSTWERT FROM MWST WHERE ABDATUM <= :ABDATUM ORDER BY MWSTSATZ,ABDATUM DESC
  INTO :MWSTSATZ_OUT,:MWSTWERT_OUT
  DO
  SUSPEND;
end^

Problem scheint also tatsächlich auf den Kern reduziert. Oder sieht jemand noch einen Fehler ?

Hallo Hansa!
Nein, das funktioniert nicht korrekt.
Wenn Du z. B. folgende Daten hast:

Zitat:

ID, MWSTSATZ, ABDATUM, MWSTWERT
1, 0, 01.01.1980, 0
2, 0, 31.12.1980, 63
3, 1, 29.05.1990, 13
4, 2, 26.09.1995, 16
5, 2, 01.01.1980, 14
6, 1, 01.01.1980, 7

und Du rufst obige Procedure mit ABDATUM = '01.01.2005' auf, dann erhieltest Du folgendes:

Zitat:

ID_OUT, MWSTSATZ_OUT, ABDATUM_OUT, MWSTWERT_OUT
1, 0, 01.01.1980, 0
2, 0, 31.12.1980, 63
6, 1, 01.01.1980, 7

Ich denke, das wäre nicht in Deinem Sinne.

Mir ist gerade erst aufgefallen, das ich fast dieselbe Procedure gepostet habe, wie Frank. Da habe ich nicht so ganz aufgepaßt. Meine Zusatzbemerkungen passen zu seiner Procedure genauso wie zu meiner.

Gruß

Thomas

[alex517]

Hi Hansa,

wie wäre es denn damit:

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (
    ABDATUM DATE)
RETURNS (
    ID_OUT INTEGER,
    MWSTSATZ_OUT INTEGER,
    ABDATUM_OUT DATE,
    MWSTWERT_OUT DECIMAL(15,2))
AS
BEGIN
  if (ABDATUM is NULL) then ABDATUM = CURRENT_DATE;
  for
    select
      distinct S.MWSTSATZ
    from
      mwst S
    into
      :MWSTSATZ_OUT
    DO BEGIN
      for
        select first 1
          M.ID,
          M.ABDATUM,
          M.MWSTWERT
        from
          mwst M
        where
          M.MWSTSATZ = :MWSTSATZ_OUT and
          M.ABDATUM <= :ABDATUM
        order by
          M.ABDATUM desc
        INTO
          :ID_OUT,
          :ABDATUM_OUT,
          :MWSTWERT_OUT
        DO
          SUSPEND;
    END
END

alex

[Niko]

Hi,

es geht auch mit "reinem" SQL:

SQL-Code:

select m1.MWSTSATZ, m1.MWSTWERT
from MWST m1 join MWST m2 using (MWSTSATZ)
where m2.ABDATUM_OUT <= :ABDATUM
group by m1.MWSTSATZ, m1.ABDATUM, m1.MWSTWERT
having m1.ABDATUM = max(m2.ABDATUM)
order by m1.MWSTSATZ
into :MWSTSATZ_OUT, :MWSTWERT_OUT

Ungetestet, sollte aber passen. Ob das jetzt besonders performant ist kann ich nicht sagen. Das dürfte bei der kleinen Tabelle aber sowieso nichts ausmachen.

[alex517]

Hi Niko

Zitat:

Zitat von Niko

es geht auch mit "reinem" SQL:

Überredet...
..wenn man "using (MWSTSATZ)" durch

SQL-Code:

on (m2.MWSTSATZ = m1.MWSTSATZ)

ersetzt.

alex

[TBx]

nicht überredet...

m2.ABDATUM_OUT gibt es nicht

Macht man dann daraus folgendes:

SQL-Code:

select m1.MWSTSATZ, m1.MWSTWERT
from MWST m1 join MWST m2 on (m2.MWSTSATZ = m1.MWSTSATZ)
where m2.ABDATUM <= :ABDATUM
group by m1.MWSTSATZ, m1.ABDATUM, m1.MWSTWERT
having m1.ABDATUM = max(m2.ABDATUM)
order by m1.MWSTSATZ
into :MWSTSATZ_OUT, :MWSTWERT_OUT

so erhält man eine leere Datenmenge.

Gruß

onlinekater

[alex517]

hi onlinekater,
sei doch mal ein wenig flexibler :)

SQL-Code:

select
  m1.ID, m1.MWSTSATZ, m1.ABDATUM, m1.MWSTWERT
from
  MWST m1
  join MWST m2 on (m2.MWSTSATZ = m1.MWSTSATZ)
where
  m2.ABDATUM <= :ABDATUM
group by
  m1.ID, m1.MWSTSATZ, m1.ABDATUM, m1.MWSTWERT
having
  m1.ABDATUM = max(m2.ABDATUM)
order by
  m1.MWSTSATZ

jetzt überredet?

[TBx]

Zitat:

Zitat von alex517

hi onlinekater,
sei doch mal ein wenig flexibler :)

nene, dass hat nichts mit Biegsamkeit zu tun.
Der Code, den Du geradr gepostet hast, ergänzt ja auch nur ein paar Felder.

Die leere Datenmenge kam nicht durch die SQL-Anweisung zustande, sondern dadurch, daß in meinem IBExpert neuerdings der Null-Haken nicht verschwindet,
wenn man einen Value einträgt .... :wall: :wall: :wall:

Werte sind nun da

Zitat:

jetzt überredet?

nö, sondern überzeugt.


Ich möchte aber dazu noch anmerken, daß die Methode von dataspider und mir weniger Reads benötigt (auch wenn diese indiziert sind).

Jetzt bleibt es Hansas Geschmack überlassen, was er einsetzen möchte.

Gruß

Thomas

[alex517]

[krümelkackermodus]

Zitat:

Zitat von onlinekater

Ich möchte aber dazu noch anmerken, daß die Methode von dataspider und mir weniger Reads benötigt (auch wenn diese indiziert sind).

nach dem ich mir deine Bemerkung bezüglich GROUP BY <--> DISTINCT zu Herzen genommen habe,
jetz nochmal:

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP (
    ABDATUM DATE)
RETURNS (
    ID_OUT INTEGER,
    MWSTSATZ_OUT INTEGER,
    ABDATUM_OUT DATE,
    MWSTWERT_OUT DECIMAL(15,2))
AS
BEGIN
  if (ABDATUM is NULL) then ABDATUM = CURRENT_DATE;
  for
    select
      S.MWSTSATZ
    from
      mwst S
    GROUP BY MWSTSATZ
    into
      :MWSTSATZ_OUT
    DO BEGIN
      for
        select first 1
          M.ID,
          M.ABDATUM,
          M.MWSTWERT
        from
          mwst M
        where
          M.MWSTSATZ = :MWSTSATZ_OUT and
          M.ABDATUM <= :ABDATUM
        order by
          M.ABDATUM desc
        INTO
          :ID_OUT,
          :ABDATUM_OUT,
          :MWSTWERT_OUT
        DO
          SUSPEND;
    END
END

Ergebnis:
deine Variante 11 Reads
meine Variante 8 Reads
:-D
[/krümelkackermodus]
alex

[TBx]

Zitat:

nach dem ich mir deine Bemerkung bezüglich GROUP BY <--> DISTINCT zu Herzen genommen habe,
jetz nochmal:

freut mich, daß ich noch ein bißchen Wissen vermitteln konnte :-D :-D


yep, Deine Methode ist hier schneller

Gruß

Thomas

[Jürgen Thomas]

Hallo,

ich komme zwar etwas spät, aber vielleicht vereinfacht mein Verfahren doch einiges:
Füge in die Tabelle ein Feld DATUM_BIS ein, z.B:

Zitat:

Zitat von Hansa

MWSTSATZ ABDATUM DATUM_BIS MWSTWERT_OUT
0 01.01.1900 31.12.2999 0
1 01.01.1980 31.12.2999 7
2 01.01.1980 31.03.1998 15
2 01.04.1998 31.12.2006 16
2 01.01.2007 31.12.2999 19

Das erleichtert die gewünschte Abfrage ungemein:

SQL-Code:

CREATE PROCEDURE ERMITTLE_ALLEMWSTSP ( ABDATUM DATE)
RETURNS (
    ID_OUT INTEGER,
    MWSTSATZ_OUT INTEGER,
    ABDATUM_OUT DATE,
    MWSTWERT_OUT DECIMAL(15,2))
AS begin
  FOR SELECT ID,MWSTSATZ,ABDATUM, MWSTWERT FROM MWST WHERE :ABDATUM BETWEEN ABDATUM AND DATUM_BIS
  ORDER BY MWSTSATZ
  INTO :ID_OUT,:MWSTSATZ_OUT,:ABDATUM_OUT,:MWSTWERT_OUT
  DO
  SUSPEND;
end^

Damit wird die Änderung der MWSt-Sätze etwas erschwert, aber auch das geht in einer Stored Procedure, wenn sie auch mit Fallunterscheidungen arbeiten muss (neuer MWSt-Satz z.B. für Luxuswaren, Änderung des Geltungsbereichs). Wenn das gewünscht wird, kann ich meine Lösung mitliefern.

Übrigens scheint es mir einfacher zu sein, den MWSt-Satz als integer = Promille zu verwenden. Aber das ist sekundär (vielleicht gibt es aber auch MWSt-Sätze wie 7,25% - dann geht das natürlich nicht).

Jürgen

[Sir Rufo]

Warum eigentlich nicht so?

Oder kann FB keine verschachtelten SELECTS?

SQL-Code:

  SELECT ID,MWSTSATZ,ABDATUM, MWSTWERT FROM MWST AS m WHERE ABDATUM IN (SELECT MAX(ABDATUM) FROM mwst n WHERE n.MWSTSATZ=m.MWSTSATZ AND n.ABDATUM<=:ABDATUM)
  ORDER BY MWSTSATZ
  INTO :ID_OUT,:MWSTSATZ_OUT,:ABDATUM_OUT,:MWSTWERT_OUT
  DO

cu

Oliver

[Hansa]

Thx, Leute. Konnte erst jetzt wieder etwas genauer hier gucken und weiter machen. Die Alex Prozedur scheint zu gehen. Zumindest mit diesen Testdaten :

SQL-Code:

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


INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (1, 2, '1998-04-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (2, 1, '1980-01-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (3, 2, '2007-01-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (4, 0, '1900-01-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (5, 2, '1990-01-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (6, 1, '1970-01-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (7, 2, '1970-01-01');
INSERT INTO MWST (ID, MWSTSATZ, ABDATUM, MWSTWERT) VALUES (8, 2, '1980-01-01');

COMMIT WORK;

Das soll jetzt aber nicht heißen, daß die anderen nicht gehen ! Die einzige, die definitiv nicht richtig war, das ist die von mir. :mrgreen: