This is indeed an interesting problem. I have spent some time over it, read some articles, and learned a few things.
Here is a working example for your scenario.
DECLARE @Agent TABLE
(
NAME VARCHAR(100),
MONEY INT
);
DECLARE @EXPORT TABLE
(
SNO INT,
[DATE] DATETIME,
NAME VARCHAR(100),
MONEY INT
);
DECLARE @PAYMENT TABLE
(
SNO INT,
[DATE] DATETIME,
NAME VARCHAR(100),
MONEY INT
);
INSERT INTO @Agent
SELECT 'Ahmed', 600;
INSERT INTO @EXPORT
SELECT 1, '25-JAN-2023', 'Ahmed', 700
UNION ALL
SELECT 2, '30-JUN-2023', 'Khalid', 800
UNION ALL
SELECT 3, '10-JUL-2023', 'Ahmed', 200
UNION ALL
SELECT 4, '30-NOV-2023', 'Ahmed', 100
;
INSERT INTO @PAYMENT
SELECT 1, '25-FEB-2023', 'Ahmed', 300
UNION ALL
SELECT 2, '30-JUL-2023', 'Khalid', 800
UNION ALL
SELECT 3, '15-AUG-2023', 'Ahmed', 300
UNION ALL
SELECT 4, '25-DEC-2023', 'Ahmed', 400
;
SELECT SNO, DATE, NAME, ISNULL(LAG(REMAINEDMONEY, 1) OVER (ORDER BY DATE ASC), REMAINEDMONEY) AS TOTAL, EXPORT, PAYMENT, REMAINEDMONEY
FROM
(
SELECT SNO, DATE, NAME, SUM(CASE WHEN PAYMENT > 0 THEN -1*REMAINEDMONEY ELSE REMAINEDMONEY END) OVER( ORDER BY Date ASC) AS TOTAL, EXPORT, PAYMENT, SUM(CASE WHEN PAYMENT > 0 THEN -1*REMAINEDMONEY ELSE REMAINEDMONEY END) OVER( ORDER BY Date ASC) AS REMAINEDMONEY
FROM
(
SELECT SNO, DATE, NAME, MONEY AS EXPORT, 0 AS PAYMENT, MONEY AS REMAINEDMONEY
FROM @EXPORT
UNION ALL
SELECT 1, NULL, NAME, 0, 0, MONEY
FROM @AGENT
UNION ALL
SELECT SNO, DATE, NAME, 0 AS EXPORT, MONEY AS PAYMENT, MONEY AS REMAINEDMONEY
FROM @PAYMENT
) T
WHERE NAME = 'Ahmed'
) P
You need to read what LAG function do in order to understand how the solution works, assuming that you understand already how PARTION BY and OVER works as its already present in your solution