Three tables SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Three tables SQL query

Author

Topic

milo1981
Starting Member

18 Posts

Posted - 2013-11-03 : 07:42:13

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: [url]sqlfiddle.com/#!3/fcebf/3[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 09:15:27

Sounds like this to me


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)

EDIT : Fixed typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

milo1981
Starting Member

18 Posts

Posted - 2013-11-03 : 11:17:37

Thank you Visakh, for your reply!

But, I get an error (Incorrect syntax near the keyword 'FROM') at this line:
FROM VehicleAllocation h

Does it have to do with the fact that VehicleAllocation alias is the same as History alias (h)?

Later Edit: I get it. It was missing a '*' in the SELECT before the FROM where the error was appearing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 13:19:15

quote:
Originally posted by milo1981

Thank you Visakh, for your reply!

But, I get an error (Incorrect syntax near the keyword 'FROM') at this line:
FROM VehicleAllocation h

Does it have to do with the fact that VehicleAllocation alias is the same as History alias (h)?

Later Edit: I get it. It was missing a '*' in the SELECT before the FROM where the error was appearing.

sorry tht was a typo. Fixed in posted query
Did it gave you expected result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

milo1981
Starting Member

18 Posts

Posted - 2013-11-27 : 07:19:16

Visakh,

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?

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

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 11:40:10

Thats required as attempt is to find date till vehicle was with particular driver.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

milo1981
Starting Member

18 Posts

Posted - 2013-11-27 : 12:31:25

Is that line required? If I remove it, the duplicate line disappears.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources