Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period

[Peter Brawley]

Here's a first pass at solving the problem.

1. Make a calendar table with a row for each possible reporting year+month eg

create table reportcal(yr smallint unsigned, mo tinyint unsigned, primary key(yr,mo));

2. Write a simple utility function to get started processing these dates

set global log_bin_trust_function_creators=1;
create function yearmonth(d date) returns int
return 100*year(d)+month(d);

We'll use this in the query. If performance becomes an issue, you may later want to optimise the function away.

3. Build the query from the bottom up, starting with its WHERE & GROUP BY clauses ...

...where concat(reportcal.yr,rreportcal.mo) between '201201' and '201305'
group by Year, Month;

4. You'll need a join from reportcal to users for per-month regs, another join to users for cumulative regs, and of course a join for invoices, so the FROM clause will look like this ...

from reportcal r
join users u on 100*r.yr + r.mo=yearmonth(u.registration_date)
join users ucum on yearmonth(u.registration_date) <= 100*r.yr + r.mo
join invoices iact on ucum.id=i.user_id and yearmonth(i.creation_date)=100*r.yr + r.mo

5. Fill in the SELECT expressions, and the prototype is ready to roll ...

select 
  r.yr as Year, r.mo as Month, 
  count(u.id) as Registrations, 
  count(ucum.id) as 'Registrations So far', 
  count(distinct i.user_id) as Active,
  count(i.invoice_number) as Invoices
from reportcal r
join user      u    on 100*r.yr + r.mo=yearmonth(u.registration_date)
join user      ucum on yearmonth(u.registration_date) <= 100*r.yr + r.mo
join invoices  i    on ucum.id=i.user_id and yearmonth(i.creation_date)=100*r.yr + r.mo
where concat(r.yr,r.mo) between '201201' and '201305'
group by Year, Month;

Not tested, but it should get you started.