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

Frunkie · #1 (**permalink**) 10-01-07, 09:56

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

r937 · #2 (**permalink**) 10-01-07, 10:36

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?

aschk · #3 (**permalink**) 10-01-07, 10:42

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 · #4 (**permalink**) 10-01-07, 10:44

Now tell us what you expect to be getting from your summation result and we'll tell you where you're going wrong

Frunkie · #5 (**permalink**) 10-01-07, 10:53

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!

Frunkie · #6 (**permalink**) 10-01-07, 10:54

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.

r937 · #7 (**permalink**) 10-01-07, 11:11

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?

Frunkie · #8 (**permalink**) 10-01-07, 20:07

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.

r937 · #9 (**permalink**) 10-01-07, 23:21

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...

Frunkie · #10 (**permalink**) 10-01-07, 23:50

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.

r937 · #11 (**permalink**) 10-02-07, 00:29

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

Frunkie · #12 (**permalink**) 10-02-07, 01:08

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.

Frunkie · #13 (**permalink**) 10-02-07, 01:16

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.

Frunkie · #14 (**permalink**) 10-02-07, 02:04

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.

r937 · #15 (**permalink**) 10-02-07, 02:57

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?