title: Super complicated SQL statement...maybe impossible
body: <p>I've done some programming and some SQL but is no developer except for my own little projects.
  I now have a problem I would love to solve in pure SQL but can't really figure out how.
  I have 2 simple tables that i need to draw a monthly report from, but i need to do some counting and that's the tricky part.
  Here are the structure...</p>
  
  <pre><code>table 
    km_locations
  columns
    location_id INT
    location_name TXT
  
  table
    km_trips
  colums
    trip_id INT
    trip_date DATE
    start_location_id INT
    end_location_id INT
    km_start INT
    km_end INT
  </code></pre>
  
  <p>And here are the rules that make it too complicated for me to figure out...</p>
  
  <ol>
  <li>Create a report within a specific date range eg. between
  2016-02-15 and 2016-03-15.</li>
  <li>Replace start_location_id and end_location_id with corresponding
  location_name in km_locations.</li>
  <li>Count how many similar trips i made within the last 12 months and
  show the incremental value in a separate column.</li>
  <li>Only count trips too or from Home, and only count if no other trips with same start and end destination combo have been made on the date.</li>
  </ol>
  
  <p>The sample below without total_trips I can get with this statement.</p>
  
  <pre><code>SELECT
  t.trip_date,
  c1.location_name AS 'trip_start',
  c2.location_name AS 'trip_end',
  t.km_start,
  t.km_end
  FROM km_trips t
  LEFT JOIN km_locations c1 ON c1.location_id = t.start_location_id
  LEFT JOIN km_locations c2 ON c2.location_id = t.end_location_id
  WHERE t.trip_date BETWEEN '2016-02-06' AND '2016-03-10'
  ORDER BY t.trip_date ASC, t.km_start ASC 
  </code></pre>
  
  <p>wanted sample date output</p>
  
  <pre><code>id     date     trip_start      trip_end        km_start    km_end  total_trips
  1   2016-02-15  Home            Grocery Store   10675       10681   8
  2   2016-02-15  Grocery Store   Gas Station     10681       10684
  3   2016-02-15  Gas Station     Home            10684       10689   5
  4   2016-02-16  Home            Grocery Store   10689       10695   9
  5   2016-02-16  Grocery Store   Home            10695       10701
  6   2016-02-17  Home            School          10701       10723   12
  7   2016-02-17  School          Grocery Store   10723       10739
  8   2016-02-17  Grocery Store   Friends House   10739       10755
  9   2016-02-17  Friends House   Home            10755       10767   7
  10  2016-02-18  Home            Friends House   10767       10783   8
  11  2016-02-18  Friends House   Home            10755       10767
  12  2016-02-19  Home            Friends House   10767       10783   9
  13  2016-02-19  Friends House   Grocery Store   10783       10792
  14  2016-02-19  Grocery Store   Home            10792       10798   10
  </code></pre>
  
  <p>It might be too much for a SQL statement but I'm thinking that it might be possible with views, unions and som mad skills.
  It is a new project I am moving from Excel, so I'm open to any suggestions to table structures that can make this work.
  Right now I'm trying to make this work with SQLite but will go with another databse if that is my only option.</p>
  
  <p>Thanks.</p>