Hello
Do not blame me for disturbing you.
It does not give me the fact that the table structure changes a little.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID =
OBJECT_ID(N'DIN') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [dbo].[DIN]
;
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID =
OBJECT_ID(N'OUTE') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [dbo].[OUTE]
;
CREATE TABLE [dbo].[DIN]
(
[ID] [int] NOT NULL,
[LOGI] [int] NOT NULL,
[DATE_] DATETIME NOT NULL,
[AMOUNT] FLOAT NULL,
[OUT_AMOUNT] FLOAT NULL,
[OUT_DATE] DATETIME NULL,
[OUT_LOGI] [int] NULL,
primary key clustered (ID, Date_, LOGI)
)
;
INSERT INTO DIN
(ID,[LOGI],DATE_,AMOUNT)
SELECT '1','150',CONVERT(DATETIME,'05.01.2018',104),'2' UNION ALL
SELECT '1','160',CONVERT(DATETIME,'05.01.2018',104),'40' UNION ALL
SELECT '1','170',CONVERT(DATETIME,'06.01.2018',104),'10' UNION ALL
SELECT '1','180',CONVERT(DATETIME,'06.01.2018',104),'552'
;
CREATE TABLE [dbo].[OUTE]
(
[ID] [int] NOT NULL,
[LOGI] [int] NOT NULL,
[DATE_] DATETIME NOT NULL,
[AMOUNT] FLOAT NULL ,
PRIMARY KEY CLUSTERED (ID, Date_, LOGI)
)
;
INSERT INTO OUTE
(ID,LOGI,DATE_,AMOUNT)
SELECT '1','52',CONVERT(DATETIME,'05.01.2018',104),'182' UNION ALL
SELECT '1','53',CONVERT(DATETIME,'06.01.2018',104),'11'
;
Query
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
The outcome of the question
ID LOGI DATE_ AMOUNT OUT_AMOUNT OUT_DATE OUT_LOGI
1 150 2018-01-05 00:00:00.000 2 2 2018-01-05 00:00:00.000 52
1 180 2018-01-06 00:00:00.000 552 130 2018-01-05 00:00:00.000 52
1 180 2018-01-06 00:00:00.000 552 11 2018-01-06 00:00:00.000 53
1 180 2018-01-06 00:00:00.000 552 411 NULL NULL
I want to be
ID | LOGI | DATE_ | AMOUNT | OUT_AMOUNT | OUT_DATE | OUT_LOGI |
1 | 150 | 2018-01-05 00:00:00.000 | 2 | 2 | 2018-01-05 00:00:00.000 | 52 |
1 | 160 | 2018-01-05 00:00:00.000 | 40 | 40 | 2018-01-05 00:00:00.000 | 52 |
1 | 170 | 2018-01-06 00:00:00.000 | 10 | 10 | 2018-01-05 00:00:00.000 | 52 |
1 | 180 | 2018-01-06 00:00:00.000 | 552 | 130 | 2018-01-05 00:00:00.000 | 52 |
1 | 180 | 2018-01-06 00:00:00.000 | 552 | 11 | 2018-01-06 00:00:00.000 | 53 |
1 | 180 | 2018-01-06 00:00:00.000 | 552 | 411 | NULL | NULL |
Thank you