0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.