November 3, 2013 at 5:28 am
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 progress so far:
November 3, 2013 at 6:35 am
The test data that you posted on SQLFiddle has some serious problems with wrapped lines and missing quotes.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 10:17 am
I am not really sure what your question is. Is this what you are looking for:
John10.002013-01-15 00:00:001
John10.002013-01-15 00:00:001
John5.00 2013-02-03 00:00:001
John5.00 2013-02-03 00:00:001
John10.002013-02-10 00:00:001
John10.002013-02-10 00:00:001
John100.002013-03-01 00:00:001
John100.002013-03-01 00:00:001
John45.002013-03-10 00:00:001
John45.002013-03-10 00:00:001
John25.002013-03-17 00:00:001
John25.002013-03-17 00:00:001
John15.002013-01-18 00:00:002
John15.002013-01-22 00:00:002
John25.002013-02-05 00:00:002
John10.002013-02-26 00:00:002
John30.002013-03-03 00:00:002
John5.00 2013-03-08 00:00:002
John10.002013-03-25 00:00:002
John30.002013-03-28 00:00:002
If this is what you are looking for the following will give it to you.
SELECT H.DriverName, R.RepairCost, R.RepairDate , r.VehicleID
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'
ORDER BY R.VehicleID, R.RepairDate
November 4, 2013 at 7:45 pm
I can't get to the SQL Fiddle page but I can say that the BETWEEN clause suggested in the below query is not a suggested best practice when evaluating for data within a date range.
joe.wolfe (11/4/2013)
SELECT H.DriverName, R.RepairCost, R.RepairDate , r.VehicleIDFROM 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'
ORDER BY R.VehicleID, R.RepairDate
Not to mention that it probably won't work with dates formatted with . separators, unless there's some obscure SQL setting that turns that on.
Proper construct would be more like:
RepairDate >= '2013-01-01' AND RepairDate < '2013-04-01'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 5, 2013 at 8:46 am
Dwain,
I took the date compare from the original query that was submitted with the question. I didn't think the the dates with dots would work either and I have never seen anybody do this before, but it seems to work. I do agree with you though that the standard format is the way to go.
November 5, 2013 at 12:11 pm
You need some additional bchages as well as Dwain's correction, in order to handle the hvehicle history correctly.
First remove
INNER JOIN History H ON H.VehicleID=V.VehicleID
(actually since the Vehicles table isn't needed for this query you should remove
INNER JOIN Vehicles AS V
as well.)
Then change the SELECT line to
SELECT (SELECT DriverName from History H
WHERE H.ReceivedDate = (SELECT MAX(ReceivedDate) from History H2 where H2.VehicleID = R.VehicleID and H2.ReceivedDate <= R. RepairDate)
AND H.VehicleID = R.VehicleID) AS DriverName, R.RepairCost, R.RepairDate
I haven't checked that this works with your data because I can't get the data to load, but it should show you what needs doing even if it doesn't work - you need to get who owned the vehicle at the repair date, by looking for the latest history date for the vehicle which isn't after the repair date and then extracting the driver name from the history record with that history date and vehicleID.
Tom
November 5, 2013 at 5:29 pm
L' Eomot Inversé (11/5/2013)
You need some additional bchages as well as Dwain's correction, in order to handle the hvehicle history correctly.First remove
INNER JOIN History H ON H.VehicleID=V.VehicleID
(actually since the Vehicles table isn't needed for this query you should remove
INNER JOIN Vehicles AS V
as well.)
Then change the SELECT line to
SELECT (SELECT DriverName from History H
WHERE H.ReceivedDate = (SELECT MAX(ReceivedDate) from History H2 where H2.VehicleID = R.VehicleID and H2.ReceivedDate <= R. RepairDate)
AND H.VehicleID = R.VehicleID) AS DriverName, R.RepairCost, R.RepairDate
I haven't checked that this works with your data because I can't get the data to load, but it should show you what needs doing even if it doesn't work - you need to get who owned the vehicle at the repair date, by looking for the latest history date for the vehicle which isn't after the repair date and then extracting the driver name from the history record with that history date and vehicleID.
Tom has hit it on the nose. It's difficult to provide good help because you should help us by providing DDL, consumable sample data and expected results. From that, it should be short work for someone to provide you with a working solution.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 28, 2013 at 12:41 am
Hello,
Thank you all for your replies!
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!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply