title: Count memberships by type for a series of dates
body: <p>I'm using SQL Server 2012 and have a database containing membership information. I'm trying to write SQL that will count the members of each type that exist during each month so I can report on if membership is going up/down month by month. A simplified version of my schema is:</p>
  
  <p><a href="http://www.sqlfiddle.com/#!6/5fed2/1" rel="nofollow">http://www.sqlfiddle.com/#!6/5fed2/1</a></p>
  
  <pre><code>CREATE TABLE MemberType
  (
      MemberTypeID INT,
      Name VARCHAR(50)
  );
  
  CREATE TABLE Person
  (
      PersonID INT,
      Name VARCHAR(50)
  );
  
  CREATE TABLE Invoice
  (
      InvoiceID INT,
      DateStart DATE,
      DateEnd   DATE,
      PersonID  INT,     --(FK) Person that purchased the membership
      MemberTypeID INT,  --(FK) Type of membership
  );
  
  INSERT MemberType (MemberTypeID,Name) VALUES (1,'Regular')
  INSERT MemberType (MemberTypeID,Name) VALUES (2,'Special')
  INSERT MemberType (MemberTypeID,Name) VALUES (3,'Honorary')
  
  INSERT Person (PersonID,Name) VALUES (1,'Joe Smith')
  INSERT Person (PersonID,Name) VALUES (2,'Anna Smith')
  INSERT Person (PersonID,Name) VALUES (3,'Ted Williams')
  INSERT Person (PersonID,Name) VALUES (4,'Bill Williams')
  
  INSERT Invoice VALUES(1,'2011-5-1', '2012-1-1', 1, 1)
  INSERT Invoice VALUES(2,'2010-1-1', '2013-1-1', 2, 2)
  INSERT Invoice VALUES(3,'2012-2-1', '2013-2-1', 3, 1)
  INSERT Invoice VALUES(4,'2009-1-1', '2011-5-1', 1, 1)
  INSERT Invoice VALUES(5,'2011-1-1', '2012-5-1', 4, 1)
  </code></pre>
  
  <p><strong>I've tried to get the member counts for a specific date like this:</strong></p>
  
  <pre><code>DECLARE @RunDt DATE='2011-1-1'
  
  SELECT
  mt.Name [MemberType], @RunDt [Date], count(p.PersonID) [Count]
  FROM MemberType mt
  LEFT OUTER JOIN Invoice i ON i.MemberTypeID=mt.MemberTypeID
  LEFT OUTER JOIN Person p ON p.PersonID=i.PersonID
  WHERE @RunDt BETWEEN i.DateStart AND i.DateEnd
  GROUP BY mt.Name
  </code></pre>
  
  <p>I think this is close but I want the honorary type to be included with a count of 0. I thought the <code>LEFT OUTER JOIN</code>s would accomplish that, but thy do not.
  Also this is only for a single date. My next step is to get the results of that query for the 1st of each month all together in a single result set.</p>