SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Three tables SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

milo1981
Starting Member

15 Posts

Posted - 11/03/2013 :  07:42:13  Show Profile  Reply with Quote
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: sqlfiddle.com/#!3/fcebf/3

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/03/2013 :  09:15:27  Show Profile  Reply with Quote
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

Edited by - visakh16 on 11/03/2013 13:18:33
Go to Top of Page

milo1981
Starting Member

15 Posts

Posted - 11/03/2013 :  11:17:37  Show Profile  Reply with Quote
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.

Edited by - milo1981 on 11/03/2013 11:37:05
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/03/2013 :  13:19:15  Show Profile  Reply with Quote
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
Go to Top of Page

milo1981
Starting Member

15 Posts

Posted - 11/27/2013 :  07:19:16  Show Profile  Reply with Quote
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!

Edited by - milo1981 on 11/27/2013 11:10:25
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/27/2013 :  11:40:10  Show Profile  Reply with Quote
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
Go to Top of Page

milo1981
Starting Member

15 Posts

Posted - 11/27/2013 :  12:31:25  Show Profile  Reply with Quote
Is that line required? If I remove it, the duplicate line disappears.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000