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

    Unanswered: Help with a sub query

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

    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_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
    Code:
    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
    Code:
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please describe in words what each of the columns is for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aha, console_Seq is an auto_increment

    remove that from your subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

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

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Ok, here is what I have for the data
    Code:
    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?
    Code:
    	(
    	SELECT MIN(console_Stamp)
    	FROM employee_Console
    	WHERE console_Status = 'Off'
    	AND console_Stamp > t1.console_Stamp 
    	)

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

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

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

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

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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so where are you stuck? don't tell me you're self-joining on the PK again!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

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

Posting Permissions

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