Then we can join these aggregations by the overlapping amount ranges within the same ID's:
select *
FROM (
select D.*,
A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount
from DIN D
CROSS APPLY (
Select SUM(D2.Amount) AllocatedAmount FROM DIN D2
where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI
) A
) D1
INNER JOIN (
select O.*, A.AllocatedAmount - O.AMOUNT PreviouslyAllocated, A.AllocatedAmount
from OUTE O
CROSS APPLY (
Select SUM(O2.Amount) AllocatedAmount FROM OUTE O2
where O2.ID = O.ID and O2.DATE_ <= O.Date_ and O2.LOGI <=O.LOGI
) A
) O1 ON O1.ID = D1.ID and
(
(O1.PreviouslyAllocated >= D1.PreviouslyAllocated and O1.PreviouslyAllocated < D1.AllocatedAmount)
OR
(O1.AllocatedAmount > D1.PreviouslyAllocated and O1.AllocatedAmount < D1.AllocatedAmount)
)
Next - we need to calculate OUTE amount allocated to each DIN record:
SELECT ...,
CASE
WHEN O1.AllocatedAmount > D1.AllocatedAmount THEN D1.AllocatedAmount - O1.PreviouslyAllocated
WHEN O1.PreviouslyAllocated < D1.PreviouslyAllocated THEN O1.AllocatedAmount - D1.PreviouslyAllocated
ELSE O1.AMOUNT
END OUT_AMOUNT, O1.Date_ OUT_DATE, O1.LOGI OUT_LOGI
Finally, we need to add records where DIN amounts are not covered with OUTE ones:
UNION ALL
SELECT OTA.ID, D1.LOGI, D1.DATE_,
D1.AMOUNT,
CASE
-- If TotalAmountPerID is withing the curent range - take only the remainder
WHEN D1.PreviouslyAllocated <= OTA.TotalAmountPerID THEN D1.AllocatedAmount - OTA.TotalAmountPerID
-- Otherwise - display the full DIN.Amount
ELSE D1.AMOUNT END OUT_AMOUNT, NULL OUT_DATE, NULL OUT_LOGI
FROM ( -- Total amounts per ID
Select ID, SUM(Amount) TotalAmountPerID
FROM OUTE
GROUP BY ID
) OTA
INNER JOIN (
select D.*, A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount
from DIN D
CROSS APPLY (
Select SUM(D2.Amount) AllocatedAmount FROM DIN D2
where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI
) A
) D1 ON OTA.ID = D1.ID and --
D1.AllocatedAmount > OTA.TotalAmountPerID
So the final query would look like this:
select D1.ID, D1.LOGI, D1.DATE_,
D1.AMOUNT,
CASE
WHEN O1.AllocatedAmount > D1.AllocatedAmount THEN D1.AllocatedAmount - O1.PreviouslyAllocated
WHEN O1.PreviouslyAllocated < D1.PreviouslyAllocated THEN O1.AllocatedAmount - D1.PreviouslyAllocated
ELSE O1.AMOUNT END OUT_AMOUNT, O1.Date_ OUT_DATE, O1.LOGI OUT_LOGI
FROM (
select D.*,
A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount
from DIN D
CROSS APPLY (
Select SUM(D2.Amount) AllocatedAmount FROM DIN D2
where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI
) A
) D1
INNER JOIN (
select O.*, A.AllocatedAmount - O.AMOUNT PreviouslyAllocated, A.AllocatedAmount
from OUTE O
CROSS APPLY (
Select SUM(O2.Amount) AllocatedAmount FROM OUTE O2
where O2.ID = O.ID and O2.DATE_ <= O.Date_ and O2.LOGI <=O.LOGI
) A
) O1 ON O1.ID = D1.ID and
(
(O1.PreviouslyAllocated >= D1.PreviouslyAllocated and O1.PreviouslyAllocated < D1.AllocatedAmount)
OR
(O1.AllocatedAmount > D1.PreviouslyAllocated and O1.AllocatedAmount < D1.AllocatedAmount)
)
UNION ALL
SELECT OTA.ID, D1.LOGI, D1.DATE_,
D1.AMOUNT,
CASE WHEN PreviouslyAllocated <= TotalAmountPerID THEN D1.AllocatedAmount - OTA.TotalAmountPerID
ELSE D1.AMOUNT END OUT_AMOUNT, NULL OUT_DATE, NULL OUT_LOGI
FROM (
Select ID, SUM(Amount) TotalAmountPerID
FROM OUTE
GROUP BY ID
) OTA
INNER JOIN (
select D.*, A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount
from DIN D
CROSS APPLY (
Select SUM(D2.Amount) AllocatedAmount FROM DIN D2
where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI
) A
) D1 ON OTA.ID = D1.ID and
D1.AllocatedAmount > OTA.TotalAmountPerID
--I added this only to make it easier to review the result. Not needed for the PROD code.
ORDER BY ID, DATE_, LOGI
Don't forget to replace * with explicit list of the columns in PROD version of the query.
_____________
Code for TallyGenerator