SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT, start_date DATE, end_date DATE, payment INT);
INSERT INTO test VALUES
(1, '2019-01-01', '2019-03-15', 1000),
(2, '2018-01-15', '2018-04-30', 100000);
2 rows affected

WITH cte1 AS ( SELECT id, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY, start_date), start_date))) lastday 
               FROM test
               UNION ALL
               SELECT cte1.id, DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEADD(DAY, 1-DATEPART(DAY, cte1.lastday), cte1.lastday)))
               FROM cte1 
               WHERE cte1.lastday < ( SELECT DATEADD(DAY, -DATEPART(DAY, DATEADD(DAY, 1, end_date)), DATEADD(DAY, 1, end_date))
                                      FROM test
                                      WHERE test.id = cte1.id )
            )
,   cte2 AS ( SELECT id, COUNT(*) cnt
              FROM cte1
              GROUP BY id
            )
SELECT test.id, cte1.lastday AS date, test.payment/cte2.cnt amount
FROM test, cte1, cte2
WHERE cte1.id = test.id AND cte2.id = test.id
ORDER BY test.id, cte1.lastday;


id
date
amount


1
31/01/2019 00:00:00
500

1
28/02/2019 00:00:00
500

2
31/01/2018 00:00:00
25000

2
28/02/2018 00:00:00
25000

2
31/03/2018 00:00:00
25000

2
30/04/2018 00:00:00
25000


WITH cte1 AS ( SELECT id, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY, start_date), start_date))) lastday 
               FROM test
               UNION ALL
               SELECT cte1.id, DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEADD(DAY, 1-DATEPART(DAY, cte1.lastday), cte1.lastday)))
               FROM cte1 
               WHERE cte1.lastday < ( SELECT DATEADD(DAY, -DATEPART(DAY, DATEADD(DAY, 1, end_date)), DATEADD(DAY, 1, end_date))
                                      FROM test
                                      WHERE test.id = cte1.id )
            )
/*
,   cte2 AS ( SELECT id, COUNT(*) cnt
              FROM cte1
              GROUP BY id
            )
*/
SELECT test.id, cte1.lastday AS date, test.payment/COUNT(cte1.id) OVER (PARTITION BY cte1.id) amount
FROM test, cte1
WHERE cte1.id = test.id
ORDER BY test.id, cte1.lastday;


id
date
amount


1
31/01/2019 00:00:00
500

1
28/02/2019 00:00:00
500

2
31/01/2018 00:00:00
25000

2
28/02/2018 00:00:00
25000

2
31/03/2018 00:00:00
25000

2
30/04/2018 00:00:00
25000

id	date	amount
1	31/01/2019 00:00:00	500
1	28/02/2019 00:00:00	500
2	31/01/2018 00:00:00	25000
2	28/02/2018 00:00:00	25000
2	31/03/2018 00:00:00	25000
2	30/04/2018 00:00:00	25000