Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

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

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 
      )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.


  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by fjm1967
    Thanks Georgie for your explanation. It was also good.
    Consolation prize... Score!!
    George
    Home | Blog

Posting Permissions

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