Growing number of months passed - MySQL

Question

I would like to create the MonthCount column described below. I have the ID and Date fields already created I am just having trouble thinking of a clever way to count the number of dates that have passed. The dates are always the first of the month, but the first month could be any month between Jan and Dec.

ID    Date    MonthCount
1     1/2016  1 
1     2/2016  2
1     3/2016  3
2     5/2015  1
2     6/2015  2
2     7/2015  3

It seems like I remember reading somewhere about joining the table to itself using a > or < operator but I can't completely recall the method.

The stated duplicate is not appropriate because there is no `partition by` for the row number. — Gordon Linoff, Jan 24 '17 at 20:52
@GordonLinoff .. the op should be able to get to a query from one of those answers. — Vamsi Prabhala, Jan 24 '17 at 20:53
@vkp I am more than happy to read another answer if this has been asked before. A link would be much appreciated because I have been searching and the only results I've found are to find the number of months between two dates. — Matthew Snell, Jan 24 '17 at 20:54
googling `row number mysql` would've given you 100's of links to SO posts. — Vamsi Prabhala, Jan 24 '17 at 20:55
If he was already familiar with the `row_number()` functionality of SQL Server... — Jerrad, Jan 24 '17 at 20:57

score 0 · Answer 1 · answered Jan 24 '17 at 20:54

The best way to handle this in MySQL is to use variables:

  select t.*,
         (@rn := if(@id = id, @rn + 1,
                    if(@id := id, 1, 1)
                   )
         ) as rn
  from t cross join
       (select @rn := 0, @id := -1) params
  order by id, date;

score 0 · Answer 2 · answered Jan 24 '17 at 20:55

It looks like you're looking for:

select a.id, a.date, b.mindate
from table as a
inner join (
    select id, min(date) as mindate
    from table
    group by id
) as b on (a.id=b.id)

this will give you

ID    Date    mindate
1     1/1/2016  1/1/2016
1     1/2/2016  1/1/2016
1     1/3/2016  1/1/2016
2     1/5/2015  1/5/2015
2     1/6/2015  1/5/2015
2     1/7/2015  1/5/2015

now homework for you is to figure out how to calculate difference between two dates

Thanks, this got me to where I needed to go. Hadn't considered that approach. — Matthew Snell, Jan 24 '17 at 21:22

Growing number of months passed - MySQL

2 Answers2