# Making Schedule

I have 3 columns.

``````Start_date = '2018-01-15'
end_date = '2018-04-30'
payment = 100000
``````

i want out like

``````date        amount
2018-01-31  25000
2018-02-28  25000
2018-03-31  25000
2018-04-30  25000
``````

ps. date is last day of the month. Schedule is till end_date group by each month and year. amount is divided by no of month.

## 1 Answer

``````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;
``````

or

``````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 )
)
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;
``````

fiddle

• Firstly, i wants to thanks for helps me. But this error shows "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." – user172047 Mar 14 at 8:57
• @user172047 Check. – Akina Mar 14 at 9:41
• yes, thank you so much @Akina! – user172047 Mar 14 at 9:47
• @user172047 You can remove `cte2` and use `COUNT(cte1.id) OVER (PARTITION BY cte1.id)` instead of `cte2.cnt`. See updated fiddle. – Akina Mar 14 at 9:51