Query String für letzte Lagerbewegung

Question

Hallo zusammen,

ich brauche mal wieder Hilfe von den Meistern.

Eine Tabelle OINM hat Spalten für

ItemCode = Artikelnummer

DocDate = Buchungsdatum

InQty = Menge eingehend

OutQty = Menge abgehend

Ich brauche jetzt einen String, der mir für jeden Artikel zwei Daten. Wann und wieviel sind zuletzt Teile eingegangen und wann und wieviel sind zuletzt Teile abgegangen.

ItemCode  LastInDate LastInQty  LastOutDate LastOutQty

Entweder steht eine Menge in der Spalte InQty, oder in OutQty. Es kommt niemals das in einer Buchung jeweils in IN und OUT etwas steht.

Hoffe ich konnte mich erklären.

Answer 1

Hallo!

Verstehe ich nicht: Eine Tabelle OINM hat eine Spalte

Oder doch 4 Spalten?

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 2

Sorry,

hat jeweils eine Spalte für.....

Answer 3

Dann geht es z. B. so:

Set Nocount on;
Declare @OINM as Table(ItemCode int, DocDate date, InQty int, OutQty int);

Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(1, '20160101', 10, 0);
Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(1, '20160112', 11, 0);
Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(1, '20160101', 0, 5);
Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(1, '20160102', 0, 1);



Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(2, '20160201', 100, 0);
Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(2, '20160212', 101, 0);
Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(2, '20160201', 0, 50);
Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(2, '20160202', 0, 10);

Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(3, '20160201', 300, 0);


Insert into @OINM (ItemCode, DocDate, InQty, OutQty) values(4, '20160201', 0, 400);

With Vorberechung_i as
(
Select ItemCode, DocDate, InQty, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocDate Desc) as dat_i 
from @OINM
where InQty > 0),
Vorberechung_o as
(
Select ItemCode, DocDate, OutQty, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocDate Desc) as dat_o 
from @OINM
where OutQty > 0)
Select coalesce(i.ItemCode, o.ItemCode) as ItemCode, i.DocDate as LastInDate , i.InQty as LastInQty, o.DocDate as LastOutDate, o.OutQty as LastOutQty
from Vorberechung_i i
Full Join Vorberechung_o o
on i.ItemCode = o.ItemCode
where coalesce(dat_i, 1) = 1
and coalesce(dat_o, 1) = 1
;

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 4

Whow, das sieht aber sehr kompliziert aus.

Leider habe ich noch etwas vergessen. Es gibt noch eine Spalte Warehouse. Weil es verschiedene Läger gibt. Lager 00 bis Lager 13.

Mit obiger Abfrage wird das leider nicht berücksichtigt. Also es kann sein dass ein Artikel Buchungen in mehreren Lagern hat. Kann, muss aber nicht.

Ich habe da einfach mal das Warehouse dazu genommen. Jetzt muss ich das für jedes Lager einzeln machen.

With Vorberechung_i as
(
Select ItemCode, DocDate, InQty, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocDate Desc) as dat_i 
from OINM
where InQty > 0 and warehouse = '01'),

Vorberechung_o as
(
Select ItemCode, DocDate, OutQty, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocDate Desc) as dat_o 
from OINM
where OutQty > 0 and warehouse = '01')

Answer 5

Letzte Lagerbewegung pro Lager, oder willst Du das Lager nur mit ausgeben?

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 6

Ja, letzte IN und Out Lagerbewegung pro Lager.

Answer 7

Set Nocount on;
Declare @OINM as Table(ItemCode int, DocDate date, InQty int, OutQty int, Lager char(2));

Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '01', '20160101', 10, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '01', '20160112', 11, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '01', '20160101', 0, 5);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '01', '20160102', 0, 1);

Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '11', '20161101', 10, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '11', '20161112', 11, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '11', '20161101', 0, 5);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(1, '11', '20161102', 0, 1);



Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(2, '02', '20160201', 100, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(2, '02', '20160212', 101, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(2, '02', '20160212', 101, 0);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(2, '02', '20160201', 0, 50);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(2, '02', '20160202', 0, 10);

Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(3, '03', '20160301', 300, 0);


Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty) values(4, '04', '20160401', 0, 400);

With Vorberechung_i as
(
Select ItemCode, Lager, DocDate, InQty, ROW_NUMBER() OVER(PARTITION BY ItemCode, Lager ORDER BY DocDate Desc) as dat_i 
from @OINM
where InQty > 0),
Vorberechung_o as
(
Select ItemCode, Lager, DocDate, OutQty, ROW_NUMBER() OVER(PARTITION BY ItemCode, Lager ORDER BY DocDate Desc) as dat_o 
from @OINM
where OutQty > 0)
Select coalesce(i.ItemCode, o.ItemCode) as ItemCode, coalesce(i.Lager, o.Lager) as Lager, i.DocDate as LastInDate , i.InQty as LastInQty, o.DocDate as LastOutDate, o.OutQty as LastOutQty
from Vorberechung_i i
Full Join Vorberechung_o o
on i.ItemCode = o.ItemCode
and i.Lager = o.Lager
where coalesce(dat_i, 1) = 1
and coalesce(dat_o, 1) = 1
Order by ItemCode, Lager;

Zur Erklärung: Zentraler Punkt sind die beiden CTEs mit der Window Function ROW_NUMBER.

Führe mal eine dieser CTEs separat aus, dann siehst Du, dass für jeden ItemCode und Lager die Lagerbewegungen durchnummeriert werden.

Im äußeren Select verwendest Du dann nur noch die Sätze mit der Nummer 1.

Damit In und Out kommen, gibt es einen Full Join wieder über ItemCode und Lager.

Damit dort keine Nullwerte kommen, wird coalesce verwendet.

Weil nach dem Full Join auch bei der Nummerierung ein NULL stehen kann, werden dort die Werte auf 1 geändert, damit die Zeile nicht komplett verschwindet wenn eine der beiden Lagerbewegungen fehlt.

HTH!

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 8

Also ehrlich gesagt dachte ich am Anfang das wäre ganz einfach zu lösen... Nun sieht das aber schon ziemlich nach Kopfschmalz aus.

Leider habe ich noch ein Problem festgestellt. Das Ergebnis zeigt mir die letzten Lagerbewegung von allen Teilen an. Ganz egal ob der aktuelle Lagerbestand 0 ist oder nicht. Da es darum geht das Alter der Teile im Lager zu bestimmen fehlt noch etwas. Zum einen die aktuelle Menge des Artikels und zum anderen sollten auch nur Artikel im Ergebnis auftauchen deren Lagerbestand >0 ist.

Die Bestandsmenge des jeweiligen Artikels steht in der Tabelle OITW in der Spalte OnHand. In der OITW gibt es für jeden Artikel pro Lager einen Eintrag mit den Menge. Derzeit haben wir 13 Lager, also 13 Einträge pro Artikel wo der aktuelle Lagerbestand für das jeweilige Lager enthalten ist.

Die Tabelle OITW sieht so aus

ItemCode Warehouse OnHand

Es sollten bei der ersten Abfrage nur Artikel im Ergebnis erscheinen, die in dem jeweiligen Lager eine Menge >0 haben. Von Teilen deren Bestand 0 ist, brauch ich keine Auswertung.

Answer 9

Falls Buchungen am selben Tag kommen, habt ihr ja sicherlich noch die Uhrzeit oder ähnliches zur Unterscheidung, oder?

Set Nocount on;
Declare @OINM as Table(ItemCode int, DocDate date, InQty int, OutQty int, Lager char(2), OnHand int);

Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '01', '20160101', 10,	0, 10);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '01', '20160101',  0, 5,  5);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '01', '20160102',  0, 1,  10);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '01', '20160112', 11, 0, 36);
														
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '11', '20161101', 10, 0, 20);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '11', '20161101', 0,  5, 15);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '11', '20161102', 0,  1, 14);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(1, '11', '20161113', 11, 0, 25);

Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(2, '02', '20160201', 100,  0, 100);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(2, '02', '20160212', 101,  0, 201);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(2, '02', '20160212', 101,  0, 302);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(2, '02', '20160213',   0, 50, 252);
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(2, '02', '20160214',   0, 10, 242);
														 
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, OnHand) values(3, '03', '20160301', 300, 0, 300);
														  
-- Vorher 1000
Insert into @OINM (ItemCode, Lager, DocDate, InQty, OutQty, Onhand) values(4, '04', '20160401', 0, 400, 600);

-- Letzter Lagerbestand
With Lagerbestand as 
(
Select ItemCode, OnHand, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocDate Desc) as rn
from @OINM
)
Select ItemCode, OnHand
from Lagerbestand
where rn = 1
;



With Lagerbestand as 
(
Select ItemCode, OnHand, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocDate Desc) as rn
from @OINM
), 
Vorberechung_i as
(
Select O.ItemCode, O.Lager, O.DocDate, O.InQty, ROW_NUMBER() OVER(PARTITION BY O.ItemCode, O.Lager ORDER BY O.DocDate Desc) as dat_i, L.OnHand 
from @OINM O
Inner Join Lagerbestand L
on O.ItemCode = L.ItemCode
and L.rn = 1
where O.InQty > 0
and L.OnHand > 0
),
Vorberechung_o as
(
Select O.ItemCode, O.Lager, O.DocDate, O.OutQty, ROW_NUMBER() OVER(PARTITION BY O.ItemCode, O.Lager ORDER BY O.DocDate Desc) as dat_o, L.OnHand 
from @OINM O
Inner Join Lagerbestand L
on O.ItemCode = L.ItemCode
and L.rn = 1
where OutQty > 0
and L.OnHand > 0)
Select coalesce(i.ItemCode, o.ItemCode) as ItemCode, coalesce(i.Lager, o.Lager) as Lager, i.DocDate as LastInDate , i.InQty as LastInQty, o.DocDate as LastOutDate, o.OutQty as LastOutQty, coalesce(I.OnHand, O.OnHand) as OnHand
from Vorberechung_i i
Full Join Vorberechung_o o
on i.ItemCode = o.ItemCode
and i.Lager = o.Lager
where coalesce(dat_i, 1) = 1
and coalesce(dat_o, 1) = 1
Order by ItemCode, Lager;

Wir ergänzen also eine neue CTE für den Lagerbestand, joinen diese und nehmen nur Artikel mit Lagerbestand > 0.

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 10

Leider funktioniert das nicht. Weil der aktuelle Lagerbestand, wie oben von mir geschrieben nicht in der gleichen Tabelle wie die Buchungen OINM steht.

Der Lagerbestand steht in der Tabelle OITW in Spalte OnHand.

Die Tabelle OITW sieht so aus

ItemCode Warehouse OnHand

In der OITW gibt es keine Spalte DocDate

Da gibt es für jeden Artikel und jedes Lager einen Eintrag. Derzeit sind es 13 Lager, also 13 Einträge für jeden Artikel.

With Vorberechung_i as
(
Select ItemCode, Warehouse, DocDate, InQty, ROW_NUMBER() OVER(PARTITION BY ItemCode, Warehouse ORDER BY DocDate Desc) as dat_i 
from OINM
where InQty > 0),
Vorberechung_o as
(
Select ItemCode, Warehouse, DocDate, OutQty, ROW_NUMBER() OVER(PARTITION BY ItemCode, Warehouse ORDER BY DocDate Desc) as dat_o 
from OINM
where OutQty > 0)
Select coalesce(i.ItemCode, o.ItemCode) as ItemCode, coalesce(i.Warehouse, o.Warehouse) as Lager, i.DocDate as LastInDate , i.InQty as LastInQty, o.DocDate as LastOutDate, o.OutQty as LastOutQty, x.OnHand
from Vorberechung_i i
Full Join Vorberechung_o o
on i.ItemCode = o.ItemCode
and i.Warehouse = o.Warehouse

left Join OITW x
on i.Warehouse = x.WhsCode and i.ItemCode = x.ItemCode


where coalesce(dat_i, 1) = 1
and coalesce(dat_o, 1) = 1
and x.OnHand >0


Order by ItemCode, Lager;

Ich habe das mal so geändert. Wenn ich mir das Ergebnis anschaue sieht das gut aus. Ich habe einen left Join auf die OITW Tabelle gesetzt um mir den aktuellen Lagerbestand zu holen und ganz unten noch nach OnHand > 0 gefiltert.

Ist das richtig so ?

Answer 11

Das sieht doch erst mal gut aus. In den CTEs kannst Du halt eine Menge an Logik reinpacken und das abschließende SQL dann etwas simpler gestalten.

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 12

Big Problem :-)

Ich habe bei mir Windows 7 und da läuft die Abfrage problemlos. Habe es auf mehreren Win7 Rechnern getestet und alles gut. Leider haben wir auch noch ein Paar XP Kisten. Da läuft es nicht. Es kommt immer der Fehler "incorrect Syntax near With....."

Also ich meine in Excel VBA. 

Es liegt nicht an den Parametern. Alle anderen Abfragen gehen, nur diese nicht.

Was nun ?

Answer 13

Diese Syntax wird ab SQL Server 2008 (oder auch 2005) unterstützt. Bedingung ist außerdem, dass das Statement davor mit einem Semikolon abgeschlossen wurde.

WITH common_table_expression

HTH!

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 14

Wie schon geschrieben. Der Query ist einem Excel VBA code eingebunden. Funktioniert ohne Probleme, solange es auf Win7 läuft. Auf XP kommt die Fehlermeldung. Es wird immer auf den gleichen SQL Server zugegriffen. Es kann nur am OS des Clients liegen.

strConnection = "Provider=SQLOLEDB;Data Source=" & strServer & _
   ";" & "Initial Catalog=" & strDatabase & ";User ID=" & _
   strUserName & ";" & "Password=" & strPassword & ";"

  Set rs = CreateObject("ADODB.Recordset")

'***********************************************************************************************************************************************
    strSQL = "With Vorberechung_i as (Select ItemCode, Warehouse, DocDate, InQty, ROW_NUMBER() OVER(PARTITION BY ItemCode, Warehouse ORDER BY DocDate Desc) as dat_i from OINM " & _
             "where InQty > 0), Vorberechung_o as (Select ItemCode, Warehouse, DocDate, OutQty, ROW_NUMBER() OVER(PARTITION BY ItemCode, Warehouse ORDER BY DocDate Desc) as dat_o from OINM " & _
             "where OutQty > 0) Select coalesce(i.ItemCode, o.ItemCode) as ItemCode, p.ItemName, p.FrgnName, f.Descr as SalesGroup, coalesce(i.Warehouse, o.Warehouse) as Lager, i.DocDate as LastInDate , i.InQty as LastInQty, o.DocDate as LastOutDate, o.OutQty as LastOutQty, datediff(day, i.DocDate, Getdate()) as StAging, x.OnHand, x.OnOrder, x.IsCommited, p.LstEvlPric, x.OnHand * p.LstEvlPric as Total " & _
             "from Vorberechung_i i Full Join Vorberechung_o o on i.ItemCode = o.ItemCode and i.Warehouse = o.Warehouse left Join OITW x on i.Warehouse = x.WhsCode and i.ItemCode = x.ItemCode inner Join OITM p on i.ItemCode = p.ItemCode " & _
             "left join UFD1 f on p.U_Item_Sales = f.FldValue and f.TableID = 'OITM' and f.FieldID = '15' where coalesce(dat_i, 1) = 1 and coalesce(dat_o, 1) = 1 and x.OnHand >0 Order by ItemCode, Lager; "
'***********************************************************************************************************************************************


 rs.Open strSQL, strConnection
   
   ActiveSheet.Range("A2").CopyFromRecordset rs
     
 rs.Close

Das ist der VBA Code. Funktioniert auf einer WIN7 Maschine unter Excel 2003, sowie Excel 2010. Auf einer XP Maschine kommt die Fehlermeldung "incorrect syntax near With"

Answer 15

XP ist ja auch Out-Of-Support. Bliebe noch alles in eine Prozedur oder View zu packen und diese dann zu verwenden.

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 16

In der VBA Routine sind noch 1000 andere Abragen und Reports. Diese funktionieren alle ohne Probleme. Lediglich alles mit "With" läuft nicht auf XP.

Ich denke mal es liegt am ODBC SQL Treiber. Mal sehen ob ich da ein update für XP bekomme.

Oder kann es am Net-Framework liegen ?

Ich habe derzeit aus anderen Gründen keine Chance die betroffenen PC auf WIN7 hoch zu rüsten.

Answer 17

Ich tippe auch eher auf den ODBC-Treiber.
Wie gesagt, zur Not sollte eine View als Krücke ausreichen.

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu