title: SQL - Calculate active minutes per month per device
body: <p>I am seeking some input/suggestions on ways that I could calculate the active number of minutes per month, as a percentage, of a specific device.</p>
  
  <p>My table has columns:</p>
  
  <pre><code>device_id      |     build_date
  A4207B23-C984     2015-05-28 07:33:29.6980000 -04:00
  76D53271-FD34     2015-05-29 15:51:19.0000000 -04:00
  </code></pre>
  
  <p>I would like to generate a report each month showing the percentage of the month that a device was active and "building."  Any build in a single minute could be counted as being active, but I need to account for varying days of the month and various (100+) difference devices.  </p>
  
  <p>As an example, for November I'd love to be able to see:</p>
  
  <pre><code>device_id      |     Utilization
  A4207B23-C984     33.2
  76D53271-FD34     21.9
  </code></pre>
  
  <p>Which would correspond to the 'A4' device having at least one build during 33.2% of the minutes of the month.  So November has 30 days, 24 hours, 60 minutes = 14,342 unique minutes.</p>
  
  <p>Would I use something like:</p>
  
  <pre><code>distinct(left(build_date,16))
  </code></pre>
  
  <p>And then narrow that down by device, called by a query that uses DATEDIFF for the number of days in the month?</p>
  
  <p>Any pointers or ideas would be greatly helpful.  Thanks all.  Roger</p>