RECURSIVE SELECT STATEMENT..
-
Hi Folks,
I have a database table named Voyage_History that stores the voyage information of our company's vehicles. I have imported data from an MS Excel file and finally I had get the result as screen below:
---------------------------------------
SELECT Voyage_ID,PlateNumber,StartDate from Voyage_End as ddd
Group By Voyage_ID,PlateNumber,StartDate
Voyage_ID PlateNumber StartDate
----------- ----------- ------------------------------------------------------
1 34 DD 0254 2005-12-28 00:00:00
2 34 DD 0254 2005-12-28 00:00:00
3 34 DD 0254 2005-12-30 00:00:00
4 34 DD 0254 2005-12-31 00:00:00
5 34 DD 0254 2006-01-01 00:00:00
6 34 DD 0254 2006-01-04 00:00:00
7 34 DD 0254 2006-01-05 00:00:00
8 34 DD 0254 2006-01-05 00:00:00
9 34 DD 0254 2006-01-06 00:00:00
10 34 DD 0254 2006-01-06 00:00:00
11 34 DD 0254 2006-01-12 00:00:00
12 34 DD 0254 2006-01-13 00:00:00
13 34 DD 0254 2006-01-16 00:00:00
14 34 DD 0254 2006-01-17 00:00:00
15 34 DD 0254 2006-01-17 00:00:00
16 34 DD 0254 2006-01-21 00:00:00
17 34 DD 0254 2006-01-23 00:00:00
18 34 DD 0254 2006-01-26 00:00:00
------------------------------------------------
In fact, voyage_ID 1 and two are same voyage. Because all data is same.
How can I create another table that stores unique Voyage_ID, and the most important part of the question how can I import this data correctly? ( I mean how to determine the same voyages)
Should I have apply a recursive select statement?
-
Hi,
Assuming that it is PlateNumber and StartDate that determine a unique voyage then you can simply do the following.
insert into somenewtable
select distinct PlateNumber, StartDate
from Voyage
Hope that helps,
-
SELECT z.Voyage_ID,
myt.PlateNumber,
myt.StartDate
FROM (
SELECT MAX(Voyage_ID) Voyage_ID -- Or MIN if you prefer
FROM MyTable
GROUP BY PlateNumber,
StartDate
) z
INNER JOIN MyTable myt ON myt.Voyage_ID = z.Voyage_ID
N 56°04'39.16"
E 12°55'05.25" -
If you only want unique Voyage_ID, this is the query to use
SELECT MAX(Voyage_ID) Voyage_ID -- Or MIN if you prefer
FROM MyTable
GROUP BY PlateNumber,
StartDate
N 56°04'39.16"
E 12°55'05.25" -
Wow...really good results...
Is it possible to move a column from second row to first row? I mean suppose that I made a basic select operation and got the result as below:
---------------------------------------------------------
SELECT Voyage_ID,StartDate,PlateNumber,Driver1 FROM voyage_END
order by startdate
Voyage_ID StartDate PlateNumber Driver1
421 2005-12-28 34 VS 0845 X
750 2005-12-28 34 VS 0845 Y
----------------------------------------------------------------
The MAJOR problem is:
How can I create a new table such as:
Voyage_ID StartDate PlateNumber Driver1 Driver2
421 2005-12-28 34 VS 0845 X Y
difficult case...right?
-
I wouldn't say that this is advisable (or the best way to do it necessarily) but you could do it like so:
This assumes that you will always have 2 rows for each voyage (one for each driver).
SELECT z.Voyage_ID,
myt.PlateNumber,
myt.StartDate,
(select Driver from MyTable where myt.PlateNumber = PlateNumber
and myt.StartDate = StartDate and Voyage_Id = z.VoyageId) as Driver1,
(select Driver from MyTable where myt.PlateNumber = PlateNumber
and myt.StartDate = StartDate and Voyage_Id z.VoyageId) as Driver2
FROM (
SELECT MAX(Voyage_ID) Voyage_ID
FROM MyTable
GROUP BY PlateNumber,
StartDate
) z
INNER JOIN MyTable myt ON myt.Voyage_ID = z.Voyage_ID
Personally, I'd look at redesigning the tables to more closely fit the design you're trying to model.
For example, I'd have a Voyage table as above but without the driver column, and a seperate Voyage_Driver table with a foreign key on Voyage_Id to the Voyage table.
That way, every record in the Voyage table is unique. By including Driver1 and Driver2 as columns in the Voyage table you're limiting the design to only ever allow for 2 drivers. What happens if in future you want to model voyages that have more than 2 drivers. It's best to design as generic a design to begin with.
Anyway, that's just food for thought.
Hope this helps,
-
The problem is some voyages include one driver and other voyages include two drivers.
I have already designed a database architecture that works well. And this is final step.If I could import the data properly then the problem will be solved..
And..Really thank you for your attention again..
-
The above query might still work then - as long as you don't have more than 2 drivers. If there is only 1 driver then the Driver2 subquery will return null.
-
No, it is not difficult at all.
I don't like subqueries much (like SQLZ's solution which do the job), because they take too much time to execute.
Derived tables however I like
Try this code for fun SELECT z.Voyage_ID,
z.PlateNumber,
z.StartDate,
z.Driver1,
CASE WHEN z.Driver2 <> z.Driver1 THEN z.Driver2 END Driver2
FROM (
SELECT StartDate,
PlateNumber,
MIN(Voyage_ID) Voyage_ID,
MIN(Driver1) Driver1,
MAX(Driver1) Driver2
FROM MyTable
GROUP BY StartDate,
PlateNumber
) z
Good luck!
Try my solution and SQL's solution and time them. Use the code below and post the results here...
DECLARE @t DATETIME
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @t = GETDATE()
SELECT z.Voyage_ID,
z.PlateNumber,
z.StartDate,
z.Driver1,
CASE WHEN z.Driver2 <> z.Driver1 THEN z.Driver2 END Driver2
FROM (
SELECT StartDate,
PlateNumber,
MIN(Voyage_ID) Voyage_ID,
MIN(Driver1) Driver1,
MAX(Driver1) Driver2
FROM MyTable
GROUP BY StartDate,
PlateNumber
) z
PRINT 'Peso solution took ' + STR(DATEDIFF(ms, @t, GETDATE() ), 10) + ' ms'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @t = GETDATE()
SELECT z.Voyage_ID,
myt.PlateNumber,
myt.StartDate,
(
select Driver
from MyTable
where myt.PlateNumber = PlateNumber
and myt.StartDate = StartDate
and Voyage_Id = z.VoyageId
) as Driver1,
(
select Driver
from MyTable
where myt.PlateNumber = PlateNumber
and myt.StartDate = StartDate
and Voyage_Id <> z.VoyageId
) as Driver2
FROM (
SELECT MAX(Voyage_ID) Voyage_ID
FROM MyTable
GROUP BY PlateNumber,
StartDate
) z
INNER JOIN MyTable myt ON myt.Voyage_ID = z.Voyage_ID
PRINT 'Sqlz solution took ' + STR(DATEDIFF(ms, @t, GETDATE() ), 10) + ' ms'
N 56°04'39.16"
E 12°55'05.25" -
I agree, subqueries are cheap and nasty and it was the first thought that came to me. Now that I see you using min and max to get the Driver it seems obvious but at the time it didn't occur to me because of the Driver not being a numeric (I have my stupid moments).
Neat solution
. -
I came to the office right now and applied your solution immediately.
It works like a well designed machine! Thank you for all...And SQLZ, thank you too!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply