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.
| Author |
Topic |
|
alphadawg
Starting Member
3 Posts |
 Posted - 2008-11-21 : 23:12:20
|
| Hi all this is my virgin post.Pretty new to MS SQL. Using a book to learn some stuff and I have come across a problem using a join.Basically I have two tables, one is TruckIn the other is TruckOut. The trucks log a time when they arrive and leave which is kept in the respective table(along with some other info). What I am trying to do is join the two tables based on TruckID, select the last 5 log times from each table and then order the 10 records by time.I can generate the 10 records correctly but the times are in different columns and I can't figure out how to merge the column or return the time value to a single column. Is there a way to do that or any other method that will allow me to creat a 'Round Trip' Report?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-21 : 23:50:48
|
| Can you show sample data and output of what you mean by Round Trip? |
 |
|
|
alphadawg
Starting Member
3 Posts |
Posted - 2008-11-22 : 00:08:08
|
| the time is in weird format, seconds from 12/31/1969 so it appears in the table as 12079554332(I have a converter) the join produces two time columns, one for the TruckIn table, one for the TruckOut table. I want to merge the two time columns so that I may perform an order by time to show a sequential list of times out then back in(a round trip). Or, if there is another way to sort without merging the columns, that would be great too. Unfortunately I can't produce the actual data from home. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-22 : 00:24:35
|
| ok. what the purpose of joining the tables? you want to show in & out times in same row? |
|
|
|
alphadawg
Starting Member
3 Posts |
Posted - 2008-11-22 : 00:33:04
|
| I want to generate the sql code to move into a crystal report so that I can spot exceptions in the time logs for our trucks. Sometimes a truck goes out and never comes back.(so it does not complete the round trip) But then the truck goes out again.I can use Crystal Reports to show all times for all trucks and highlight when there are consecutive in or out records. This prompts me to ask the driver questions.Think of it like a time clock. An employee clocks in at the beginning of the day and the time is stored in an InTable. Then when he leaves at the end of the day he punches out, which is stored in an OutTable. At the end of the week I want to see a 'timecard' - I hope that helps. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-22 : 00:45:38
|
| still cant get your actual scenario if possible, can you post data as in instruction belowhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
Join Table and shuffle