Wiązanie dwóch tabel z uwzględnieniem ilości t-sql

Witam.
Mam ciekawy problem do rozwiązania w t-sql, mianowicie:
Mam dwie tabele, z których każda zawiera pola: indeks, data, ilość.

Tabela 1:

Tabela 2:

Chodzi mi o napisanie takiego zapytania, które poszczególne pozycje tabeli 1 rozchoduje mi ilościowo tabela 2 powiązana poprzez pole Index. Algorytm ma być taki:

1. Biorę pierwszą pozycję wg daty z tabeli 1.

2. Pobieram pierwszą pozycję wg daty z tabeli 1, która ma to samo pole Index i ilość jest większa od zera.

3. Od pozycji z tabeli 1 odejmujemy ilość z pozycji z tabeli 2 ale tak aby nie zejść poniżej zera i wpisujemy do nowego pola Id pozycji z tabeli 2.

4. Pole ilość z pozycji tabeli 2 pomniejszamy o ilość odjęta w tabeli 1.

5. Sprawdzamy czy pozycja z tabeli 1 ma ilość większą od zera. Jeżeli tak to wracamy do punktu 2. Jeżeli nie to bierzemy następny wiersz z tabeli 1 i wracamy do punktu 2.

   W wyniku mam otrzymać tabelę 1 (może być powiększona o nowe wiersze) rozchodowaną przez tabelę 2.
   Czy ktoś ma może pomysł jak podejść do tematu.
   Zaznaczam, że nie interesują mnie kursory ani pętle.
   Musi to być na jednym, może 2-3 zapytaniach. Będę operował na dużym zbiorze danych i chodzi mi o wysoką wydajność. Zapytanie musie działać szybko. Mówimy tu o zbiorze w tabeli 1 sięgającej kilkudziesięciu tysięcy pozycji.

   Z góry dziękuję za wszelkie podpowiedzi.

... ja tylko w kwestii formalnej: tak - pętle, kursory - to nie jest dobry pomysł na szybkość. Ale rozbicie logiczne algorytmu z użyciem tabel hashowych - już tak.

1. Czy można założyć, że rozchody (tabela 2) nigdy nie występują przed przychodami (tabela 1)?
   Mam na myśli sytuację:
   Dzień pierwszy - przychód 10
   Dzień drugi - rozchód 9
   Dzień trzeci - rozchód 4
   Dzień czwarty - przychód 20
   Czy taka sytuacja jest zabezpieczona? Jeśli nie, to jak miałby wyglądać wynik zapytania?
   Teraz się dopatrzyłem, że dokładnie taki przypadek masz w danych przykładowych. Dla index=2 przychody wynoszą 90, a rozchody 130.

Mógłbyś wypisać, jakiego wyniku oczekujesz dla danych przykładowych.

1. Czy tych danych potrzebujesz do cyklicznego (lub jednorazowego) uzupełnienia tabeli, czy za każdym razem będziesz sobie liczył wszystkie stany magazynowe?

2. kilkadziesiąt tysięcy rekordów to nie jest duży zbiór danych

Fac napisał(a):

1. Czy można założyć, że rozchody (tabela 2) nigdy nie występują przed przychodami (tabela 1)?
   Mam na myśli sytuację:
   Dzień pierwszy - przychód 10
   Dzień drugi - rozchód 9
   Dzień trzeci - rozchód 4
   Dzień czwarty - przychód 20

Czy taka sytuacja jest zabezpieczona? Jeśli nie, to jak miałby wyglądać wynik zapytania?

1. Czy tych danych potrzebujesz do cyklicznego (lub jednorazowego) uzupełnienia tabeli, czy za każdym razem będziesz sobie liczył wszystkie stany magazynowe?

2. kilkadziesiąt tysięcy rekordów to nie jest duży zbiór danych

Ad.1.
Niestety nie można założyć że przychody będą zawsze przed rozchodami. Dlatego muszę zestawić rozchody z przychodami sortując po dacie każdego z nich, tzn. numeruję przychody wg dat i numeruję rozchody wg dat i według tych numerów zestawiam ze sobą.
Ad.2
Te zapytania będą uruchamiane cyklicznie, na żądanie. Nie jest to jednorazowa akcja. To zapytanie muszę zapakować do procedury składowanej, wywoływanej na żądanie użytkownika.
Ad.3.
Tak, jeżeli mamy porządną maszynę na której stoi sql i nie zależy nam na czasie wykonania. Jednak ja muszę to zastosować w aplikacji, w której wydajność jest podstawą a aplikacja pracuje często na słabych maszynach. Dlatego chcę aby to zapytanie wykonywało się jak najkrócej, a by nie blokowało pracy bazy.

Udało mi się rozpisać ten algorytm na pętle, w której wykonuję za każdym razem dwa zapytania. Wszystko fajnie tylko dla 65000 pozycji w tabeli rozchodów czas wykonania to około 19 sekund (a testuję to na mocnej maszynie). Muszę zejść do 1 sekundy. Może ktoś ma pomysł jak przekuć to na jedno zapytanie?
Kod który zastosowałem:

	CREATE TABLE #przeliczDokPoInw_zejscia (towarId int null, wzLp int null, iloscZdjeta decimal(20,4) null, dostawaLp int null, dokMagPozId int null, wymiarId int null, lp int null)
	DECLARE @lp int = 1

	WHILE exists(SELECT * FROM #przeliczDokPoInw_wz WHERE lp=@lp)
		AND exists(SELECT * FROM #przeliczDokPoInw_dostawy WHERE ilosc>0)
	BEGIN
		INSERT INTO #przeliczDokPoInw_zejscia
		(towarId, wzLp, iloscZdjeta, dostawaLp, dokMagPozId, wymiarId, lp)
		SELECT towarId, wzLp, iloscZdjeta, dostawaLp, dokMagPozId, wymiarId, @lp
		FROM (
			SELECT wz.towarId, wz.lp [wzLp]
			, CASE WHEN ABS(wz.ilosc)>=SUM(CASE WHEN ABS(wz.ilosc)<=d.ilosc THEN ABS(wz.ilosc) ELSE d.ilosc-ABS(wz.ilosc) END) OVER (PARTITION BY wz.towarId ORDER BY d.lp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
				THEN SUM(CASE WHEN ABS(wz.ilosc)<=d.ilosc THEN ABS(wz.ilosc) ELSE d.ilosc-ABS(wz.ilosc) END) OVER (PARTITION BY wz.towarId ORDER BY d.lp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
				ELSE 0
				END [iloscZdjeta]
			, CASE WHEN ABS(wz.ilosc)>=SUM(CASE WHEN ABS(wz.ilosc)<=d.ilosc THEN ABS(wz.ilosc) ELSE d.ilosc-ABS(wz.ilosc) END) OVER (PARTITION BY wz.towarId ORDER BY d.lp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
				THEN d.lp
				ELSE 0
				END [dostawaLp]
			, wz.dokMagPozId, d.wymiarId
			FROM #przeliczDokPoInw_wz as wz
			INNER JOIN #przeliczDokPoInw_dostawy as d ON d.towarId=wz.towarId AND d.ilosc>0
			WHERE wz.lp=@lp
		) as p
		WHERE dostawaLp>0

		UPDATE #przeliczDokPoInw_dostawy SET
		ilosc = d.ilosc - z.iloscZdjeta
		FROM #przeliczDokPoInw_zejscia as z
		INNER JOIN #przeliczDokPoInw_dostawy as d ON d.towarId=z.towarId AND d.lp=z.dostawaLp
		WHERE z.lp=@lp
		
		SET @lp = @lp + 1
	END
	

No to jeszcze powiedz, co ma się wydarzyć, jeśli rozchodów jest więcej, niż przychodów (jak w podanych przez ciebie danych dla INDEX=2).
A najlepiej, to wypisz, co mamy Ci wypluć jako wynik dla podanego przez Ciebie zestawu danych.

Fac napisał(a):

No to jeszcze powiedz, co ma się wydarzyć, jeśli rozchodów jest więcej, niż przychodów (jak w podanych przez ciebie danych dla INDEX=2).
A najlepiej, to wypisz, co mamy Ci wypluć jako wynik dla podanego przez Ciebie zestawu danych.

Jeżeli rozchodów jest więcej niż przychodów to pomijamy w wyniku te przychody, których nie ma czym rozchodować.
W wyniku interesują mnie kolumny dokMagPozId, wymiarId, iloscZdjeta ztabeli #przeliczDokPoInw_zejscia.
Należy pamiętać jednak o tym, że wiersze z rozchodów mogą się rozmnożyć, gdy jeden rozchód będzie rozchodowywał więcej niż jeden przychód.

WojtexProgramista napisał(a):

Fac napisał(a):

No to jeszcze powiedz, co ma się wydarzyć, jeśli rozchodów jest więcej, niż przychodów (jak w podanych przez ciebie danych dla INDEX=2).
A najlepiej, to wypisz, co mamy Ci wypluć jako wynik dla podanego przez Ciebie zestawu danych.

Jeżeli rozchodów jest więcej niż przychodów to pomijamy w wyniku te przychody, których nie ma czym rozchodować.
W wyniku interesują mnie kolumny dokMagPozId, wymiarId, iloscZdjeta ztabeli #przeliczDokPoInw_zejscia.
Należy pamiętać jednak o tym, że wiersze z rozchodów mogą się rozmnożyć, gdy jeden rozchód będzie rozchodowywał więcej niż jeden przychód.

Proszę, PROSZĘ!
Rozpisz oczekiwany wynik dla danych podanych przez Ciebie w pierwszym wpisie wątku.
Bo nazwy dokMagPozId, wymiarId nie mają nic wspólnego z opisanym przez Ciebie na początku problemem :)

Myślę, że najrozsądniejszym rozwiązaniem jest rozbicie poszczególnych dokumentów (zarówno PZ, jak i WZ) na poszczególne sztuki i potem skojarzenie tego po sztukach.
Tu masz początek zapytania. Dalej kombinuj (pozostało zgurpowanie co i jak)

/*
create table wz
(
id int, 
indeks int not null,
data_wz date not null,
ilosc int not null
)
;

create table pz
(
id int, 
indeks int not null,
data_pz date not null,
ilosc int not null
)
;

insert into wz
values
(1, 1, '2024-01-01', 5),
(2, 1, '2024-01-02', 4),
(3, 1, '2024-01-03', 6),
(4, 2, '2024-01-01', 50),
(5, 2, '2024-01-02', 40),
(6, 3, '2024-01-03', 3)
;

insert into pz
values
(1, 1, '2024-01-11', 3),
(2, 1, '2024-01-12', 10),
(3, 2, '2024-01-11', 100),
(4, 2, '2024-01-12', 30),
(5, 3, '2024-01-13', 30)
;
*/
with dok_wz as
(
select 
	*, 
	sum(ilosc)over(partition by indeks order by id)-ilosc pop_wz, 
	sum(ilosc)over(partition by indeks order by id) ilosc_wz_nar 
from wz 
), dok_pz as
(
select 
	*, 
	sum(ilosc)over(partition by indeks order by id)-ilosc pop_pz, 
	sum(ilosc)over(partition by indeks order by id) ilosc_pz_nar 
from pz 
)
, lp as
(
select row_number()over(order by object_id) lp from sys.all_objects
)
, wz_rozbite as
(
select * from dok_wz join lp on lp.lp between pop_wz+1 and ilosc_wz_nar
)
, pz_rozbite as
(
select * from dok_pz join lp on lp.lp between pop_pz+1 and ilosc_pz_nar
)
select * from wz_rozbite w join pz_rozbite p on p.indeks=w.indeks and w.lp=p.lp

Fac napisał(a):

WojtexProgramista napisał(a):

Fac napisał(a):

No to jeszcze powiedz, co ma się wydarzyć, jeśli rozchodów jest więcej, niż przychodów (jak w podanych przez ciebie danych dla INDEX=2).
A najlepiej, to wypisz, co mamy Ci wypluć jako wynik dla podanego przez Ciebie zestawu danych.

Jeżeli rozchodów jest więcej niż przychodów to pomijamy w wyniku te przychody, których nie ma czym rozchodować.
W wyniku interesują mnie kolumny dokMagPozId, wymiarId, iloscZdjeta ztabeli #przeliczDokPoInw_zejscia.
Należy pamiętać jednak o tym, że wiersze z rozchodów mogą się rozmnożyć, gdy jeden rozchód będzie rozchodowywał więcej niż jeden przychód.

Proszę, PROSZĘ!
Rozpisz oczekiwany wynik dla danych podanych przez Ciebie w pierwszym wpisie wątku.
Bo nazwy dokMagPozId, wymiarId nie mają nic wspólnego z opisanym przez Ciebie na początku problemem :)

W wyniku chciałbym otrzymać Tabele 1 z wypełnionymi polami IdaTabela2, którą wiążą rekordy tabeli 1 z tabelą 2.

Marcin.Miga napisał(a):

Myślę, że najrozsądniejszym rozwiązaniem jest rozbicie poszczególnych dokumentów (zarówno PZ, jak i WZ) na poszczególne sztuki i potem skojarzenie tego po sztukach.
Tu masz początek zapytania. Dalej kombinuj (pozostało zgurpowanie co i jak)

Dziękuję za próbę ale w moim przypadku to się nie sprawdzi.
Przede wszystkim wiązanie: between pop_pz+1 and ilosc_pz_nar zakłada, że ilości są całkowite a są rzeczywiste, więc to założenie nie rozwiąże problemu.
Pozdrawiam.

Definicja tabel i danych (posłużyłem się nazewnictwem kolumn z pierwszego postu, ale tabele nazwałem bardziej po ludzku - i tak będziesz musiał to sobie dostosować i tak:

create table dbo.przychody (id int identity(1,1), [index] int null, [data] date null, ilosc int null)
create table dbo.rozchody (id int identity(1,1), [index] int null, [data] date null, ilosc int null)

INSERT INTO dbo.rozchody ([index],[data],ilosc)
VALUES 
 (1,'20240101',5)
,(1,'20240102',4)
,(1,'20240103',6)
,(2,'20240101',50)
,(2,'20240102',40)
,(3,'20240103',3)

INSERT INTO dbo.przychody ([index],[data],ilosc)
VALUES 
 (1,'20240111',3)
,(1,'20240112',10)
,(2,'20240111',100)
,(2,'20240112',30)
,(3,'20240113',30)

Zapytanie:

with ctep as (
	SELECT przychody.*
		, sum(ilosc) over(partition by [index] order by [data],id) sumaprz
		, ISNULL(sum(ilosc) over(partition by [index] order by [data],id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) popsumaprz
	FROM przychody --where [index]=1
),
cter as (
	SELECT rozchody.*
		, sum(ilosc) over(partition by [index] order by [data],id) sumaroz
		, ISNULL(sum(ilosc) over(partition by [index] order by [data],id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) popsumaroz
	FROM rozchody --where [index]=1
)

select roz.*,p.id AS ID_PRZYCHODU
from cter r
join rozchody roz on roz.id=r.id
left join ctep p on r.[index]=p.[index] 
	and popsumaroz < sumaprz
	and popsumaprz < sumaroz

Fac napisał(a):

Definicja tabel i danych (posłużyłem się nazewnictwem kolumn z pierwszego postu, ale tabele nazwałem bardziej po ludzku - i tak będziesz musiał to sobie dostosować i tak:

O coś takiego mi właśnie chodziło.
Muszę je tylko dopracować gdyż wychodzi mi coś za dużo wierszy w wyniku ale to jest czego szukałem.
Dzięki za pomoc.