CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2012date-difference
sparc
sparc

Reputation: 429

Calculate number of payments done between two dates SQL

I want to calculate the number of payments made between two dates in SQL including the start date.

For e.g. if the first payment date is 28/08/2020, and the subsequent payments are done on 28th of every month.

I want to calculate how many payments are done so far.

I have tried select datediff(month,'2020-08-28 00:00:00.000','2021-03-28 00:00:00.000') it is giving 7, but I want it to be 8.

if I try select datediff(month,'2020-08-28 00:00:00.000','2021-04-01 00:00:00.000') it is giving 8.

I want to know the number of months from 28/08/2020. It should be counted as 1 for 28/08/2020.

The desired result should be as below

28/08/2020 - 1
28/09/2020 - 2
28/10/2020 - 3
28/11/2020 - 4
28/12/2020 - 5
28/01/2021 - 6
28/02/2021 - 7
28/03/2021 - 8

Please can you advise. Thank you.

Edit

If the first payment date is 30/01/2020, because there is no 30 Feb, it should take the end of the month date and should show as 2 payments done so far.

Upvotes: 1

Views: 687

Answers (1)

Jeff
Jeff

Reputation: 537

Is this what you are looking for?

Declare @dates Table (PaymentDate date);

 Insert Into @dates (PaymentDate)
 Values ('2020-08-28'), ('2020-09-28'), ('2020-10-28'), ('2020-11-28'), ('2020-12-28'), ('2021-01-28'), ('2021-02-28'), ('2021-03-28');

 Select *
      , PaymentCount = datediff(month, min(PaymentDate) over(Order By PaymentDate), PaymentDate) + 1
   From @dates;

Upvotes: 0

Related Questions