title: SQL: Count items by day (where day must be converted)
body: <p>Thank you in advance for reading my question. I have data like this:</p>
  
  <pre><code>| Date_Time               | City | Vehicle_ID |
  '2017-01-01 00:00:00.000'   'A'       1159
  '2017-01-01 13:12:00.000'   'A'       1159
  '2017-01-02 01:00:34.000'   'B'       1323
  '2017-01-02 02:00:25.000'   'A'       1159 
  </code></pre>
  
  <p>I would like to calculate a new column 'Day_Number' which represents the interval of days from min(Date_Time) + 1, and then count the unique(Vehicle_IDs) by 'City' and by 'Day_Number'.</p>
  
  <p>Example: </p>
  
  <pre><code>| City | Day | count_unique_vehicles |
    'A'     1             2
    'A'     2             1
    'B'     2             1
  </code></pre>
  
  <p>I have written some terrible sql like</p>
  
  <pre><code> select date_time, city, count(distinct Vehicle_ID) as unique_vehicle
   from X
   where date_time &gt;= '2017-12-01 00:00:00.000'
   group by DATEADD(DAY, DATEDIFF(DAY,'2017-12-01', date_time), date_time), Division
  </code></pre>
  
  <p>which doesn't work. What should my query look like?</p>