December 9, 2011 at 8:58 pm
I have two tables, Car User Log and Car List (see table1 and table2). I want to combine this two tables into one table. The output i want is show all cars and the latest driver at date who used it (see table3). Please help!
Table 1: Car User Log
CarNo Driver Date
Car1 don 12/9/2011
Car2 Neil 12/10/2011
Car2 Ramon 12/9/2011
Car2 sherwood 12/10/2011
Car3 don 12/9/2011
Car3 Neil 12/10/2011
Car4 Ramon 12/9/2011
Car4 Calz 12/10/2011
Car5 Neil 12/9/2011
Car5 Marlon 12/10/2011
Car6 null null
Table2: Car List
CarNo Fuel
Car1 Diesel
Car2 Diesel
Car3 Diesel
Car4 Diesel
Car5 Diesel
Car6 Diesel
Table3: Hope for this Output
CarNo Fuel Driver Date
Car1 Diesel Neil 12/10/2011
Car2 Diesel sherwood12/10/2011
Car3 Diesel Neil 12/10/2011
Car4 Diesel Calz 12/10/2011
Car5 Diesel Marlon 12/10/2011
Car6 Diesel null null
December 10, 2011 at 1:56 am
And what are the algorithm / logic for it?
Q1: In Table2 car list ‘Car1 Diesel’ matches to ‘Car1 don 12/9/2011’ in Table1 Car User Log but you want the output as ‘Car1 Diesel Neil 12/10/2011’. How to connect it to Neil (Car2)?
Q2: In Table2 car list ‘Car2 Diesel’ matches to 3 rows in Table1 Car User Log but you want the output as ‘Car2 Diesel sherwood 12/10/2011’. Why Sherwood ONLY?
December 11, 2011 at 4:37 pm
Sorry Sir. Tables should be like this:
Table 1: Car User Log
CarNo Driver Date
Car1 Neil 12/9/2011
Car1 Don 12/10/2011
Car2 Ramon 12/9/2011
Car2 Sherwood 12/10/2011
Car3 Don 12/9/2011
Car3 Neil 12/10/2011
Car4 Ramon 12/9/2011
Car4 Calz 12/10/2011
Car5 Neil 12/9/2011
Car5 Marlon 12/10/2011
Car6 null null
Table2: Car List
CarNo Fuel
Car1 Diesel
Car2 Diesel
Car3 Diesel
Car4 Diesel
Car5 Diesel
Car6 Diesel
Table3: Hope for this Output
CarNo Fuel Driver Date
Car1 Diesel Don 12/10/2011
Car2 Diesel Sherwood 12/10/2011
Car3 Diesel Neil 12/10/2011
Car4 Diesel Calz 12/10/2011
Car5 Diesel Marlon 12/10/2011
Car6 Diesel null null
The logic is, i want to combine this two tabes into 1 table. Car User Log will join to Car List. show all cars and the latest or last driver at date who used it. Thank you!
December 11, 2011 at 6:47 pm
Is this what you require
CREATE TABLE #Car_User_Log(CarNo VARCHAR(6), Driver VARCHAR(20), UsedDate smalldatetime)
INSERT INTO #Car_User_Log
SELECT 'Car1', 'Neil','12/9/2011' UNION ALL
SELECT 'Car1', 'Don','12/10/2011' UNION ALL
SELECT 'Car2', 'Ramon','12/9/2011' UNION ALL
SELECT 'Car2', 'Sherwood','12/10/2011' UNION ALL
SELECT 'Car3', 'Don,', '12/9/2011' UNION ALL
SELECT 'Car3', 'Neil', '12/10/2011' UNION ALL
SELECT 'Car4', 'Ramon','12/9/2011' UNION ALL
SELECT 'Car4', 'Calz','12/10/2011' UNION ALL
SELECT 'Car5', 'Neil', '12/9/2011' UNION ALL
SELECT 'Car5', 'Marlon','12/10/2011' UNION ALL
SELECT 'Car6', null, null
CREATE TABLE CarList(CarNo VARCHAR(10), Fuel VARCHAR(10))
INSERT CarList
SELECT 'Car1','Diesel' UNION ALL
SELECT 'Car2', 'Diesel' UNION ALL
SELECT 'Car3', 'Diesel' UNION ALL
SELECT 'Car4', 'Diesel' UNION ALL
SELECT 'Car5', 'Diesel' UNION ALL
SELECT 'Car6', 'Diesel'
;with numbered as(SELECT rowno=row_number() over
(partition by CarNo order by UsedDate DESC),CarNo,Driver,UsedDate from #Car_User_Log)
SELECT N.CarNo,N.Driver,N.UsedDate,CL.Fuel FROM numbered N
JOIN CarList CL
ON N.Carno = CL.CarNo
WHERE N.Rowno = 1
Results;
CarNoDriver UsedDate Fuel
Car1Don 2011-12-10 00:00:00Diesel
Car2Sherwood 2011-12-10 00:00:00Diesel
Car3Neil 2011-12-10 00:00:00Diesel
Car4Calz 2011-12-10 00:00:00Diesel
Car5Marlon 2011-12-10 00:00:00Diesel
Car6NULL NULL Diesel
December 11, 2011 at 7:52 pm
Thank you very much Sir!
December 11, 2011 at 7:55 pm
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply