[Frunkie]

Hi everyone,

I have code here that uses a sub query. I entered some sample data into the db and noticed that it isn't working the way I thought it would work.

Could someone please have a look? I understand perfectly what the basic sql is doing at the beginning but the sub query really throws me. I have also included some sample data from the db not calculated by this code, and then some calculated by the code. Maybe someone can tell me what is going wrong. It may just be that I am misunderstanding the results.

I was looking for this to code to function like a timecard; to record the person or employee's on time and off time and match them by date for a report. The results just don't jive..

SELECT t1.employee_Number_Seq AS employee
  , t1.console_Stamp AS 'On'
  , t2.console_Stamp AS 'Off'
FROM
 employee_Console AS t1
  Inner Join employee_Console AS t2 ON t1.employee_Number_Seq = t2.employee_Number_Seq
   AND t2.console_Stamp = 
	(
	SELECT MIN(console_Stamp)
	FROM employee_Console
	WHERE console_Seq = t2.console_Seq
	AND console_Status = 'Off'
	AND console_Stamp > t1.console_Stamp 
	)
WHERE t1.console_Status  = 'On'
AND t2.console_Status = 'Off'

Raw data

Status	Emp_Seq		by	console_Stamp		console_Seq	
Off	4		1	2007-10-23 21:28:54	22
On	4		1	2007-10-23 21:30:03	23
On	4		1	2007-10-23 21:39:43	25
Off	4		1	2007-10-23 21:41:34	26
On	4		1	2007-10-23 21:44:14	27
Off	4		1	2007-10-23 21:44:29	28
Off	9		1	2007-10-23 22:17:12	29
On	9		1	2007-10-23 22:19:51	30
Off	9		1	2007-10-23 22:20:13	31
On	9		1	2007-10-23 22:22:51	32
On	2		1	2007-10-23 22:30:51	33
Off	2		1	2007-10-23 22:33:56	34
On	2		1	2007-10-23 22:39:05	35
Off	2		1	2007-10-23 22:39:43	36
On	2		1	2007-10-23 23:08:41	37
Off	2		1	2007-10-23 23:09:08	38
On	9		1	2007-10-23 23:56:25	39

Calculated data

Emp_Seq	On			Off
4	2007-10-08 20:21:13	2007-10-23 21:28:54
4	2007-10-08 20:21:13	2007-10-23 21:41:34
4	2007-10-23 21:30:03	2007-10-23 21:41:34
4	2007-10-23 21:39:43	2007-10-23 21:41:34
4	2007-10-08 20:21:13	2007-10-23 21:44:29
4	2007-10-23 21:30:03	2007-10-23 21:44:29
4	2007-10-23 21:39:43	2007-10-23 21:44:29
4	2007-10-23 21:44:14	2007-10-23 21:44:29
9	2007-10-08 20:21:49	2007-10-23 22:17:12
9	2007-10-08 20:21:49	2007-10-23 22:20:13
9	2007-10-23 22:19:51	2007-10-23 22:20:13
2	2007-10-08 20:20:46	2007-10-23 22:33:56
2	2007-10-23 22:30:51	2007-10-23 22:33:56
2	2007-10-08 20:20:46	2007-10-23 22:39:43
2	2007-10-23 22:30:51	2007-10-23 22:39:43
2	2007-10-23 22:39:05	2007-10-23 22:39:43
2	2007-10-08 20:20:46	2007-10-23 23:09:08
2	2007-10-23 22:30:51	2007-10-23 23:09:08
2	2007-10-23 22:39:05	2007-10-23 23:09:08
2	2007-10-23 23:08:41	2007-10-23 23:09:08

It is weird because the calculated data is showing duplicate On and Off times at random. Can someone please guide me in fixing this please?

Thanks,

Frank

[r937]

could you please describe in words what each of the columns is for

[Frunkie]

Rudy, thank you.

The columns in the "raw sample data" are:

Status = The current status of the employee, "on or off work"
Emp_Seq = sequencing employee number FK from the employee table
by = the employee number of the employee changing the status above
console_Stamp = timestamp which occurs when the status is changed
console_Seq = the auto-int PK in the table.

The other columns are from the calculated data from the sql at runtime. That is what is output.

[r937]

aha, console_Seq is an auto_increment

remove that from your subquery

[Frunkie]

Rudy, your good.. That was what did the trick. Thanks!

I would like to understand why it works.. What can I research that will teach me a little more about a sub query; its the sub query that is throwing me. Do you have anything on your site that you can point me to? If I have any questions, I could always post back here.

[r937]

try running this query:

SELECT t1.employee_Number_Seq 
     , t1.console_Stamp 
     , t1.console_Status  
     , t2.console_Stamp 
     , t2.console_Status  
  FROM employee_Console AS t1
INNER
  JOIN employee_Console AS t2 
    ON t2.employee_Number_Seq = t1.employee_Number_Seq
 WHERE t1.console_Status  = 'On'
   AND t2.console_Status = 'Off'
   AND t2.console_Stamp > t1.console_Stamp
ORDER
    BY t1.employee_Number_Seq 
     , t1.console_Stamp 
     , t2.console_Stamp 

take the output, and insert some blank lines after every different t1.console_Stamp value

now examine the t2 values

then look at the code in the subquery and see if you can figure out what it would do if it were operating on the data you're looking at from this query

[Frunkie]

Ok, here is what I have for the data

2	2007-10-08 20:20:46	On	2007-10-23 22:33:56	Off 
2	2007-10-08 20:20:46	On	2007-10-23 22:39:43	Off 
2	2007-10-08 20:20:46	On	2007-10-23 23:09:08	Off 

2	2007-10-23 22:30:51	On 	2007-10-23 22:33:56	Off 
2	2007-10-23 22:30:51	On 	2007-10-23 22:39:43	Off 
2	2007-10-23 22:30:51	On 	2007-10-23 23:09:08	Off 

2	2007-10-23 22:39:05	On 	2007-10-23 22:39:43	Off 
2	2007-10-23 22:39:05	On 	2007-10-23 23:09:08	Off 

2	2007-10-23 23:08:41	On 	2007-10-23 23:09:08	Off 

4	2007-10-08 20:21:13	On 	2007-10-23 21:28:54	Off 
4	2007-10-08 20:21:13	On 	2007-10-23 21:41:34	Off 
4	2007-10-08 20:21:13	On 	2007-10-23 21:44:29	Off 

4	2007-10-23 21:30:03	On 	2007-10-23 21:41:34	Off 
4	2007-10-23 21:30:03	On 	2007-10-23 21:44:29	Off 

4	2007-10-23 21:39:43	On 	2007-10-23 21:41:34	Off 
4	2007-10-23 21:39:43	On 	2007-10-23 21:44:29	Off 

4	2007-10-23 21:44:14	On 	2007-10-23 21:44:29	Off 

9	2007-10-08 20:21:49	On 	2007-10-23 22:17:12	Off 
9	2007-10-08 20:21:49	On 	2007-10-23 22:20:13	Off 
9	2007-10-08 20:21:49	On 	2007-10-24 05:16:25	Off 

9	2007-10-23 22:19:51	On 	2007-10-23 22:20:13	Off 
9	2007-10-23 22:19:51	On 	2007-10-24 05:16:25	Off 
9	2007-10-23 22:22:51	On 	2007-10-24 05:16:25	Off 

9	2007-10-23 23:56:25	On 	2007-10-24 05:16:25	Off 

Ok, let me take a stab at what I think the sub query is doing to this data.

I see that t1 (Left) has many duplicate entries and I think that this sub query says "choose the minimum or smallest amount timestamp from exactly *where I don't know*. I know that it is the employee_Console but what happened to t1 and t2?? Why isn't that part of this sub query?

Anyway, select the smallest stamp WHERE console_Status = off. (I don't understand that because wouldn't we want the On time? The on time is smaller than the off time. If I started work at 08:00 and stopped at 15:00, the 08:00 is smaller. Wouldn't we want the On instead?

	(
	SELECT MIN(console_Stamp)
	FROM employee_Console
	WHERE console_Status = 'Off'
	AND console_Stamp > t1.console_Stamp 
	)

[r937]

thanks for displaying the data -- nice job

the "duplicate" t1 entries are the result of the one-to-many relationship of each t1 'On' with multiple t2 'Off'

notice that the t2s all have a later stamp than the t1

look closely and you can see that the same t2s are showing up for multiple t1s, and that's because the data query simply matches each t1 'On' with all t2 'Off' that have a later stamp

so what the subquery does is pick the lowest stamp of all the Off stamps which are a later stamp, for the same employee

t1 is mentioned in the subquery (which makes it correlated), but not t2 -- in effect, the table in the subquery is t3

[Frunkie]

Wow.. I totally missed the duplicate values in t2's column but your right. Those values also repeat themselves.

I'm gonna be honest.. it is still a bit hazy and maybe it is because I have been awake all night again, but I am going to look at this again when I get up.

Also, I have a very simple query of 2 tables; actually a self join that I am working on. It sort of resembles the two tables in the query that you just helped me with but I cannot get the thing to work... I'll post it when I get up.

Is there a trick to knowing exactly what type of query to build or is it just experience?

Thanks Rudy!

[Frunkie]

SELECT
  t1.emp_Status
, t1.emp_Seq
, t1.status_Stamp AS arrive
, t2.status_Stamp AS depart
FROM
  emp_Status AS t1
    Inner
      Join emp_Status AS t2
          ON t2.emp_Status_Seq = t1.emp_Status_Seq

This is the self join that I have been able to get to work so far.. I don't know what to do from here though. I would like this query to tell me how long an employee stays at particuar location by finding the difference in time between the two status_Stamps.

This query looks like it would be almost identical to the last one and I tried to duplicate it but couldn't get it working. I thought I would post it and actually try to *learn* how to do it instead of "trying whatever" to get it to work. Maybe if I try a step by step method I may have better luck.

What would be my next step? I know that I need a self join and I know that the column I need to calculate my times with, are now both in the query.

The emp_Status is a column that says "arrive" or "depart"
The stamp is the timestamp
The emp_Seq is the employee number

[r937]

hiya frank, how was your nap -- i was waiting for ya!

so, how/why is this different from the On/Off previous example? is emp_Status related to emp_Console?

[Frunkie]

Originally Posted by r937

hiya frank, how was your nap -- i was waiting for ya!

so, how/why is this different from the On/Off previous example? is emp_Status related to emp_Console?

Hey Rudy, it was a longer nap than I anticipated.

Well, I don't see it any different than the last query but when I duplicated it and couldn't get it working, I just figured that maybe it really isn't the same type of query at all. I hate being sql impared.

Yes, emp_Status is the parent table to emp_Console and employee is also the parent table to emp_Console.

[r937]

so where are you stuck? don't tell me you're self-joining on the PK again!!

[Frunkie]

SELECT
ROUND(AVG(TIMEDIFF(arrive, depart))) / 60 AS diff
FROM emp_Status AS t1
WHERE t1.emp_Seq = '1'

This was the code I *was using* before I broke up the table. This was working great..

[Frunkie]

Originally Posted by r937

so where are you stuck? don't tell me you're self-joining on the PK again!!

I was Rudy.. Let me try joining on a different column.