SQL - Count month difference in non-consecutive date period

Hey coders 0
Viewed 27

I am trying to extract how many months of membership a member gets up to date. As the picture shows, this member got four years of subscription since 2018. However, she stopped the subscription for a year ending in 2019. And then, restart the membership in 2020 again.

Each membership lasts for 12 months. if we look at the last membership starting from 2022-05-08, it will end up on 2023-05-08. However, I only want to get the total month count up to date(getdate - 2022-09-14).

Please advise how I could approach this matter. Thanks!

enter image description here

tsql sql
1 Answers

Assuming you were planning to apply sum(monthcount), you could wrap the monthcount within a CASE statement to checks if the vip_end is greater than today's date:

sum(case when vip_end > getdate() then ... else monthcount end)

What you do within that ... depends on whether you wanted to just count the different number of months within the date range (e.g. 31st Jan -> 01st Feb is counted as a whole month because it just considers Jan -> Feb):

datediff(month, datecreated, getdate())

or perhaps calculate the number of months based on the average days in a month:

datediff(day, datecreated, getdate())*12.0/365.25

or maybe something else... it really depends on what level of detail you want to achieve.

userutLl10
Related