Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: Transpose Data Using SQL

    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

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    do you have a finite definable number of in and outs for an employeee for a day?

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    Hi...

    we do have a number that we are working on and that is 4 in clock and 4 out clocks so a total of 8 clocks... when i say that we are working on one i mean that the max we want to display will be 8 clocks...

    but if it is simpliar to just use whatever there is then we should do so...

  4. #4
    Join Date
    Jul 2006
    Posts
    22
    Hey, I had that problem when developing the software that work with the electronic clocks to control employees asistance.

    Well, some solutions here. Lets go the cursors way:

    With a cursor you can navigate the Clock table and pick up each data field make validations, calculations, etc. and then put in the Transposed table:

    First recommend to insert the employees records corresponding to a specific date:
    insert into TransposedTable (EmployeeId, Date) Select distinct EmployeeId, LogDate from ClockTable where LogDate=@theDate

    then the cursor thing:

    declare clocks cursor for select * from ClockTable where LogDate=@theDate

    open clocks

    fetch next from clocks into @RCDID, @EmployeeID, @LogDate, @LogTime, @TerminalID, @InOut
    set @i = 1
    while @@fetch_satatus=0
    begin
    if @i = 1
    update TransposedTable set In1 = @LogTime where employeeid=@employeeid and Date=@LogDate
    if @i= 2
    update TransposedTable set Out1 = @LogTime where employeeid=@employeeid and Date=@LogDate
    if @i = 3
    update TransposedTable set In2 = @LogTime where employeeid=@employeeid and Date=@LogDate
    ...
    ...
    ...
    fetch next from clocks into @RCDID, @EmployeeID, @LogDate, @LogTime, @TerminalID, @InOut
    set @i = @i +1
    end
    ...


    Within the While-bucle you can make the calculations of TotalTimeIn and others to use in updates.

    All this stuff can go within a stored procedure.

    Respect the data types you can convert cast(LogDate+' '+LogTime as datetime)

    Hope this help. Ask if need more explanations.
    Last edited by carloco; 07-26-06 at 19:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •