SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Two Different Values from a Single Column

    I have kind of a timesheet table and I have a column for the row ID, a column for the username, a column for that status (in/out), and then a timestamp column.

    I want to select the username, then the time in and the subsequent time out. I figured out a query that will select the username, then the time in and all other out times after that, but I only want the next out time.

    Here is what I have so far -
    PHP Code:
    SELECT t1.usernamet1.statust1.tstampt2.statust2.tstamp
        FROM ta_timesheet 
    AS t1
        JOIN ta_timesheet 
    AS t2 ON t1.username t2.username
        WHERE t1
    .status "IN" AND t2.status "OUT" AND t1.tstamp t2.tstamp
        ORDER BY t1
    .ts_id ASC 
    For example, this currently displays -
    user1 - in - 2011-05-18 08:00:30 - out - 2011-05-18 09:31:25
    user1 - in - 2011-05-18 08:00:30 - out - 2011-05-19 10:00:45
    user1 - in - 2011-05-18 08:00:30 - out - 2011-05-21 14:02:25
    user1 - in - 2011-05-19 08:03:10 - out - 2011-05-19 10:00:45
    user1 - in - 2011-05-19 08:03:10 - out - 2011-05-21 14:02:25
    user1 - in - 2011-05-21 10:58:02 - out - 2011-05-21 14:02:25

    I want it to show -
    user1 - in - 2011-05-18 08:00:30 - out - 2011-05-18 09:31:25
    user1 - in - 2011-05-19 08:03:10 - out - 2011-05-19 10:00:45
    user1 - in - 2011-05-21 10:58:02 - out - 2011-05-21 14:02:25

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t1.username
         , t1.tstamp AS tstamp_in
         , t2.tstamp AS tstamp_out 
      FROM ta_timesheet AS t1  
    INNER
      JOIN ta_timesheet AS t2 
        ON t2.username = t1.username    
       AND t2.status = 'OUT' 
       AND t2.tstamp =
           ( SELECT MIN(tstamp)
               FROM ta_timesheet
              WHERE username = t1.username
                AND status = 'OUT'  
                AND tstamp > t1.tstamp )
     WHERE t1.status = 'IN' 
    ORDER 
        BY t1.username
         , t1.tstamp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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