FIFO_new.sql

DECLARE @tblInp TABLE (
	[Code] NVARCHAR(128),
    [Date] DATETIME,
    [Quantity] INT
);

DECLARE @tblOut TABLE (
	[Code] NVARCHAR(128),
    [Date] DATETIME,
    [Quantity] INT
);

-- Dữ liệu mẫu
INSERT INTO @tblInp ([Code], [Date], [Quantity]) VALUES
('INP1','2024-04-06', 100),
('INP2','2024-04-22', 50),
('INP3','2024-05-11', 170),
('INP4','2024-11-30', 150);

INSERT INTO @tblOut ([Code], [Date], [Quantity]) VALUES
('OUT1','2024-04-29', 150),
('OUT2','2024-06-24', 160),
('OUT3','2024-12-16', 150);

WITH InputSorted AS (
    SELECT 
        CAST(ROW_NUMBER() OVER(ORDER BY Date, Code) AS INT) AS in_rn,
        *
    FROM @tblInp
),
OutputSorted AS (
    SELECT 
        CAST(ROW_NUMBER() OVER(ORDER BY Date, Code) AS INT) AS out_rn,
        *
    FROM @tblOut
),
FIFO_Calculation AS (
    -- Anchor member
    SELECT 
        1 AS RowNumber,
        in_rn,
        Quantity AS in_remaining,
        0 AS out_rn,
        0 AS out_remaining,
        0 AS assigned
    FROM InputSorted 
    WHERE in_rn = 1

    UNION ALL

    -- Recursive member
    SELECT
        rec.RowNumber,
        rec.in_rn,
        CASE WHEN rec.in_remaining > rec.out_remaining THEN rec.in_remaining - rec.out_remaining 
			ELSE 0 
		END in_remaining,

        rec.out_rn,
        CASE WHEN rec.out_remaining > rec.in_remaining THEN rec.out_remaining - rec.in_remaining ELSE 0 END out_remaining,
        
		CASE WHEN rec.in_remaining > rec.out_remaining THEN rec.out_remaining ELSE rec.in_remaining END assigned
    FROM (
        SELECT
            cur.RowNumber + 1 RowNumber,
            inp.in_rn,
            CASE WHEN cur.isNextInp = 1 THEN inp.Quantity ELSE cur.in_remaining END in_remaining,
            ot.out_rn,
            CASE WHEN cur.isNextOut = 1 THEN ot.Quantity ELSE cur.out_remaining END out_remaining
        FROM (
            SELECT
                RowNumber,

				CASE WHEN in_remaining = 0 THEN 1 
					ELSE 0
				END isNextInp,

                CASE WHEN in_remaining = 0 THEN in_rn+1 
					ELSE in_rn 
				END in_rn,

				in_remaining,	
                
				CASE WHEN out_remaining = 0 THEN 1 
					ELSE 0 
				END isNextOut,
                
				CASE 
					WHEN out_remaining = 0 THEN out_rn+1 
					ELSE out_rn 
				END out_rn,

				out_remaining
            FROM (
                SELECT ROW_NUMBER() OVER(ORDER BY RowNumber DESC) LastRowNumber,
                    *
                FROM FIFO_Calculation
            ) w     
            WHERE w.LastRowNumber = 1
        ) cur
        JOIN InputSorted inp ON cur.in_rn = inp.in_rn
        JOIN OutputSorted ot ON cur.out_rn = ot.out_rn
    ) rec
)
SELECT 
    inp.[Date] AS InputDate,
	inp.[Code] AS InputCode,
    ott.[Date] AS OutputDate,
	ott.[Code] AS OutputCode,
    v.assigned,
    v.in_remaining,
	v.out_remaining
FROM FIFO_Calculation v
JOIN InputSorted inp ON v.in_rn = inp.in_rn
JOIN OutputSorted ott ON v.out_rn = ott.out_rn
ORDER BY v.RowNumber;