SQL Abfrage über Bewegungen

[renekr]

Hi @,
Ich habe folgendes vor:
Ich habe eine Tabelle wo die Artikel drin stehen mit Kennummer.
Dann eine Tabelle mit Bewegungen ( Abgang,Zugang ) von den Artikel mit Datum.

Nun will ich 2 Auswertungen machen.
1.: Eine Auswertung wo mir Alle Artikel anzeigt wo noch nie eine Abgangs -Bewegungs Buchung hatten.
2.: Im Prinzip das selbe aber auf Jahre bezogen,also wenn Artikel A in 2002 und 2004 einen Abgang hatten und der User wählt ab 2006 aus dann sollte der Artikel A angezeigt werden ,weil er ja ab 2006 keine Bewegungen mehr hatte.

Auswertung 1 habe ich hinbekommen.
Ich gehe über die Lagerliste mit Left outer join in die Bewegungen rein und dann mit Group by Kennummer where Bewegung = Abgang ist.
Das habe ich als View angelegt.
Dann habe ich alle Artikel die jemals eine Buchung hatten.

Dann habe ich eine 2. View gemacht die auch über die Lagerliste geht und mit Left outer join auf die 1. View und dann wo die Kennummer der 1. View is null ist.

Dann bekomme ich alle Artikel die niemals eine Bewegung hatten.

Nun stecke ich aber in der 2. Abfrage voll fest wo ich ab Jahr - Keine Bewegungen raus bekommen muss.
Hab schon vieles probiert mit Year(Buchungs_datum) <=2008 zb.:
aber dann bekomme ich ja nur unterschiedliche Ergebnisse angezeigt obwohl der Artikel schon eine Buchung hatte in dem Jahr.


Wäre über nen Denkanstoss sehr dankbar.

[rwachtel]

Zitat:

Zitat von renekr

[...] Nun stecke ich aber in der 2. Abfrage voll fest wo ich ab Jahr - Keine Bewegungen raus bekommen muss.
Hab schon vieles probiert mit Year(Buchungs_datum) <=2008 zb.:
aber dann bekomme ich ja nur unterschiedliche Ergebnisse angezeigt obwohl der Artikel schon eine Buchung hatte in dem Jahr. [...]

Kannst Du das nochmal verständlich formulieren?

Code (Tabellenstruktur und Deine Ansätze) wäre wahrscheinlich auch hilfreich.

[NormanNG]

Hi,

nur Pseucode, da keine Tabekllenstruktur bekannt:

SQL-Code:

select * from artikel a where not exists( select * from bewegungen b where b.artikel = a.artikel and bewegung = abgang and jahr=2008 )

[renekr]

Hi,
also hier meine 2 Tables

SQL-Code:

CREATE TABLE [dbo].[Lagerliste](
   [Kennummer] [int] IDENTITY(1,1) NOT NULL,
   [Datum] [smalldatetime] NULL,
   [Lager] [int] NULL,
   [Fachnummer] [nvarchar](6) NULL,
   [Bestellnummer] [nvarchar](255) NULL,
   [Warenbezeichnung] [nvarchar](255) NULL,
   [Bestand] [int] NULL,
   [Einheit] [nvarchar](255) NULL,
   [Meldebestand] [int] NULL,
   [Mindestbestand] [int] NULL,
   [Bestelltanzeige] [bit] NOT NULL,
   [Herstellernummer] [nvarchar](255) NULL,
   [Hersteller] [nvarchar](255) NULL,
   [Preis] [float] NULL,
   [Kreditor] [int] NULL,
   [Info] [nvarchar](255) NULL,
   [Inventar] [bit] NULL,
   [Zaehler] [nvarchar](50) NULL,
   [Bemerkung_Zaehler] [nvarchar](50) NULL,
   [gezaehlt] [bit] NULL,
   [Shuttle_Inventur] [bit] NULL,
   [Anleger] [nvarchar](50) NULL,
   [Show_Tab] [bit] NULL,
   [Ranking] [nvarchar](50) NULL,
   [Order_Time] [nvarchar](50) NULL,
   [Project] [nvarchar](50) NULL,
   [Inventar_number] [nvarchar](50) NULL,
   [Gewicht_gramm] [int] NULL
)

SQL-Code:

CREATE TABLE [dbo].[Lagerbewegung](
   [Bewegungsnummer] [int] IDENTITY(1,1) NOT NULL,
   [Materialkennummer] [int] NULL,
   [aktuelles_Datum] [smalldatetime] NULL,
   [Technischer_platz] [nvarchar](50) NULL,
   [Platznummer] [float] NULL,
   [Bewegung] [nvarchar](50) NULL,
   [Stueckzahl] [float] NULL,
   [Name] [nvarchar](50) NULL,
   [Bemerkung] [nvarchar](500) NULL,
   [Liste] [bit] NULL)

Dann die eine View wo mir alle Artikel zurückgibt mit Kennummer und Jahr

SQL-Code:

SELECT     TOP (100) PERCENT Materialkennummer AS Kennummer, Bewegung, YEAR(aktuelles_Datum) AS Aktuelles_Jahr
FROM         dbo.Lagerbewegung
WHERE     (Bewegung = N'Abgang')

Hier meine aktuelle Abfrage allerdings bekomme ich da noch doppelte Kennummer zurück.Da bin ich noch dran.

SQL-Code:

SELECT     TOP (100) PERCENT a.Kennummer, a.Warenbezeichnung, a.Bestand, a.Einheit, a.Preis, ROUND(a.Preis * a.Bestand, 0) AS PrSt, a.Lager, 
                      a.Fachnummer, a.Datum AS Anlege_Datum, dbo.VW_Lagerbewegung_Year.Aktuelles_Jahr
FROM         dbo.Lagerliste AS a INNER JOIN
                      dbo.VW_Lagerbewegung_Year ON a.Kennummer = dbo.VW_Lagerbewegung_Year.Kennummer
WHERE     (NOT EXISTS
                          (SELECT     Kennummer
                            FROM          dbo.VW_Lagerbewegung_Year AS b
                            WHERE      (Kennummer = a.Kennummer) AND (Aktuelles_Jahr IN ('2008', '2007'))))
ORDER BY Anlege_Datum, a.Fachnummer

[renekr]

So nun habe ich etwas optimiert.
Doppelten Einträge sind draussen.

Hier die View.

SQL-Code:

SELECT     TOP (100) PERCENT Materialkennummer AS Kennummer, YEAR(aktuelles_Datum) AS Aktuelles_Jahr
FROM         dbo.Lagerbewegung
WHERE     (Bewegung = N'Abgang')
GROUP BY Materialkennummer, YEAR(aktuelles_Datum)
ORDER BY Kennummer, Aktuelles_Jahr

Hier die Abfrage ansich:

SQL-Code:

SELECT     TOP (100) PERCENT a.Kennummer, MAX(a.Warenbezeichnung) AS Warenbezeichnung, MAX(a.Bestand) AS Bestand, MAX(a.Einheit) AS Einheit, 
                      MAX(a.Preis) AS Preis, MAX(ROUND(a.Preis * a.Bestand, 0)) AS PrSt, MAX(a.Lager) AS Lager, MAX(a.Fachnummer) AS Fachnummer, MAX(a.Datum) 
                      AS Anlege_Datum, MAX(dbo.VW_Lagerbewegung_Year.Aktuelles_Jahr) AS Neueste_Bewegung
FROM         dbo.Lagerliste AS a INNER JOIN
                      dbo.VW_Lagerbewegung_Year ON a.Kennummer = dbo.VW_Lagerbewegung_Year.Kennummer
WHERE     (NOT EXISTS
                          (SELECT     Kennummer
                            FROM          dbo.VW_Lagerbewegung_Year AS b
                            WHERE      (Kennummer = a.Kennummer) AND (Aktuelles_Jahr IN ('2008', '2007'))))
GROUP BY a.Kennummer
ORDER BY Anlege_Datum, Fachnummer

Nun muss ich es ein wenig testen ob auch wirklich alles soweit Konform ist.

[NormanNG]

Hi,

du kannst noch

SQL-Code:

(SELECT     Kennummer
                            FROM          dbo.VW_Lagerbewegung_Year AS b
                            WHERE      (Kennummer = a.Kennummer) AND (Aktuelles_Jahr IN ('2008', '2007'))))

durch

SQL-Code:

(SELECT   *
                            FROM          dbo.VW_Lagerbewegung_Year AS b
                            WHERE      (Kennummer = a.Kennummer) AND (Aktuelles_Jahr IN ('2008', '2007'))))

ersetzen. Dann kann der SQL-Server selbst die schnellste Möglichkeit (Index) auswählen.

[renekr]

So hab noch ein wenig Optimiert weil ich das Jahr vom Delphi raus in nerCombobox auswählen lasse.

SQL-Code:

(SELECT     Kennummer 
                            FROM          dbo.VW_Lagerbewegung_Year AS b 
                            WHERE      (Kennummer = a.Kennummer) AND (Aktuelles_Jahr >= '2006')))

Also alle wo in diesem Jahr oder in den nachfolgenden Jahren keine Bewegung hatten wird mir angezeigt.

Vielen Dank.

[shmia]

Also die Abfragen erscheinen mir noch wenig effizient zu sein. :gruebel:
Man muss doch bei den Lagerbewegungen anfangen und diese schon mit GROUP BY "eindampfen".
Dieses frühzeitige Reduzieren der Datenmenge spart dem Server viel Zeit und Resourcen (Abfrage 1):

SQL-Code:

SELECT Materialkennummer, COUNT(*) AS AnzahlBeweg
FROM   dbo.Lagerbewegung 
WHERE  Bewegung = N'Abgang'
GROUP BY Materialkennummer

Es gibt jetzt pro Materialkennummer nur einen Datensatz. :thumb:
Nächster Schritt wäre die Datumseinschränkung (Abfrage 2):

SQL-Code:

SELECT Materialkennummer, COUNT(*) AS AnzahlBeweg
FROM     dbo.Lagerbewegung 
WHERE  Bewegung = N'Abgang' AND aktuelles_Datum >= :MinDatum AND aktuelles_Datum <= :MaxDatum)
GROUP BY Materialkennummer

Es ist empfehlenswert, wie oben gezeigt den Zeitbereich über die Parameter :MinDatum und :MaxDatum auszuwählen, anstatt das feste Jahresschema anzuwenden.

Diese Abfrage wird jetzt mit der Lagerliste verjoint (Abfrage 3):

SQL-Code:

SELECT Lagerliste.*, DevTbl.* FROM
Lagerliste INNER JOIN (@@@) DevTbl ON Lagerliste.Kennummer=DevTbl.Materialkennummer
ORDER BY Lagerliste.Kennummer

Der Platzhalter @@@ steht für die Abfrage 2.
Ich habe mit Absicht die Abfrage 2 nicht in die Abfrage 3 einkopiert, damit man besser die Struktur sieht.

Der Unterschied zwischen der Abfrage in Beitrag #5 und meinem Vorschlag ist, dass bei meinem Vorschlag zuerst gruppiert und dann verjoint wird.
Im Beitrag #5 ist das Gruppieren nachgelagert und verursacht damit einige Probleme.
Der Ausdruck MAX(a.Warenbezeichnung) AS Warenbezeichnung ist eigentlich nur aus der Not geboren und macht ansonsten Sinn.

[renekr]

Hi,

Also ich habe den ganzen Tag getestet und ich muss die LAgerbewegungen nach kennummer und Jahr gruppiern,von ehemals 22000 datensätze ahbe ich dadurch ca. 6000 .
Aber ich brauche das Jahr weil es ja zurückliegend angezeigt werden soll.

Wenn ich nach Kennummer eindäpfe, Gruppiere habe ihc nur 1 Eintrag pro Kennummer und ev. davon das Letzte Abgangs Datum,dadurch weiß ich welcher Artikel als Letztes eine Bewegung hatte,aber nicht ob er in 2003 od. 2005 eine Bewegung hatte,also brauche ich Alle Kennummern und alle Jahre davon.Group by Kennummer ,Year(datum).

Ich habe diese abfrage ja schon eingedämt.
Ich habe leider nichts effizienteres hinbekommen. :wall: :wall:

Diese Gruppierung von dir verwende ich in der Abfrage (1) wo ich "nur" wissen will ob ein Artikel überhaupt eine Bewegung hatte um diese dann mit Is Null auszusortieren damit ich alle "Ohne" Bewegung bekomme von der Lagerliste.

Ich weiß ist etwas verzwickt aber Kollegen haben auch schon aufgegeben.

Bei Interesse kann ich auch gerne mal die Daten der Tabellen zur verfügung astellen um es realitäts nah zu testen.

In den LAgerbewegungen stehen oder kommt pro Bewegung " Abgang,Zugang" eines Artikel ein Datensatz hinzu,also nicht nur 1 eintrag pro Kennummer sondern eigentlich unendlich viel ,jenachdem wie oft ein Artikel Bewegt wurde.

mfg

[shmia]

Man kann die Abfrage 2 noch etwas aufbohren:

SQL-Code:

SELECT Materialkennummer, COUNT(*) AS AnzahlBeweg, Min(Year(aktuelles_Datum)) AS MinDatum, Max(Year(aktuelles_Datum)) AS MaxDatum
FROM     dbo.Lagerbewegung 
WHERE  Bewegung = N'Abgang' AND aktuelles_Datum >= :MinDatum AND aktuelles_Datum <= :MaxDatum) 
GROUP BY Materialkennummer

Das Gesamtergebnis sieht dann verkürzt so aus:

Code:

Kennummer   | ....... | AnzahlBeweg | MinDatum | MaxDatum
=========================================================
4711        |         | 42          | 2001     | 2005
4713        |         | 8           | 2002     | 2002

Man sieht daraus, dass der Artikel 4711 zwischen 2001 und 2005 genau 42 Mal abgegangen ist.
Die Punkte stehen für alle anderen Angaben zum Material.
Reicht dem Benutzer das als Info ?