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:
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.
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.
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
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
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?
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