Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Is it possible to create a comparason query on one field?

    Hello everyone.

    Is it possible to create a comparason query off of one field? I have a timestamp field in a table and another field describing that row.

    Code:
    employee	status			timestamp
    1		location1		2007-10-01 10:00:01
    1		location2		2007-10-01 11:00:03
    1		location3		2007-10-01 12:34:29
    WHERE status
    IN( 'location1', 'location2', 'location3' ) AND
    t1.employee = '3'

    I would like to add (SUM()) the times say between location1 and location2 or location2 and location3 based on the status column. Is this at all possible?

    Thanks.

    Frank

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure what you want, sorry frank

    you cannot sum timestamps (well, you can try, but it'll be garbage)

    do you want differences between consecutive timestamp values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Hi Frank,

    How do you propose doing this? In a timestamp you have a year month day hour minute second.
    So my question to you is, how do you add up 13-05-2007 14:54:27 and 14-08-2004 01:45:21 .
    By my "guess" calculation I would say this adds up to be the 27th of the 13th month of the year 4011 at 16:39:48 , so rounding the month around you have
    27-01-4001 16:39:48 .
    Now do you see why summing timestamps is nonsense?

    p.s. the date component of my timestamps i've provided above are in GMT format dd-mm-yyyy

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Now tell us what you expect to be getting from your summation result and we'll tell you where you're going wrong

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    not sure what you want, sorry frank

    you cannot sum timestamps (well, you can try, but it'll be garbage)

    do you want differences between consecutive timestamp values?
    Well, I have two issues with two different types of queries and both of them have to do with timestamps.

    The first query is to calculate the total hours worked in a day then total the hours at the end of the week. Like a timecard.

    The second query is the one I tried to explain here, today. What I wanted to do was find the difference between two times. What is confusing me is that that both times needs to come out of one field in the table. I have a type field that differentiates the two. If there were two stamps, I could simply find the difference between the two fields, but because I am using a type table to distinguish between them, I don't even know if it can even be done, or how for that matter.

    Would you recommend that I add another stamp to the table?

    BTW: I thought I would get fancy by adding the type column and I am seeing that I ruined the identity in that table. (again with identity..)

    Thanks again Rudy!

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by aschk
    Hi Frank,

    How do you propose doing this? In a timestamp you have a year month day hour minute second.
    So my question to you is, how do you add up 13-05-2007 14:54:27 and 14-08-2004 01:45:21 .
    By my "guess" calculation I would say this adds up to be the 27th of the 13th month of the year 4011 at 16:39:48 , so rounding the month around you have
    27-01-4001 16:39:48 .
    Now do you see why summing timestamps is nonsense?

    p.s. the date component of my timestamps i've provided above are in GMT format dd-mm-yyyy
    aschk,

    you can use the TIMDIFF() function. It has not yet failed me.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    frank, a time diff is not the same as a time sum

    so what do you want, difference in consecutive timestamps?

    for which statuses, any?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Rudy, I'm sorry but I think I was using the wrong word (SUM) in post #1.

    I believe that what I need would be the difference in time, using the timestamp to get the total number of hours worked in a day. Again, I can't see a way to get two times from the same column.

    Code:
    employee	status			timestamp
    1		On		2007-10-01 08:00:01
    1		Off		2007-10-01 16:00:03
    So using the above sample data, I would want to find the difference in time between the "On" and "Off" stamps. That will tell me the total number of hours worked. Then, total all of the "hours" or the result for a grand total of hours worked in a given period.

    The other query that was giving me a problem was almost identical to the above data. I want to also find the difference in time.

    Code:
    employee	status			timestamp
    1		Arrive		2007-10-01 09:20:01
    1		Depart		2007-10-01 10:05:03
    Thanks Rudy.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you always want to do the calculation for a specific date?

    what if there are two Ons before the Off? what if there's an On without an Off? what if there are two perfectly good pairs of On and Off in the same date?

    the query will involve a self-join, so you could look that up while you're getting your head around all the On/Off possibilities...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    do you always want to do the calculation for a specific date?
    No, for a range of dates that the user can specify. For example:
    Starting 2007-09-03
    Ending 2007-09-17

    Quote Originally Posted by r937
    what if there are two Ons before the Off?
    I was thinking that there should always be a matching "off" to an "on". I suppose that there could be a data entry error or maybe someone forgot to enter a status for a particular employee. I'm not sure Rudy; I don't know how to handle that. Is there a better way to model it? Remember that status table that I needed help with last week? This is the same table. I figured that since I already had the employee and the employee's work status already in that table that I could also use that table for timecard calculation as well. I didn't want to create another table.

    Quote Originally Posted by r937
    what if there's an On without an Off? what if there are two perfectly good pairs of On and Off in the same date?
    Would it be possible to *only calculate* on matches and discard the rows that only have one "On" or one "Off"?

    Quote Originally Posted by r937
    the query will involve a self-join, so you could look that up while you're getting your head around all the On/Off possibilities...
    Ok, thanks for the head start.. I will look that up now.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    Remember that status table that I needed help with last week?
    nope, sorry

    my answers are like the web -- stateless



    what process inserts into this table? that's where the data integrity checking should be done, on input, not when you are selecting for analysis on output
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    nope, sorry

    my answers are like the web -- stateless



    what process inserts into this table? that's where the data integrity checking should be done, on input, not when you are selecting for analysis on output
    Ok..

    I have not yet programmed the ability to insert or update this table but I can't see where I would be able to ensure integrety with my application code having nothing more than a timestamp, employee_Number and status field.

    I think I could however, enforce integrety if I got rid of the type column and in its place put two columns; one for begin_shift (timestamp) and end_shift (timestamp). That way, I can enforce the integrety of the data by not allowing someone to enter a new row of data until the prior row for that employee has a begin and end_shift time.

    Am I correct on this?

    I also read up on self joins. I understand them to be simply a duplicate table that is compared to itself.

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Disregard that Rudy. Yes, I would be able to enforce integrety on that table exactly the way it is. I would just use a logical operator to make sure that a start and end status were present before allowing a new row of data.

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I have been playing around with a self join but I can't get it to return the correct results.

    Code:
    SELECT e.console_Status AS 'status'
    , e.employee_Number_Seq AS 'employee'
    , m.console_Stamp AS 'timestamp'
    FROM employee_Console AS e
       LEFT OUTER
          JOIN employee_Console AS m
            ON e.console_Seq = m.console_Seq
      WHERE
        e.console_Status =  'In' AND
        m.console_Status =  'Out'
    I think my problem is in my WHERE clause.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's look at a number of things

    first, it should be an INNER JOIN, because you don't really want an In without its matching Out, right?

    next, if there are multiple In/Out pairs (as there will be over a span of several days), then you want to match each In with only one Out -- namely the next one

    this will require a subquery, which will ensure that the Out being joined is the one with the lowest timestamp that's greater than the timestamp of the In

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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