Query that is not working properly. Gets some rows and not others.

Frunkie · #1 (**permalink**) 10-25-07, 08:25

I have a table called employee_Console. This table holds information on employees that start and end work, just like a timecard would.

I am using this query

Code:

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_Status = 'Off'
	AND console_Stamp > t1.console_Stamp 
	)
WHERE t1.console_Status  = 'On'
AND t2.console_Status = 'Off'

The really strange thing about this query is that sometimes it will get the paired On and Off times and other times it won't -- even though there is data. Here is test data from the employee_Console table

Code:

status	Emp#		by	stamp			console_Seq
On	1		1	2007-10-25 00:41:22	1
On	2		1	2007-10-25 00:32:46	2
Off	1		1	2007-10-25 00:42:15	3
Off	2		1	2007-10-27 00:44:33	4
On	4		1	2007-10-25 00:45:37	5
On	7		1	2007-10-25 00:46:07	6
Off	4		1	2007-10-25 00:47:13	7
Off	7		1	2007-10-25 00:47:32	8
On	9		1	2007-10-25 00:52:45	9
Off	9		1	2007-10-25 00:53:09	10
On	1		1	2007-10-25 01:04:55	11
Off	1		1	2007-10-25 01:05:05	12

Here is the part I don't get. The data is below is what the query produced. Please notice that only employee numbers 1, 4, 9 and 1 are listed below but.. if you look at the test data above, you will see that the query completely missed the data for employees 2 and 7. Also, please notice that there is an "On" and "Off" stamp for both employees 2 and 7. Even though the data appears above, the query misses them for some reason.

Code:

1	2007-10-25 00:41:22	2007-10-25 00:42:15
4	2007-10-25 00:45:37	2007-10-25 00:47:13
9	2007-10-25 00:52:45	2007-10-25 00:53:09
1	2007-10-25 01:04:55	2007-10-25 01:05:05

r937 · #2 (**permalink**) 10-25-07, 08:48

subquery is missing a correlation to the same employee

Code:

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

gvee · #3 (**permalink**) 10-25-07, 08:52

For the benefit of anyone wanting to try this out too here is some "pick up and go" code

Code:

DECLARE @employee_Console table (
    console_status char(3)
  , employee_Number_Seq int
  , console_Stamp datetime
)
INSERT INTO @employee_Console (console_status, employee_Number_Seq, console_Stamp)
SELECT 'On' , 1, '2007-10-25 00:41:22' UNION ALL
SELECT 'On' , 2, '2007-10-25 00:32:46' UNION ALL
SELECT 'Off', 1, '2007-10-25 00:42:15' UNION ALL
SELECT 'Off', 2, '2007-10-27 00:44:33' UNION ALL
SELECT 'On' , 4, '2007-10-25 00:45:37' UNION ALL
SELECT 'On' , 7, '2007-10-25 00:46:07' UNION ALL
SELECT 'Off', 4, '2007-10-25 00:47:13' UNION ALL
SELECT 'Off', 7, '2007-10-25 00:47:32' UNION ALL
SELECT 'On' , 9, '2007-10-25 00:52:45' UNION ALL
SELECT 'Off', 9, '2007-10-25 00:53:09' UNION ALL
SELECT 'On' , 1, '2007-10-25 01:04:55' UNION ALL
SELECT 'Off', 1, '2007-10-25 01:05:05'

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_Status = 'Off'
	AND    console_Stamp > t1.console_Stamp 
	)
WHERE t1.console_Status  = 'On'
AND   t2.console_Status  = 'Off'

EDIT: Sniped by Rudy.

Actually, he gave the answer and I was still busy thunking

Frunkie · #4 (**permalink**) 10-25-07, 09:16

Wow. Rudy, it appears to work.. I'm not even gonna ask how you figured that out.

George.. what the hay is that?

EDIT:

Rudy, thank you so much!

gvee · #5 (**permalink**) 10-25-07, 09:36

It was what I wrote so I could toy with it and find the answer. It re-creates your scenario given the sample.

It's so others could try the problem at home too.

The answer seems so obvious now it's been pointed out; Rudy finds this stuff way to easy

Frunkie · #6 (**permalink**) 10-25-07, 09:43

Quote:

Originally Posted by georgev

It was what I wrote so I could toy with it and find the answer. It re-creates your scenario given the sample.

It's so others could try the problem at home too.

The answer seems so obvious now it's been pointed out; Rudy finds this stuff way to easy

Well.. I wish I could see it.. Why don't you do me a favor and try to explain it to me please. I think I wore Rudy out with all of my questions.

gvee · #7 (**permalink**) 10-25-07, 10:08

The subquery on the join has no relation to the outer tables.

Code:

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

The highlighted above will look for the very smallest value, whereas you want the smallest value for each employee_seq pair in t1.

I'm not so good when it comes to explaining these bits - but I hope that helps instead of confuses further

r937 · #8 (**permalink**) 10-25-07, 10:21

the subquery that you had was returning the t2 row only when it happened to be the next one, for any employee, that was greater than the t1

simple, really

Frunkie · #9 (**permalink**) 10-25-07, 10:52

Quote:

Originally Posted by r937

the subquery that you had was returning the t2 row only when it happened to be the next one, for any employee, that was greater than the t1

simple, really

For some reason, VBulletin is not emailing me any updates on my subscriptions.. Anyway,

Yes, your right, the solution was easy because you know what you are doing.

I understand most of the query but after the inner join, things go south for me. I have never seen a query that uses a constrain like AND...
Inner Join .... AND --never seen it before. I always thought the AND was part of the WHERE clause.

Anyhow, Rudy, thank you very much for all of your help and I am now going to retire with my oversized wife where I can dream about sql for about the next 5 to 6 hours.

Thanks Georgie for your explanation. It was also good.