title: SELECT query with multiple sub-queries for counts
body: <p>I just wrote this query for a report.  But I originally wrote it without the date range filter on every sub-query.  But that didn't work.  So I added it to each sub-query.  And that worked, but I don't really like having to repeat it every time, is there syntax to do the same thing simpler?</p>
  
  <pre><code> SELECT Count(r.id)                       AS cnt_total,
     (SELECT Count(r1.entity_id)
      FROM   auto_reminders_members r1
      WHERE  r1.reminder_id = r.reminder_id
             AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                               101)
                                               AND
                 CONVERT(DATETIME,
                 '03/28/2013' + ' 23:59:59.997 ', 101)
             AND r1.action = 'notnow') AS cnt_notnow,
     (SELECT Count(r1.entity_id)
      FROM   auto_reminders_members r1
      WHERE  r1.reminder_id = r.reminder_id
             AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                               101)
                                               AND
                 CONVERT(DATETIME,
                 '03/28/2013' + ' 23:59:59.997 ', 101)
             AND r1.action = 'insert') AS cnt_insert,
     (SELECT Count(r1.entity_id)
      FROM   auto_reminders_members r1
      WHERE  r1.reminder_id = r.reminder_id
             AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                               101)
                                               AND
                 CONVERT(DATETIME,
                 '03/28/2013' + ' 23:59:59.997 ', 101)
             AND r1.action = 'update') AS cnt_update,
     (SELECT Count(r1.entity_id)
      FROM   auto_reminders_members r1
      WHERE  r1.reminder_id = r.reminder_id
             AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                               101)
                                               AND
                 CONVERT(DATETIME,
                 '03/28/2013' + ' 23:59:59.997 ', 101)
             AND r1.action = 'verify') AS cnt_verify
  FROM   auto_reminders_members r
  WHERE  r.reminder_id = 1
         AND r.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013', 101) AND
                                              CONVERT(DATETIME,
                                              '03/28/2013' + ' 23:59:59.997 ', 101
                                              )
  GROUP  BY r.reminder_id  
  </code></pre>