Finding max value involving a join

1

Suppose I have a table of website visits and customer birthdays, as follows:

create table visits (visitid INT PRIMARY KEY, date datetime, cid INT);
insert into visits values (1, '2016-01-01', 5000);
insert into visits values (2, '2016-01-07', 5000);
insert into visits values (3, '2016-02-01', 6000);
insert into visits values (4, '2016-02-11', 8000);
insert into visits values (5, '2016-02-28', 4000);
insert into visits values (6, '2016-03-01', 5000);
insert into visits values (7, '2016-04-01', 6000);
insert into visits values (8, '2016-07-01', 7000);
insert into visits values (9, '2016-08-01', 9000);
insert into visits values (10, '2016-10-01', 1000);

create table birthdays (cid INT, birthday datetime);
insert into birthdays values (4000,'1983-09-19');
insert into birthdays values (5000,'1983-09-25');
insert into birthdays values (6000,'1983-01-25');
insert into birthdays values (7000,'1983-02-25');
insert into birthdays values (8000,'1983-03-25');
insert into birthdays values (9000,'1983-12-25');
insert into birthdays values (1000,'1983-11-25');

How can I produce a result with two columns: month, and cid of customer with the most website visits among customers with a birthday in that month?

2

If people with birthday on february 29th should be counted on february 29th (every 4th year):

select month(v.[date]) as [month]
      ,v.cid
      ,count(*) as visits
  from visits as v
       inner join birthdays as b
               on b.cid=v.cid
              and month(b.birthday)=month(v.[date])
              and day(b.birthday)=month(v.[date])
 group by month(v.[date])
         ,v.cid
;

If people with birthday on february 29th should be counted on february 29th in leap year and february 28th in non-leap year:

select month(v.[date]) as [month]
      ,v.cid
      ,count(*) as visits
  from visits as v
       inner join birthdays as b
               on b.cid=v.cid
              and dateadd(year,year(v.[date])-year(b.birthday),b.birthday)=v.[date]
 group by month(v.[date])
         ,v.cid
;

If people with birthday on february 29th should be counted on february 29th in leap year and march 1th in non-leap year:

select month(v.[date]) as [month]
      ,v.cid
      ,count(*) as visits
  from visits as v
       inner join birthdays as b
               on b.cid=v.cid
              and dateadd(day,day(b.birthday)-1,dateadd(month,month(b.birthday)-1,dateadd(year,datediff(year,0,v.[date]),0)))=v.[date]
 group by month(v.[date])
         ,v.cid
;