[MSSQL] Onmogelijke query?

[Verwijderd]

Heb weer eens een in mijn ogen onmogelijke query.

Heb 2 tabellen:

Tabel1: ProductVoorraad
ProductID Voorraad
10____________2
12____________0
27____________9


Tabel2: Verkopen
Facnr Productid Aantal
23______10____________1
23______27____________3
24______10____________1
24______12____________3
25______10____________5

Nu wil ik in Tabel2 een extra kolom, met daarin "beschikbare voorraad". Deze beschikbare voorraad, moet op deze manier bepaald worden.

Tabel1 doorlopen, gesort op productid. Voor ieder productid tabel2 doorlopen om te kijken of er wat van verkocht is. Zo ja, een select uitvoeren op Tabel2 gesort op facnr. Dan een update doen van de "beschikbare voorraad" in deze Tabel 2, totdat de beschikbare voorraad op is. Je doorloopt de laatste select van Tabel 2 dus net zolang totdat je beschikbare voorraad van dat product (uit Tabel1) op 0 staat. In dit geval heeft de 1e factuur de hoogste prioriteit bij het toekennen van de voorraad, en gaat de voorraad dus als eerste naar de oudste factuur toe.

In ASP lukt me dit wel, maar dit duurt echt oneindig lang , dus wil het liefst een stored procedure. Voor mijn gevoel gaat dit echter niet werken in een stored procedure.

Tabel2 moet in dit geval dan worden:

Tabel2: Verkopen
Facnr Productid Aantal BeschikbareVoorraad
23______10____________1____________1
23______27____________3____________3
24______10____________1____________1
24______12____________3____________0
25______10____________5____________0


Hoop dat het een beetje duidelijk is. Is vrij lastig om uit te leggen

[Verwijderd]

Het verhaal is idd een beetje ingewikkeld, maar laat ik je eerst deze ( vrij belangrijke ) vraag stellen :
Waarom wil je deze gegeven hard in de database vastleggen ? Het lijkt me dat soort vooraadtoewijzigingen ( want dat is het toch ) bij iedere voorraadontvangst en verkoop weer veranderen en je dus je gegevens weer opnieuw zou moeten bepalen.

[stp_4]

Ik denk ( ondanks dat je verhaal wat onduidelijk is ) dat je gebruik moet maken van een FETCH misschien in combinatie met een temp table

[ Voor 19% gewijzigd door stp_4 op 05-03-2005 15:34 ]

[Verwijderd]

Verwijderd schreef op zaterdag 05 maart 2005 @ 15:32:
Het verhaal is idd een beetje ingewikkeld, maar laat ik je eerst deze ( vrij belangrijke ) vraag stellen :
Waarom wil je deze gegeven hard in de database vastleggen ? Het lijkt me dat soort vooraadtoewijzigingen ( want dat is het toch ) bij iedere voorraadontvangst en verkoop weer veranderen en je dus je gegevens weer opnieuw zou moeten bepalen.

Dit om vrij vlot van alle openstaande orders te kunnen zien wat ervan op voorraad is. De bedoelde stored procedure moet dus om de paar minuten aangeropen worden, maar dat is allemaal niet zo'n probleem.

[djluc]

Ik vind het maar een beetje vaag. Waarom heb je niet gewoon 1 tabel transacties. Dan kan je heel eenvoudig alles uitrekenen. Je kan dan een tabel maken als:
productid-leveranciersid-klantid-aantal

Voor een inkoopactie van 100 producten:
22211 - 3232 - 0 - 100

Nu verkopen we er 50:
22211 - 0 - 665 - (-50)

[Verwijderd]

Dit is qua business logica wel erg raar. Als je een order aanmaakt, moet tegelijkertijd je voorraad worden aangepast. Als je niet voldoende voorraad hebt, maak je een back order aan. Je zult dus je orderlines moeten opsplitsen tussen een reserved for order tabel en een back order tabel. Iedere keer als je voorraden binnen krijgt, moet je alle back orders eerst opvullen en omzetten naar reserved for order. Eventueel kan je ook in de ordel line twee kolommen maken, 1 voor het aantal gereserveerd en 1 voor het aantal in back order, afhankelijk van hoeveel gegevens je bij wil gaan houden. Dit representeert veel meer de realiteit van de situatie. Order gegevens in de order tabel, voorraad gegevens in de voorraad tabel, en geen vage mix.

Als je dit eenmaal aangepast hebt, is wat je wil bereiken een eitje.

Edit:
Sterker nog, je zult je voorraad wel direkt moeten aanpassen, want anders kan het voorkomen dat het voor een klant / verkoper lijkt alsof er zat voorraad is en dus een snelle levering verwacht / belooft, en dat bij het uitleveren uiteindelijk blijkt dat de voorraad dubbel verkocht is, en dus 1 van de klanten kan gaan wachten.

[ Voor 23% gewijzigd door Verwijderd op 05-03-2005 16:18 ]

[EfBe]

Dit kan niet in een table column, want die is statisch (kan wel computed column maken met functie maar jouw column betrekt zn waarde uit andere rows in de table, dus het is semantisch niet correct deze in dezelfde table te stoppen). Je moet een view maken, met daarin de extra column, of beter de facturen op volgorde. Maar veel sneller dan het nu gaat zal het niet gaan. Waarom sla je de beschikbare voorraad niet op wanneer je het record in table 2 aanmaakt, want op dat tijdstip weet je de dan beschikbare voorraad.

[Verwijderd]

Probleem hierbij is echter dat orders continu aangepast worden, en er ook weer uitgehaald kunnen worden.

Een tabel met de beschikbare voorraad per artikel leek mij het beste. Zodra je dan iets binnen krijgt, wordt de voorraad op dat artikel bijgeboekt, wordt er een order verzonden, wordt de voorraad afgeboekt.

Het verhaal waar ik het over heb, is puur voor de weergave, en wat berekeningen. De echte voorraad wordt dus bijgehouden in de producten tabel, bij het product. Ook is er uiteraard een voorraad mutatie tabel e.d., maar dat staat hier allemaal los van.

Voor de weergave wilde ik graag de extra kolom in de tabel met daarin de op DAT moment beschikbare voorraad voor die factuurregel. Als de order daarboven dan geannuleerd wordt, gaat deze voorraad automatisch naar de volgende order met dit product erin.

Denk dat ik het dan toch maar met 2 loopjes in ASP moet gaan doen......, want een stored procedure wordt zoals ik al verwacht had zo te horen niets

[Verwijderd]

EfBe schreef op zaterdag 05 maart 2005 @ 16:13:
Dit kan niet in een table column, want die is statisch (kan wel computed column maken met functie maar jouw column betrekt zn waarde uit andere rows in de table, dus het is semantisch niet correct deze in dezelfde table te stoppen). Je moet een view maken, met daarin de extra column, of beter de facturen op volgorde. Maar veel sneller dan het nu gaat zal het niet gaan. Waarom sla je de beschikbare voorraad niet op wanneer je het record in table 2 aanmaakt, want op dat tijdstip weet je de dan beschikbare voorraad.

De extra kolom moet ook statisch zijn, deze moet alleen "ververst" worden om de paar minuten.

[Jaspertje]

Verwijderd schreef op zaterdag 05 maart 2005 @ 16:24:
[...]
De extra kolom moet ook statisch zijn, deze moet alleen "ververst" worden om de paar minuten.

Daar kan je toch gewoon een trigger voor gebruiken.. ON Upate of On Insert

[Verwijderd]

Ja, het gaat alleen om wat er getriggerd moet worden. Dat moet een stored procedure worden (denk ik) die deze extra kolom van de goede informatie voorziet. (zie helemaal bovenaan voor welke informatie er in deze kolom moet komen)

[whoami]

EfBe schreef op zaterdag 05 maart 2005 @ 16:13:
Dit kan niet in een table column, want die is statisch (kan wel computed column maken met functie maar jouw column betrekt zn waarde uit andere rows in de table, dus het is semantisch niet correct deze in dezelfde table te stoppen).

Ik dacht zelfs dat het helemaal niet mogelijk is om een 'computed column' te gaan maken, als die column gegevens nodig heeft uit een andere table.

Met een trigger zou het wel kunnen.

[EfBe]

whoami schreef op zaterdag 05 maart 2005 @ 16:36:
[...]

Ik dacht zelfs dat het helemaal niet mogelijk is om een 'computed column' te gaan maken, als die column gegevens nodig heeft uit een andere table.
Met een trigger zou het wel kunnen.

Je kunt een user defined function maken en die kan natuurlijk vanalles doen en die gebruiken als value provider.

Wat TS echter wil is een set en een derived set mergen in dezelfde table, waarbij de derived set gebaseerd is op de set waar deze mee merged. Dat kan niet, je hebt dan een nieuwe container nodig, zodat de derived set zn juiste context krijgt, immers wanneer je ze bij elkaar gaat proppen is de context van table 2 zoek. Het zou inderdaad beter zijn wanneer TS de orders opsplitst in backorder en reserved for order lines.

[ Voor 35% gewijzigd door EfBe op 05-03-2005 17:01 ]

[Verwijderd]

Op volgorde van wenselijkheid:

Optie 1:
Stored proc maken om je orders te inserten. Bij het inserten hiervan de voorraad bijwerken. Klopt ten alle tijde je voorrraad veld.

Optie 2:
Op basis van een trigger op je order tabel.

De stored proc die jij graag wilt lijkt me (samen met de ASP-oplossing) niet een prettige oplossing omdat je voorraad nooit klopt, behalve tijdens het runnen van je stored proc.

[Verwijderd]

Misschien even duidelijk om aan te geven. Een order is nog geen uitgeboekte order, maar een openstaande order die een bijvoorbeeld een tijdje open staat, om te wachten op andere onderdelen. Werken backorders is geen optie, vanwege verzendkosten e.d.

Voorraad klopt altijd, want wordt bijgehouden in de producten tabel. Komt er een product binnen, wordt dit bijgeboekt, gaat er een product de deur uit (met een order bijvoorbeeld) wordt dit afgeboekt (automatisch uiteraard). De voorraad waar we het over hebben is dus de werkelijke voorraad - gereserveerde voorraad. Dit is voor het betreffende product makkelijk te berekenen door de actuele voorraad (uit de producten tabel) te nemen, minus de openstaande orders (die nog niet uitgeboekt zijn).

Met mijn oplossing gebeurd er dus fysiek niets met de voorraad. Het enige wat je ermee kunt zien is welke voorraad er voor een bepaalde factuurregel gereserveerd ligt. Als er een openstaande order niet doorgaat, schuift dit dus door naar de volgende openstaande order. Bij het uitboeken van de order wordt er dan ook gekeken naar de voorraad in de producten tabel.

Ander voordeel van mijn methode is dat je op deze manier erg snel de orders die geheel op voorraad zijn eruit kunt filteren, je selecteerd gewoon de orders waarbij de gereserveerde voorraad gelijk is aan de bestelde hoeveelheid. Is er bij een order geen verschil in beide, is deze dus compleet op voorraad.

Als je in bovenstaand geval voor iedere orderregel moet gaan berekenen of er een gedeelte van de voorraad voor deze order bestemd is, ben je uren bezig als je wilt berekenen welke orders gereed zijn.

De voorraad wordt dus pas bijgewerkt bij het uitboeken van een order, maar daar zit het probleem niet. Het gaat PUUR om de weergave

[ Voor 5% gewijzigd door Verwijderd op 05-03-2005 18:18 ]

[jvdmeer]

Ik sluit me aan bij eerdere posters dat je eigenlijk een andere oplossing moet zoeken, maar toch nog even de query die toont wat jij wilt:

SQL:

SELECT factuur, a.productid, 
    CASE 
      WHEN aantal<voorraad-isnull((select sum(aantal) from tabel2 b where b.factuur<a.factuur and b.productid=a.productid),0) THEN aantal
      ELSE voorraad-isnull((select sum(aantal) from tabel2 b where b.factuur<a.factuur and b.productid=a.productid),0)
    END BeschikbareVoorraad
  FROM tabel2 a 
  JOIN tabel1 b on b.productid=a.productid
  ORDER BY factuur, a.productid