Three tables SQL query

I have a table (Vehicles) which contains a list of vehicles.

VehicleID
PlateNo
CurrentDriver

I also have a table (History) which contains a the driver history for the vehicles:

  • How to tell Linq which Database Version to use
  • Connection string to remote DB when using EntityFramework
  • What are the advantages of VistaDB
  • Insert 65535 rows into SQL Server 2008
  • Invalid Instance when using ADODB.Recordset
  • Is there a way to get dates with custom formats in SQL Server?
  • HistoryID
    VehicleID
    ReceivedDate (vehicle receiving date)
    DriverName
    

    I have another table (Repairs) which contains the repairs for all the vehicles:

    RepairID
    VehicleID
    RepairDate
    RepairCost
    

    Using SQL Server and based on the History table, I want to get all the RepairCost values between two dates for a given DriverName.

    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.

    My query so far is:

    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'
    

    There’s also some sample data in a SQL Fiddle.

    The problem seems to be that I’m getting all the results twice.

    LATER EDIT:
    My progress so far:

    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 <> h.DriverName
    AND ReceivedDate > 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 > = @StartDt
    AND RepairDate < @EndDt + 1
    AND RepairDate BETWEEN h.ReceivedDate AND COALESCE(h.ChangeDate,RepairDate)
    

    I discoverd a problem with the line ‘AND DriverName <> 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.

    Sample data:

    ‘History’ table

    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
    

    ‘Repairs’ table

    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
    

    Query result

    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
    

    As you can see in the query result, line 3 and 4 have the same value/date.
    The query interval was 01-01-2013 <-> 31-12-2013.

    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…

    How can I adapt the script?

    Thanks!

    2 Solutions collect form web for “Three tables SQL query”

    I have no idea why you include you Vehicle table in your query, as you don’t want any information from there.

    You are getting “double” results because you match every Repair (e.g. the one on jan 15th) with every record with the same Vehicle id is History (there are three of those!). Two of those matches are for drive John, so you get two results.

    What you want is to match only on the driver that, according to your history table, was the drive at the time of the repair!

    So, I first matched each repairdate with the actually matching Receiveddate in the history table:

    SELECT R1.Repairdate, Max(H1.ReceivedDate) as ReceivedDate
    FROM Repairs R1
    JOIN History H1 
      ON R1.VehicleID=H1.VehicleID 
     AND H1.ReceivedDate < R1.RepairDate
    

    GROUP BY R1.RepairDate

    I then used that query in a join to receive the wanted data:

    SELECT R.RepairDate, H.DriverName, R.RepairCost 
      FROM Repairs AS R 
      JOIN History H ON R.VehicleID=H.VehicleID 
      JOIN (
           SELECT R1.Repairdate, Max(H1.ReceivedDate) as ReceivedDate
             FROM Repairs R1
             JOIN History H1 
               ON R1.VehicleID=H1.VehicleID 
              AND H1.ReceivedDate < R1.RepairDate
            GROUP BY R1.RepairDate) 
            AS H2
        ON R.Repairdate = H2.Repairdate
       AND H.ReceivedDate = H2.Receiveddate
     WHERE R.RepairDate BETWEEN '01.01.2013' AND '04.01.2013' 
       AND H.DriverName = 'John'
    

    This returns me 6 records : http://sqlfiddle.com/#!3/fcebf/62

    As a sanity check, leave out the complete WHERE on date and name and include the vehicle number in teh select.

    You will get 14 repairs listed with the name of the driver who was drivign the vehicle at that time. You can easily confirm that the driver linked to the vehicle at that time is correct according to your History data:

    DRIVERNAME  VEHICLEID   REPAIRDATE  REPAIRCOST
    John    1   January, 15 2013 00:00:00+0000  10
    Ryan    2   January, 18 2013 00:00:00+0000  15
    Ryan    2   January, 22 2013 00:00:00+0000  15
    John    1   February, 03 2013 00:00:00+0000 5
    Ryan    2   February, 05 2013 00:00:00+0000 25
    John    1   February, 10 2013 00:00:00+0000 10
    John    2   February, 26 2013 00:00:00+0000 10
    Ryan    1   March, 01 2013 00:00:00+0000    100
    John    2   March, 03 2013 00:00:00+0000    30
    John    2   March, 08 2013 00:00:00+0000    5
    Ryan    1   March, 10 2013 00:00:00+0000    45
    Ryan    1   March, 17 2013 00:00:00+0000    25
    Ryan    2   March, 25 2013 00:00:00+0000    10
    Ryan    2   March, 28 2013 00:00:00+0000    30
    

    if you add HistoryID to your query you will notice that rows are not duplicate, but they have different HistoryID, try this query

    SELECT H.DriverName, R.RepairCost, R.RepairDate , H.HistoryID
    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'
    

    so I think it’s something wrong with your data.

    you can eliminate these duplicate rows using DISTINCT , but I recommend you to double check your History table data

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.