[SQL Server] Laatste records ophalen tot specifiek aantal

[Tha-PC-Legend]

In een SQL Server database heb ik een tabel waar alle transacties in aanwezig zijn van alle artikelen. Dat wil zeggen: Van iedere transactie (voorraad in of uit) is een record aanwezig. Laten we zeggen dat ik de volgende kolommen heb:

Artikelcode

Magazijn

Magazijnlocatie

Aantal (+ of -)

Datum

Ik wil per artikel de transacties zien die zorgen voor de huidige voorraad (aantal) per magazijnlocatie. De huidige voorraad is dus de SUM van alle transacties per artikel/magazijn/magazijnlocatie. Dit zijn logischerwijs de laatste paar transacties die plaats hebben gevonden.

Voorbeeld: De volgende transacties hebben plaats gevonden voor Artikel XYZ:

Artikelcode	Magazijn	Magazijnlocatie	Aantal (+ of -)	Datum
XYZ	1	A	10	01-10-2021
XYZ	1	A	20	02-10-2021
XYZ	1	A	20	03-10-2021
XYZ	1	A	-12	04-10-2021
XYZ	1	A	10	05-10-2021

De SUM van deze transacties is 48. Ik zou nu in mijn query graag alle transacties zien met een positief aantal, tot dat het aantal 48 is bereikt of overschreden. Dus, de transacties van 05-10-2021, 03-10-2021 en 02-10-2021. Dit zorgt samen voor 50. Derhalve wil ik dat de transactie van 01-10-2021 niet getoond wordt. Immers is deze voorraad verbruikt op 04-10-2021 conform het FIFO principe.

Ik ben al de hele dag bezig om het voor elkaar te krijgen, maar tevergeefs. Alvast dank voor je hulp!

[urk_forever]

Wil je dit in een query doen, of kan je ook een stored procedure of een table valued function gebruiken? In een sp/tvf zou je eerst de positieve en negatieve records op kunnen halen en dan vervolgens met een loop/cursor de negatieve records eraf kunnen trekken totdat je alle records gehad hebt en dan geeft je alleen de records terug die een waarde > 0 hebben.

In een query wordt het denk ik lastiger.

[Paul!1987!]

code:

create table test(id int not null primary key, aantal int not null)

insert into test values(1,10),(2,20),(3,20),(4,-12),(5,10);

select  id, aantal
from (
    select id, aantal, (select sum(aantal) 
                  from test T2 
                  where T2.id >= T1.id) as acc 
    from test T1 
) as tmp 
where acc < (select sum(aantal) from test)
and aantal >0

[Tha-PC-Legend]

Paul!1987! schreef op donderdag 7 oktober 2021 @ 16:01:

code:

create table test(id int not null primary key, aantal int not null)

insert into test values(1,10),(2,20),(3,20),(4,-12),(5,10);

select  id, aantal
from (
    select id, aantal, (select sum(aantal) 
                  from test T2 
                  where T2.id >= T1.id) as acc 
    from test T1 
) as tmp 
where acc < (select sum(aantal) from test)
and aantal >0

Bedankt voor je reactie. Jouw voorbeeld werkt inderdaad perfect Echter, wanneer ik deze ombouw naar een query voor mijn database, dan gaat het helaas niet goed. En ik kan maar niet vinden waar het in zit.

In betreffende tabel in mijn database zitten transacties voor alle artikelen. Ik moet met wat meer variabelen rekening houden dan in mijn eerste post aangegeven. Ik zag de relevantie in eerste instantie niet, daarom heb ik het weg gelaten. Maar misschien zijn deze toch belangrijk. Het betreft de transtype en reknr variabelen. Hieronder de query zoals ik deze nu gebruik:

code:

select id, artikel, aantal, datum
from (
select id, artikel, aantal, datum, (select sum(aantal)
from test T2
where T2.id >= T1.id and artikel = 'XYZ' and transtype = 'N' and ltrim(rtrim(reknr)) = '3000') as acc
from test T1
where artikel = 'XYZ'
and transtype = 'N'
and ltrim(rtrim(reknr)) = '3000'
) as tmp
where acc < (select sum(aantal) from test where artikel = 'XYZ' and transtype = 'N' and ltrim(rtrim(reknr)) = '3000')
and aantal >0

De SUM van deze transacties is 70. Desalniettemin wordt de volgende output gegenereerd:



De som hiervan is 75. Misschien komt het omdat de laatste transactie in deze lijst 24 is? In werkelijkheid is de eerste transactie die van 01-12-2019 (regel 6), en moet deze dus weg worden gelaten, want deze is al uit voorraad. Een kleine aanpassing? Bedankt!

[Creepy]

Ja ho ff. Queries schrijven moeten we in principe zelf doen. Dus wat zou je zelf aanpassen om dat ene resultaat niet mee te krijgen? Ga er even iets verder mee aan de slag aub ipv om een aanpassing te vragen. Het is hier geen code afhaal balie.

[ Voor 29% gewijzigd door Creepy op 07-10-2021 16:47 ]

[Tha-PC-Legend]

Ik ben er vanavond opnieuw voor gaan zitten (na reeds uren en uren hier aan gespendeerd te hebben gisteren en vandaag; het is niet zo dat ik nog niets geprobeerd heb. Integendeel). Met de suggestie hierboven krijg ik het helaas niet aan de praat. Ik heb het over een andere boeg gegooid: Een running total mee laten lopen en alle records opgehaald waarbij de running total < de som van alle transacties (de voorraad).

Je mist dan nog één record: Het record waar slechts een deel nog van op voorraad ligt. Ik heb een UNION toegepast en dat ene record in die query opgehaald. Zodoende heb ik nu alle records die ik wil hebben. Misschien niet heel efficiënt, maar de performance van de query is goed en de data klopt.

Bedankt voor het meedenken, Paul

[hoi3344]

Ik zie trouwens dat je LTRIM en RTRIM gebruikt. sinds "SQL Server 2017 (14.x) and later" is er ook TRIM, die beiden in één heeft.
Verder vind ik de functionaliteit die je probeert te bereiken een beetje vreemd, maar zou via SQL zeker haalbaar moeten zijn. misschien met wat APPLY's of zelfs een recursieve CTE, om de 'running total' te bepalen. Het is echter vanuit zo'n stuk tekst lastig in te schatten wat de oplossing moet zijn, daarvoor zou ik zelf wat queries moeten schrijven.

[nescafe]

De ltrim(rtrim(reknr)) = '3000' maakt je query inefficiënt.

Beter is (veld is rechts uitgelijnd in een varchar(9)):

reknr = '     3000'

Of je pakt een query uit één van de voorloopschermen (CTRL-Q in Globe) en gebruikt die als basis. Bijv. join naar Items ON gbkmut.artcode = Items.ItemCode AND gbkmut.reknr = Items.GLAccountDistribution houdt rekening met artikelen met een andere voorraadrekening dan 3000.

Tevens mis je in je transtype nog je beginbalans en correcties, hoewel ik dat in de praktijk nog nooit ben tegengekomen.. transtype IN ('X', 'N', 'C', 'P')

[ Voor 16% gewijzigd door nescafe op 14-10-2021 17:38 ]

[Tha-PC-Legend]

hoi3344 schreef op donderdag 14 oktober 2021 @ 17:04:
Ik zie trouwens dat je LTRIM en RTRIM gebruikt. sinds "SQL Server 2017 (14.x) and later" is er ook TRIM, die beiden in één heeft.
Verder vind ik de functionaliteit die je probeert te bereiken een beetje vreemd, maar zou via SQL zeker haalbaar moeten zijn. misschien met wat APPLY's of zelfs een recursieve CTE, om de 'running total' te bepalen. Het is echter vanuit zo'n stuk tekst lastig in te schatten wat de oplossing moet zijn, daarvoor zou ik zelf wat queries moeten schrijven.

Bedankt voor de tip. Ik zal TRIM eens gaan proberen

[Tha-PC-Legend]

nescafe schreef op donderdag 14 oktober 2021 @ 17:10:
De ltrim(rtrim(reknr)) = '3000' maakt je query inefficiënt.

Beter is (veld is rechts uitgelijnd in een varchar(9)):

reknr = '     3000'

Of je pakt een query uit één van de voorloopschermen (CTRL-Q in Globe) en gebruikt die als basis. Bijv. join naar Items ON gbkmut.artcode = Items.ItemCode AND gbkmut.reknr = Items.GLAccountDistribution houdt rekening met artikelen met een andere voorraadrekening dan 3000.

Tevens mis je in je transtype nog je beginbalans en correcties, hoewel ik dat in de praktijk nog nooit ben tegengekomen.. transtype IN ('X', 'N', 'C', 'P')

In het eindresultaat join ik inderdaad op items.glaccountdistribution en items.itemcode en gebruik ik transtype in (‘N’, ‘C’, ‘P’, ‘X’). Ik wilde het voorbeeld zo simpel mogelijk houden

De voorloopschermen bieden niet echt het gewenste overzicht. Dat had ik reeds geprobeerd. Het ‘Ouderdom voorraad’ overzicht komt het meeste in de buurt. Die query heb ik gevangen middels de Profiler, maar bleek uiteindelijk niet bruikbaar.

Anyway, de eigen query werkt naar wens en doet zijn werk! Bedankt voor je feedback

[Skinny]

Zelf gebruik ik in dit soort situaties vaak PARTITION BY icm OVER().

Op een (versimpelde) voorraad mutatie tabel :



Kun je dan alle relevantie mutaties (voor product en lokatie) selecteren met een positief aantal als mutatie :

code:

SELECT ProductID, OrganizationUnitID, Quantity, CreationTime
From ProductLedger 
WHERE ProductID = 41693
AND OrganizationUnitID = 48
AND Quantity > 0

Vervolgens kun je aan die result zet een 'running total' toevoegen :

code:

SELECT ProductID, OrganizationUnitID, Quantity, CreationTime, SUM (Quantity) OVER (PARTITION BY ProductID, OrganizationUnitID ORDER BY CreationTime) AS RunningTotal
From ProductLedger 
WHERE ProductID = 41693
AND OrganizationUnitID = 48
AND Quantity > 0

Hiermee krijg je het volgende resultaat :



Nu ga je daar nog de limiet aan toevoegen tot waar je wil zoeken. Dit moet helaas met een subquery omdat PARTITION BY niet in een WHERE mag voorkomen.

code:

SELECT * FROM  (

SELECT ProductID, OrganizationUnitID, Quantity, CreationTime, SUM (Quantity) OVER (PARTITION BY ProductID, OrganizationUnitID ORDER BY CreationTime) AS RunningTotal
From ProductLedger 
WHERE ProductID = 41693
AND OrganizationUnitID = 48
AND Quantity > 0

) source
WHERE RunningTotal <=80
ORDER BY CreationTime

Hierboven selecteer ik dus alle mutaties met positieve aantallen tot en met een totaal van 80. Of je je datum ASC of DESC wil sorteren zal je zelf even moeten bepalen aan de hand van de business vraag. Ik denk dat je hiermee het wel kan vertalen naar jouw situatie. Succes!


Meer info over PARTITON BY : https://codingsight.com/c...-by-clause-in-sql-server/