SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Three tables SQL query


Three tables SQL query

Author
Message
milo1981
milo1981
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 35
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:
http://sqlfiddle.com/#!3/fcebf/3
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339749 Visits: 42624
The test data that you posted on SQLFiddle has some serious problems with wrapped lines and missing quotes.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
joe.wolfe
joe.wolfe
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 213
I am not really sure what your question is. Is this what you are looking for:
John 10.00 2013-01-15 00:00:00 1
John 10.00 2013-01-15 00:00:00 1
John 5.00 2013-02-03 00:00:00 1
John 5.00 2013-02-03 00:00:00 1
John 10.00 2013-02-10 00:00:00 1
John 10.00 2013-02-10 00:00:00 1
John 100.00 2013-03-01 00:00:00 1
John 100.00 2013-03-01 00:00:00 1
John 45.00 2013-03-10 00:00:00 1
John 45.00 2013-03-10 00:00:00 1
John 25.00 2013-03-17 00:00:00 1
John 25.00 2013-03-17 00:00:00 1
John 15.00 2013-01-18 00:00:00 2
John 15.00 2013-01-22 00:00:00 2
John 25.00 2013-02-05 00:00:00 2
John 10.00 2013-02-26 00:00:00 2
John 30.00 2013-03-03 00:00:00 2
John 5.00 2013-03-08 00:00:00 2
John 10.00 2013-03-25 00:00:00 2
John 30.00 2013-03-28 00:00:00 2

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
dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 Visits: 6431
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.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



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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
joe.wolfe
joe.wolfe
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 213
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.
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36081 Visits: 12792
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

dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
milo1981
milo1981
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 35
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search