Recursive SELECT paradox!!! [modified]
-
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 Voyage_ID 2 are same voyages. 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? -- modified at 9:44 Thursday 13th July, 2006
-
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 Voyage_ID 2 are same voyages. 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? -- modified at 9:44 Thursday 13th July, 2006
This will give you all duplicates:
SELECT a.*
FROM Voyage_End a, Voyage_End b
WHERE a.Voyage_ID != b.Voyage_ID AND a.platenumber = b.platenumber AND a.startdate = b.startdateBut you could use something like this to insert into your table:
SELECT (SELECT TOP 1 voyage_id
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate) as voyage_id, PlateNumber,StartDate
FROM Voyage_End b GROUP BY platenumber, startdateOr, if you don't care about the IDs, then just:
insert into voyage_end2 (platenumber, startdate) select distinct platenumber, startdate from Voyage_End
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
This will give you all duplicates:
SELECT a.*
FROM Voyage_End a, Voyage_End b
WHERE a.Voyage_ID != b.Voyage_ID AND a.platenumber = b.platenumber AND a.startdate = b.startdateBut you could use something like this to insert into your table:
SELECT (SELECT TOP 1 voyage_id
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate) as voyage_id, PlateNumber,StartDate
FROM Voyage_End b GROUP BY platenumber, startdateOr, if you don't care about the IDs, then just:
insert into voyage_end2 (platenumber, startdate) select distinct platenumber, startdate from Voyage_End
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
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?
-
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 don't understand the question. --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
I don't understand the question. --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
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?
-
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?
Would be to have driver in another table since there is an obvious 1 to many relationship between the data. However, you can write a T-SQL cursor to loop (I know, I know I am laughing as I type) through the data and create a mock data set that matches your hearts desire. It will be slow though. Split the tables make your life easy. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
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?
This isn't very pretty...and there are probably better ways to do what you want. But, if there is only ever two drivers, you can still get it all into another table with just one SQL:
insert into voyage_end2 (voyage_id,platenumber, startdate,Driver1,Driver2)
SELECT (SELECT TOP 1 voyage_id
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate) as voyage_id,
PlateNumber,StartDate,
(SELECT TOP 1 Driver1
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate order by Driver1) as Driver1,
(SELECT TOP 1 Driver1
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate and
(SELECT TOP 1 Driver1 FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate order by Driver1) != Driver1 order by Driver1 desc) as Driver2
FROM Voyage_End b
GROUP BY platenumber, startdateHowever, your best long term solution is to do as
Ennis Ray Lynch, Jr.has suggested and modify your table structure. --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters