Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Three tables SQL query Expand / Collapse
Author
Message
Posted Sunday, November 3, 2013 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 12:42 AM
Points: 2, Visits: 29
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
Post #1510884
Posted Sunday, November 3, 2013 6:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 36,978, Visits: 31,499
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1510887
Posted Monday, November 4, 2013 10:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 6:19 AM
Points: 698, Visits: 129
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
Post #1511196
Posted Monday, November 4, 2013 7:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
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!
Post #1511346
Posted Tuesday, November 5, 2013 8:46 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 6:19 AM
Points: 698, Visits: 129
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.
Post #1511515
Posted Tuesday, November 5, 2013 12:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:06 PM
Points: 8,713, Visits: 9,259
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
Post #1511588
Posted Tuesday, November 5, 2013 5:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
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!
Post #1511676
Posted Thursday, November 28, 2013 12:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 12:42 AM
Points: 2, Visits: 29
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!
Post #1518279
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse