SQL Query String für Lagerbestand gesucht.

Question

Hallo Gemeinde,

bin noch nicht so ein Crack in Sachen SQL Abfrage und hoffe hier auf Hilfe.

Habe eine Tabelle (dbo.OINM), in der alle Lagerbewegungen für alle Artikel festgehalten werden und folgende Spalten sind für mein Ergebnis wichtig.

ItemCode = Artikelnummer

DocDate = Datum der Buchung

InQty = Lager Zugangs Menge

OutQty = Lager Abgangs Menge

CalcPrice = Preis

Nun möchte ich als Abfragekriterium einen Stichtag wählen können. Zum Beispiel der 31.12.2009 und als Ergebnis

sollen nur die Lagerleichen ermittelt werden, bei denen nach diesem Stichtag keine Lagerbewegung mehr statt fand.

ItemCode | DocDate (LetzteBuchung)| Lagerbestand (aktueller Lagerbestand| Lagerwert (Preis für ein Teil ergibt sich aus dem letzten Eintrag bei CalcPrice)

P000123   01.10.09                                        50000                                    1000

P000345   06.08.08                                             20                                       100

Wäre nett eine Lösung zu bekommen.

Gruss

ToBo

 PS: Hab noch was vergessen. Zwischen, oder am ende der einzelnen Zu/Abgangsbuchungen kann es noch andere Buchungen

geben, die aber die Menge nicht verändern. Also diese Datensätze haben eine Nullmenge im Feld InQty und OutQty,

und beeinflussen die Gesamtmenge nicht. Denke das muss man für die Abfrage wissen. Den letzten Preis sollte man

vom letzten Buchungssatz des jeweiligen Artikels nehmen, der eine Bestandsänderung verursacht hat. Also für die Abrage

der Schläfer ist der letzte Buchungssatz wichtig, der einen Zu/Abgang hatte und NICHT, falls der letzte Buchungssatz eine

Nullmenge war.

 

 

 

Answer 1

Hi,

so ganz hab ichs zwar nicht verstanden, evtl. passt das hier aber doch für dich:

SELECT DISTINCT ItemCode
FROM  OINM
WHERE ItemCode NOT IN (
            SELECT DISTINCT ItemCode
            FROM  OINM
            WHERE DocDate > DATEADD( d, 1, '2009-12-31T00:00:00' )
            AND  (
                InQty IS NOT NULL OR
                OutQty IS NOT NULL
               )
            )

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Hallo Stefan,

Danke für die schnelle Antwort, doch leider ist das nicht so ganz was ich suche.

Das Ergebnis sollte wie oben beschrieben eine Berechnung sein, die wie folgt ausschaut und diese

Spalten hat.

ItemCode    |      Letzte Buchung am  |  Total Menge im Lager | Gesamtpreis der Totalmenge

 Diese Berechnung soll aber nur für Teile durchgeführt werden, bei denen kein Zu oder Abgang NACH

dem Stichtag erfolgte. Also alle Teile, bei denen nach dem Stichtag noch Lagerbewegungen vorhanden

sind bleiben unberücksihtigt. Das macht deine Abfrage ja schon gut :-)

Die Totalmenge muss man nicht unbeding berechnen, sondern könnte man auch aus einer anderen

Tabelle holen. Tabelle OITM, Spalte OnHand hat den aktuellen Lagerbestand. Mit dieser Menge und

dem eintrag CalcPrice aus der OINM sollte es doch möglich sein die Gesamtsumme zu ermitteln ?

Habe jetzt auch schon eine Weile versucht Dein Beispiel entsprechend anzupassen, doch mein

Wissen ist leider am Ende und ich komme mit der Syntax nicht klar. 

Weiterhin liefert Dein Ergebnis auch Teile, bei denen der Lagerbestand null ist. Im Ergebnis sollten aber

nur Teile berücksichtigt werden, bei denen auch ein Lagerbestand vorhanden ist. Also zum Beispiel

OnHand aus OITM > 0

 Und falls dies jetzt nicht zu unverschämt ist, wäre es nocht perfekt, wenn im Ergebnis der zum Artikel gehörende

Text stehen könnte. Das sind die Spalten ItemName und FrgnName aus der Tabelle OITM :-)))

Conclusion:

Im Prinzip ist Deine Abfrage schon fast Perfekt. Sie müsste jetzt noch mit der Spalte OnHand der Tabelle OITM verknüpft werden und nur Artikel anzeigen, bei der hier die Menge >0 ist. Dann diese Menge mit dem Wert aus OINM.CalcPrice multiplizieren und der Gesampreis ist da. Dazu noch das Datum der letzten Lagerbewegung (kann entweder InQty oder OutQty sein, je nachdem ob die letzte Bewegung ein Ab- oder Zugang war) und den Namen, sowie FrgnName aus OITM und es wäre genau das was ich brauche..

Noch was zum Schluss :-)

Deine Abfrage dauert jetzt schon ca. 3 Minuten, Ist das normal ?

Answer 3

Hi,

es wäre sinnvoll, wenn die beiden Tabellen mal als CREATE Skript hier posten könntest. Dann kann man sich das besser vorstellen und auch schauen, dass man das Statement aufbauen kann.

Die Abfrage sollte natürlich nicht so lange laufen, das kommt aber auch auf die Anzahl der Datensätze in deiner Tabelle an. Wie viele Datensätze gibts denn da? Evtl. muss man da etwas umstricken. Bei meinen 10 Testdatensätzen gibts das Problem so naturgemäß nicht^^

BTW: Von welcher SQL Server Version genau reden wir eigentlich? Da jede neue Version auch neue Features hat, die man ggfs. nutzen kann, wäre das wichtig.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Hi Stefan,

würde ungern die Tabellen hier öffentlich reinstellen. Die genaue Zeit bei der obigen Abfrage ist bis jetzt 5 Minuten und 12 Sekunden. Die

Tabelle OINM hat zur Zeit 70533 Datensätze.

Vielleicht schreibe ich Dir noch mal genau die Spalten um die es geht.

Tabelle OINM (Lagerjournal)

ItemCode - Artikelnummer

InQty - Zugangs-Menge (Stückzahl des Lagerzugangs)

Out Qty - Abgangs-Menge (Stückzahl des Lagerabgangs)

CalcPrice - Preis für ein Stück

DocDate - Datum der Buchung

Und noch eine Menge mehr Spalten, die aber für das Ergebnis nicht gebraucht werden.

Tabelle OITM (Item Master Data)

ItemCode - Artikelnummer (gleich der Spalte ItemCode in OINM)

ItemName - Name des Artikels

FrgnName - Name des Artikel in Foreign Language

OnHand - Aktueller Lagerbestand

Und noch ca. 240 andere Spalten, die aber für das Ergebnis nicht gebraucht werden.

Deine Obige Abfrage ist schon nicht schlecht. Sie liefert alle Artikelnummern, bei denen seit dem Stichtag KEINE Lagerbuchung mehr

satt fand. Allerdings auch Artikel mit Nullmengen. Das Ergebnis der Abfrage soll eine Liste von Artikeln liefern, bei denen

1. seit dem Stichtag keine Bewegung mehr statt fand.

2. Nur Artikel auflisten, deren Bestand (OITM OnHand) > NULL ist

3. Die Namen des Artikels (OITM ItemName und OITM FrgnName) enthalten

4. Den aktuellen Lagerbestsan (OITM OnHand) enthalten

5. Den Gesamtbuchungswert des aktuellen Lagerbestandes (OITM OnHand x OINM CalcPrice) enthalten

6. Das Datum der letzten Lagerbewegung (OINM InQty ODER OINM OutQty, je nachdem ob die letzte Bewegung ein Zu oder Abgang war)

Beste Grüße aus Shanghai.

ToBo

 PS: SQL Server 2005. Auf meinem Laptop nur die Express Version. Für die richtige DB die Vollversion.

Answer 5

use tempdb
go
create table Lagerjournal_OINM	-- Lagerjournal
(
	ItemCode int,			-- Artikelnummer
	InQty int,				-- Zugangs-Menge (Stückzahl des Lagerzugangs)
	OutQty int,				-- Abgangs-Menge (Stückzahl des Lagerabgangs)
	CalcPrice decimal(9,4),	-- Preis für ein Stück
	DocDate datetime		-- Datum der Buchung
)
go
create table Items_OITM			-- (Item Master Data)
(
	ItemCode int,			-- Artikelnummer (gleich der Spalte ItemCode in OINM)
	ItemName nvarchar(255),	-- Name des Artikels
	FrgnName int,			-- Name des Artikel in Foreign Language
	OnHand	int				-- Aktueller Lagerbestand
)
go

declare @stichtag datetime = '2009-12-31'

select
	i.ItemCode,
	i.ItemName,
	i.FrgnName,
	j.DocDate as [Datum der letzten Lagerbewegung],
	i.OnHand,
	j.CalcPrice,
	(i.OnHand * j.CalcPrice) as [Gesamtbuchungswert des aktuellen Lagerbestandes]
from
	Items_OITM i inner join Lagerjournal_OINM j
	on i.ItemCode = j.ItemCode
where 
	-- seit dem Stichtag keine Bewegung mehr statt fand.
	not exists(select * from Lagerjournal_OINM where ItemCode = i.ItemCode and DocDate > @stichtag
	-- keine Zwischenbuchungen
	and (InQty is not null or OutQty is not null))
	-- Preis für ein Teil ergibt sich aus dem letzten Eintrag bei CalcPrice
	and j.DocDate = (select MAX(DocDate) from Lagerjournal_OINM where ItemCode = i.ItemCode and (InQty is not null or OutQty is not null))

	-- Nur Artikel auflisten, deren Bestand (OITM OnHand) > NULL ist
	and i.OnHand > 0
go

drop table Lagerjournal_OINM,Items_OITM
go

Answer 6

Genial. Hab das jetzt so wie nachfolgend getestet und es braucht keine Sekunde bis das Ergebnis da steht.

select

i.ItemCode,

i.ItemName,

i.FrgnName,

j.DocDate as [Datum der letzten Lagerbewegung],

i.OnHand,

j.CalcPrice,

(i.OnHand * j.CalcPrice) as [Gesamtbuchungswert des aktuellen Lagerbestandes]

from

OITM i inner join OINM j

on i.ItemCode = j.ItemCode

where 

-- seit dem Stichtag keine Bewegung mehr statt fand.

not exists(select * from OINM where ItemCode = i.ItemCode and DocDate > '2009-12-31T00:00:00'

-- keine Zwischenbuchungen

and (InQty is not null or OutQty is not null))

-- Preis fr ein Teil ergibt sich aus dem letzten Eintrag bei CalcPrice

and j.DocDate = (select MAX(DocDate) from OINM where ItemCode = i.ItemCode and (InQty is not null or OutQty is not null)) -- Nur Artikel auflisten, deren Bestand (OITM OnHand) > NULL ist

and i.OnHand > 0

Echt Prima und vielen Dank

Beste Grüße aus Shanghai

ToBo

 PS: Hab keine Ahnung warum das hier jetzt so klein und in Blau angezeigt wird ???

 

Answer 7

Sorry, da war ich zu schnell mit meiner Antwort.

Habe gerade das Ergebnis nach ItemCode sortiert und siehe da, die meisten Artikel sind zwei mal, manche drei mal, einer sogar fünf mal mit dem gleichem Ergebnis in der Liste.

Habe mal beide Ergebnisse (Yury und Stefan) nach Excel exportiert und mit nem VBA script die doppelten Artikel gelöscht.

Bei Stefan gab es keine doppelten Artikel. Jeder ist nur ein mal vorhanden.

Bei der Liste von Stefan habe ich 982 Artikel,

bei der Liste von Yury habe ich 791 Artikel.

 

 

Answer 8

Hi ToBo,

falls Du nur eindeutige Datenzeilen benötigst, schreibe nach dem select das Schlüsselwort distinct.

Answer 9

Hab ich gemacht. Hat die Anzahl von 1322 auf 952 reduziert, aber das Problem nicht

ganz gelöst. Es gibt immer noch doppelte Artikel im Ergebnis. Die Richtige Anzahl wäre

791.

Habe den Distinct Befehl hinter jedem Select getestet und es bewirkt nur etwas bei dem ersten

ganz oben. Ich weiss nicht was passiert, aber es gibt immer noch doppelte Artikel in der Liste.

 

Answer 10

Hi ToBo,

ich vermute, dass entweder an einem Tag mehrere Buchungen durchgeführt wurden oder mehrere Zeilen mit dem gleichen ItemCode in der Tabelle OITM gespeichert sind. Deswegen sind die Bedingungen der Abfrage nicht ausreichend, um auf das eindeutige Ergebnis zu kommen.

Zusätzliche Bedingungen wären z.B. Zeitstempel incl. Stunde, Minute, Sekunde oder die fortlaufende ID der Tabelle OINM zu berücksichtigen.

Answer 11

Halo Yury,

Doppelte Artikel in der OITM ist absolut undmöglich. Mehrere Buchungen an einem Tag sind natürlich vorhanden und

bei den doppelten, dreifachen Artikeln im Ergebnis auch vorhanden. Einen Zeitstempel gibt es in der OINM nicht,

allerdings eine Spalte TransNum, die die fortlaufende ID der OINM enthält.

 

Answer 12

Wird in der Spalte TransNum eine fortlaufende Nummer gespeichert, könnte man die zusätzliche Bedingung verwenden:

 

and TransNum = (select MAX(TransNum) from OINM where ItemCode = i.ItemCode and DocDate = j.DocDate and (InQty is not null or OutQty is not null))

 

Answer 13

Perfekt. Ergebnis = 791 Artikel

So brauch ich das. Mann, wie kann man das so lernen ?

 

Answer 14

Bekommt man den kleinen Finger.....

Dieses Problem ist zwar gelöst, aber um die Sache zu verfeinern ist mir noch was eingefallen. Ist vielleicht etwas unverschämt, aber hatte schon des öfteren das Problem, dass ich Infos nicht nur aus zwei, sondern aus Drei Tabellen brauche. Jetzt hab ich noch eine Zeile in den String eingefügt.

select

i.ItemCode,

i.ItemName, 

i.FrgnName, 

i

.U_Item_Group2,

j.DocDate as [Datum der letzten Lagerbewegung],

 

i.OnHand,

j.CalcPrice,

(i.OnHand * j.CalcPrice) as [Gesamtbuchungswert des aktuellen Lagerbestandes]

Diese U_Item_Group2 steht in der OITM. Aber nur als Nummer. 01 bis xx. Jetzt gibt es eine Tabelle UFD1, wo der Klartextname dafür hinterlegt ist. Am liebsten sollte jetzt dieser Klarname im Ergebnis auftauchen statt des Codes. Bisher habe ich den mit diesem Query ermittelt.

select Descr from UFD1 where

TableID = 'OITM'

and FieldID = '0'

and FldValue = 'hier der Code (i.U_Item_Group2) des Klarnamens aus der OITM'

Wie könnte ich nun statt des Codes für i.U_Item_Group2 den entsprechenden Text aus der UFD1 im Ergebnis anzeigen lassen ?

Falls das noch gehen würde, wäre es Prima und ich hab heute ne Menge gelernt :-)

Answer 15

select
	u.Descr
from 
	OITM i inner join UFD1 u
	on i.U_Item_Group2 = u.FldValue
where
	u.TableID = 'OITM'
	and u.FieldID = '0'

Answer 16

Ja, das verstehe ich schon. Aber wo / wie bau ich das in Deine obige Abfrage ein ?

 

 

Answer 17

Hallo ToBo,

folgende Links sollten helfen den "Stoff" besser zu verstehen:

SELECT (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Join Fundamentals

 

Ich hoffe, dass die Abfrage Dir weiterhelfen kann:

 

use tempdb
go
create table Lagerjournal_OINM	-- Lagerjournal
(
	TransNum int identity,
	ItemCode int,			-- Artikelnummer
	InQty int,				-- Zugangs-Menge (Stückzahl des Lagerzugangs)
	OutQty int,				-- Abgangs-Menge (Stückzahl des Lagerabgangs)
	CalcPrice decimal(9,4),	-- Preis für ein Stück
	DocDate datetime		-- Datum der Buchung
)
go
create table Items_OITM			-- (Item Master Data)
(
	ItemCode int,			-- Artikelnummer (gleich der Spalte ItemCode in OINM)
	ItemName nvarchar(255),	-- Name des Artikels
	FrgnName int,			-- Name des Artikel in Foreign Language
	OnHand	int,				-- Aktueller Lagerbestand
	U_Item_Group2 int
)
go
create table UFD1 
(
	FldValue int,
	TableID sysname,
	FieldID sysname,
	Descr nvarchar(255)
)
go
insert into UFD1 values
(1,'OITM','0','DescrDescrDescr')
go
insert into Items_OITM values
(1,1,1,15,1),(2,2,2,2,1)
go
insert into Lagerjournal_OINM values
(1,10,null,11,'2008.01.11'),
(1,5,null,22,'2008.01.22'),
(1,null,null,33,'2008.01.22')
go
insert into Lagerjournal_OINM values
(2,7,null,44,'2008.01.11'),
(2,null,null,55,'2008.01.22'),
(2,null,5,66,'2010.01.22')
go
declare @stichtag datetime = '2009-12-31'

;with t1 as
(
	select
		i.ItemCode,
		i.ItemName,
		i.FrgnName,
		j.DocDate as [Datum der letzten Lagerbewegung],
		i.OnHand,
		j.CalcPrice,
		(i.OnHand * j.CalcPrice) as [Gesamtbuchungswert des aktuellen Lagerbestandes],
		j.TransNum,
		u.Descr
	from
		Items_OITM i inner join UFD1 u
		on i.U_Item_Group2 = u.FldValue 
		and u.TableID = 'OITM'
		and u.FieldID = '0'
		inner join Lagerjournal_OINM j
		on i.ItemCode = j.ItemCode
	where 
		-- seit dem Stichtag keine Bewegung mehr statt fand.
		not exists(select * 
					from Lagerjournal_OINM 
					where ItemCode = i.ItemCode and DocDate > @stichtag
		-- keine Zwischenbuchungen
		and (j.InQty is not null or j.OutQty is not null))
		-- Nur Artikel auflisten, deren Bestand (OITM OnHand) > NULL ist
		and i.OnHand > 0
		and (j.InQty is not null or j.OutQty is not null)
)
select *
from t1 as t2
where
	---- Preis für ein Teil ergibt sich aus dem letzten Eintrag bei CalcPrice
	TransNum = (select MAX(TransNum) from t1 where ItemCode = t2.ItemCode)
go
--select * from Lagerjournal_OINM
drop table Lagerjournal_OINM,Items_OITM, UFD1
go

Answer 18

Werde die Links mal ganz in Ruhe und langsam studieren.

Leider gibts ein Problem. Artikel, bei denen der Wert U_Item_Group2 = NULL ist, tauchen im Ergebnis nicht auf.

 

Answer 19

ersetze

Items_OITM i inner join UFD1 u

durch

Items_OITM i left join UFD1 u

Answer 20

Da gibts wohl noch ganz schön viel für mich zum studieren.....

Klappt jetzt alles super und ich gehe schlafen. Ist schon 2:45 in China.

Vielen Dank noch mal und bis zum nächsten Problem :-)

ToBo