title: Three tables SQL query
body: <p>I have a table (Vehicles) which contains a list of vehicles.</p>
  
  <pre><code>VehicleID
  PlateNo
  CurrentDriver
  </code></pre>
  
  <p>I also have a table (History) which contains a the driver history for the vehicles:</p>
  
  <pre><code>HistoryID
  VehicleID
  ReceivedDate (vehicle receiving date)
  DriverName
  </code></pre>
  
  <p>I have another table (Repairs) which contains the repairs for all the vehicles:</p>
  
  <pre><code>RepairID
  VehicleID
  RepairDate
  RepairCost
  </code></pre>
  
  <p>Using SQL Server and based on the History table, I want to get all the RepairCost values between two dates for a given DriverName.</p>
  
  <p>For example, I want to get all the RepairCost values for driver 'John Doe', between 01.01.2013 and 01.05.2013, who was allocated to three different vehicles in that period.</p>
  
  <p>My query so far is:</p>
  
  <pre><code>SELECT H.DriverName, R.RepairCost, R.RepairDate
    FROM Repairs AS R 
   INNER JOIN Vehicles AS V ON R.VehicleID = V.VehicleID 
   INNER JOIN History H ON H.VehicleID = V.VehicleID 
   WHERE H.DriverName = 'John' 
     AND R.RepairDate BETWEEN '01.01.2013' AND '04.01.2013'
  </code></pre>
  
  <p>There's also some sample data in a <a href="http://sqlfiddle.com/#!3/fcebf/3" rel="nofollow">SQL Fiddle</a>.</p>
  
  <p>The problem seems to be that I'm getting all the results twice.</p>
  
  <p>LATER EDIT:
  My progress so far:</p>
  
  <pre><code>DECLARE @Driver varchar(50),@StartDt datetime, @EndDt datetime
  
  SELECT @Driver = 'John Doe',@StartDt = '20130101' ,@EndDt = '20130501'
  
  ;With VehicleAllocation
  AS
  (
  SELECT h.*,h1.ChangeDate
  FROM History h
  OUTER APPLY (SELECT MIN(ReceivedDate) AS ChangeDate
  FROM History
  WHERE VehicleID = h.VehicleID
  AND DriverName &lt;&gt; h.DriverName
  AND ReceivedDate &gt; h.ReceivedDate
  )h1
  WHERE h.DriverName = @Driver
  )
  SELECT *
  FROM VehicleAllocation h
  INNER JOIN Repairs r
  ON r.VehicleID = h.VehicleID
  WHERE DriverName = @Driver
  AND RepairDate &gt; = @StartDt
  AND RepairDate &lt; @EndDt + 1
  AND RepairDate BETWEEN h.ReceivedDate AND COALESCE(h.ChangeDate,RepairDate)
  </code></pre>
  
  <p>I discoverd a problem with the line 'AND DriverName &lt;> h.DriverName'. Why is that line useful? If I had the same driver name, one after the other, in the History table, it skipped to the last car delivery date for that driver name.</p>
  
  <p>Sample data:</p>
  
  <p>'History' table</p>
  
  <pre><code>ReceivedDate  DriverName
  04.11.2013    Mike
  13.11.2013    Dan
  15.11.2013    Dan
  17.11.2013    Ryan
  20.11.2013    Dan
  22.11.2013    Ryan
  25.11.2013    Mike
  26.11.2013    Dan
  29.11.2013    Ryan
  04.12.2013    Dan
  </code></pre>
  
  <p>'Repairs' table</p>
  
  <pre><code>RepairDate RepairCost
  05.11.2013 2615.30
  14.11.2013 135.66
  16.11.2013 4913.04
  18.11.2013 538.92
  21.11.2013 152.48
  23.11.2013 5946.89
  26.11.2013 3697.64
  27.11.2013 734.01
  30.11.2013 279.62
  </code></pre>
  
  <p>Query result</p>
  
  <pre><code>RepairDate RepairCost
  07.11.2013 380.00
  14.11.2013 135.66
  16.11.2013 4913.04
  16.11.2013 4913.04
  21.11.2013 152.48
  27.11.2013 734.01
  </code></pre>
  
  <p>As you can see in the query result, line 3 and 4 have the same value/date.
  The query interval was 01-01-2013 &lt;-> 31-12-2013.</p>
  
  <p>Also, what if I want to get the SUM of different colums from different tables?
  For example, SUM(Total) column from 'Repairs' table, SUM(Value) column from 'Tires' table...</p>
  
  <p>How can I adapt the script?</p>
  
  <p>Thanks!</p>