Advanced Sql Query

[ggoosen]

Hi Guys

I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,

I have data as follows...

FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..

Second problem is i need to take this data and transform it to look like the table below

RCDID EmployeeID LogDate LogTime TerminalID InOut
41 1 07/23/2006 20:45:02 iGuard# IN
42 1 07/23/2006 20:46:17 iGuard# OUT
43 1 07/23/2006 20:48:08 iGuard# IN
44 1 07/23/2006 20:48:18 iGuard# OUT
45 1 07/23/2006 20:48:24 iGuard# IN
46 1 07/23/2006 20:48:30 iGuard# OUT
47 1 07/23/2006 20:48:36 iGuard# IN
48 1 07/23/2006 20:48:41 iGuard# OUT
50 1 07/23/2006 20:49:57 iGuard# IN
51 1 07/23/2006 20:50:14 iGuard# OUT
52 1 07/23/2006 20:59:34 iGuard# IN
53 1 07/23/2006 20:59:40 iGuard# OUT

Employee ID Date In Out In Out TotalTimeIn
1 23/07/2006 20:35 20:36 20:38 20:39 00:02


Basicaly i need to transpose it..

If anyone has the sql or knows the sql on how to do this i would be very greatful

 

[FoFa]

What does the original data look like?
You can use CDATE to convert the text date to a date value.

 

[ggoosen]

Hi fofa

if you take a look at the original post there are 2 tables the first is the original data and the second is the desired outcome

thanks

 

[ggoosen]

hi the data is in the correct format now...

so all i need is the sql query that will return my data as desirted...

thanks