Calculate the amount between two dates in the table-CodePudding

I have these 2 tables. I need to merge into one table. Where should I put in the column the amount of expenses between two dates. How can I do it?

Profits:

Id	Date	Money
1	01.01.2022	100
2	15.01.2022	50
3	25.01.2022	30

Expenses:

Id	Date	Money
1	01.01.2022	20
2	03.01.2022	30
3	30.01.2022	40

Result:

Id	Date	Profits	Expenses(Sum)
1	01.01.2022	100	50
2	15.01.2022	50
3	25.01.2022	30	40

CodePudding user response：

The following statement is a possible option:

Data:

SELECT *
INTO Profits
FROM (VALUES
   (1, CONVERT(date, '01.01.2022', 104), 100),
   (2, CONVERT(date, '15.01.2022', 104), 50),
   (3, CONVERT(date, '25.01.2022', 104), 30)
) v (Id, [Date], [Money])

SELECT *
INTO Expenses
FROM (VALUES
   (1, CONVERT(date, '01.01.2022', 104), 20),
   (2, CONVERT(date, '03.01.2022', 104), 30),
   (3, CONVERT(date, '30.01.2022', 104), 40)
) v (Id, [Date], [Money])

Statement:

SELECT p.Id, p.Date, p.Money AS Profits, SUM(e.Money) AS Expenses
FROM (
   SELECT *, LEAD(Date) OVER (ORDER BY Date) AS NextDate
   FROM Profits
) p
LEFT JOIN Expenses e ON p.Date <= e.Date AND (e.Date <= p.NextDate OR p.NextDate IS NULL)
GROUP BY p.Id, p.Date, p.Money

Result:

Id	Date	Profits	Expenses
1	2022-01-01	100	50
2	2022-01-15	50
3	2022-01-25	30	40

For SQL Server 2008 you have to replace LEAD() with a self-join (a simplified approach when there are no gaps in the Id column):

SELECT p.Id, p.Date, p.Money AS Profits, SUM(e.Money) AS Expenses
FROM (
   SELECT p1.*, p2.Date AS NextDate
   FROM Profits p1
   LEFT JOIN Profits p2 ON p1.Id = p2.Id - 1
) p
LEFT JOIN Expenses e ON p.Date <= e.Date AND (e.Date <= p.NextDate OR p.NextDate IS NULL)
GROUP BY p.Id, p.Date, p.Money