#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    Help Joining two dates within the same table


    I was wondering if anyone could help me. Im trying to create a query which shows the start time and end time for a worker and calculates the hours they've worked.

    Initially, the values in the table are set out like this. What makes this tricky is that each time a worker enters and leaves, a new row is created.

    Code:
    SELECT * FROM entry WHERE w_id = 60
    
    w_id             access_time    entering_site      access_id
    60	2011-05-29 07:01:19.000	1	          1
    60	2011-05-29 10:24:38.000	0	          68
    60	2011-05-30 13:07:41.000	1	          322
    60	2011-05-30 18:57:44.000	0	          389
    60	2011-05-31 11:35:28.000	1	          484
    60	2011-05-31 14:01:49.000	0	          542
    60	2011-06-01 11:40:23.000	1	          692
    60	2011-06-01 17:42:21.000	0	          786
    60	2011-06-02 07:37:21.000	1 	          809
    60	2011-06-02 08:13:20.000	0	          819
    This is what i've come up with

    Code:
    SELECT     access_transaction.worker_id, t.entering_site,
    		   t.access_date AS start_time, 
    		   access_transaction.entering_site AS exiting_site,  
    			access_transaction.access_date AS finish_time
    FROM       access_transaction AS T 
    
    INNER JOIN access_transaction
    ON         access_transaction.worker_id = t.worker_id AND t.entering_site = 1
    WHERE      access_transaction.entering_site = 0
    and these are the results

    Code:
    w_id  entering         start_time      exiting           exit_time
    29	1	2011-05-29 07:06:31.000	0	2011-05-29 07:16:35.000
    29	1	2011-05-30 11:42:13.000	0	2011-05-29 07:16:35.000
    29	1	2011-05-31 09:57:53.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-01 12:24:41.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-02 09:05:49.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-03 12:49:08.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-28 11:51:19.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-22 11:25:31.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-23 10:11:18.000	0	2011-05-29 07:16:35.000
    29	1	2011-06-24 09:54:33.000	0	2011-05-29 07:16:35.000
    
    EDIT:: The formattings messed up the column names a bit, these should be
    w_id, entering, start_time, exiting , exit_time
    The problem is that the start times increment correctly by date but the exit times do not. Can anyone help fix this please?

    EDIT:: I've been using distinct on the access_id but but am still getting trouble comparing the dates.

    Many thanks
    Jimmy
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Does every 'entry' have a corresponding 'exit'?

    And vice versa?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Yes sorry, I should have. been more clear about that. Also every worker only signs in and out no more than once per day.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    In ths particular case, you don't need any joins at all!
    Code:
    DROP TABLE IF EXISTS entry;
    
    CREATE TABLE entry 
    ( worker_id INT NOT NULL
    , access_time DATETIME NOT NULL
    , entering_site TINYINT NOT NULL
    , access_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    );
    
    INSERT INTO entry VALUES
    (60,'2011-05-29 07:01:19',1,1),
    (60,'2011-05-29 10:24:38',0,68),
    (60,'2011-05-30 13:07:41',1,322),
    (60,'2011-05-30 18:57:44',0,389),
    (60,'2011-05-31 11:35:28',1,484),
    (60,'2011-05-31 14:01:49',0,542),
    (60,'2011-06-01 11:40:23',1,692),
    (60,'2011-06-01 17:42:21',0,786),
    (60,'2011-06-02 07:37:21',1,809),
    (60,'2011-06-02 08:13:20',0,819),
    
    (29,'2011-05-29 07:06:31',1,NULL),
    (29,'2011-05-29 17:16:35',0,NULL),
    
    (29,'2011-05-30 11:42:13',1,NULL),
    (29,'2011-05-30 19:42:13',0,NULL),
    
    (29,'2011-05-31 09:57:53',1,NULL),
    (29,'2011-05-31 18:57:53',0,NULL),
    
    (29,'2011-06-01 12:24:41',1,NULL),
    (29,'2011-06-01 20:24:41',0,NULL),
    
    (29,'2011-06-02 09:05:49',1,NULL),
    (29,'2011-06-02 15:05:49',0,NULL),
    
    (29,'2011-06-03 12:49:08',1,NULL),
    (29,'2011-06-03 18:49:08',0,NULL),
    
    (29,'2011-06-28 11:51:19',1,NULL),
    (29,'2011-06-28 20:51:19',0,NULL),
    
    (29,'2011-06-22 11:25:31',1,NULL),
    (29,'2011-06-22 21:25:31',0,NULL),
    
    (29,'2011-06-23 10:11:18',1,NULL),
    (29,'2011-06-23 20:11:18',0,NULL),
    
    (29,'2011-06-24 09:54:33',1,NULL),
    (29,'2011-06-24 19:54:33',0,NULL);
    
    
    SELECT worker_id
         , MAX(CASE WHEN entering_site = 1 THEN access_time END) `in`
         , MAX(CASE WHEN entering_site = 0 THEN access_time END) `out` 
      FROM entry 
     GROUP 
        BY worker_id
         , DATE(access_time);
         
    +-----------+---------------------+---------------------+
    | worker_id | in                  | out                 |
    +-----------+---------------------+---------------------+
    |        29 | 2011-05-29 07:06:31 | 2011-05-29 17:16:35 |
    |        29 | 2011-05-30 11:42:13 | 2011-05-30 19:42:13 |
    |        29 | 2011-05-31 09:57:53 | 2011-05-31 18:57:53 |
    |        29 | 2011-06-01 12:24:41 | 2011-06-01 20:24:41 |
    |        29 | 2011-06-02 09:05:49 | 2011-06-02 15:05:49 |
    |        29 | 2011-06-03 12:49:08 | 2011-06-03 18:49:08 |
    |        29 | 2011-06-22 11:25:31 | 2011-06-22 21:25:31 |
    |        29 | 2011-06-23 10:11:18 | 2011-06-23 20:11:18 |
    |        29 | 2011-06-24 09:54:33 | 2011-06-24 19:54:33 |
    |        29 | 2011-06-28 11:51:19 | 2011-06-28 20:51:19 |
    |        60 | 2011-05-29 07:01:19 | 2011-05-29 10:24:38 |
    |        60 | 2011-05-30 13:07:41 | 2011-05-30 18:57:44 |
    |        60 | 2011-05-31 11:35:28 | 2011-05-31 14:01:49 |
    |        60 | 2011-06-01 11:40:23 | 2011-06-01 17:42:21 |
    |        60 | 2011-06-02 07:37:21 | 2011-06-02 08:13:20 |
    +-----------+---------------------+---------------------+
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Thanks a lot for the reply cafelatte. Being an idiot, I realised I have posted this in the mySQL section insead of the Server sql section. Thats what happens when your up at 5am doing these things!!

    When i use this code, the code compiles correctly if i remove the group by clause Date(access_date). However, only a 100 rows are retrieved (one for each worker) and only one date for each worker is retrieved.

    Code:
    SELECT worker_id
         , MAX(CASE WHEN entering_site = 1 THEN access_date END) AS in_time
         , MAX(CASE WHEN entering_site = 0 THEN access_date END) AS out_time
    
      FROM access_transaction 
     GROUP 
        BY worker_id
         , DATE(access_date);
    With the group by DATE clause left in, the compiler throws an error about DATE not being a function. Is this because DATE is mySQL syntax or something along those lines. Is there a simple alternative for SQL server. Sorry about the mix up and thanks for all your help.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I don't know about $QL.

    However, a well-known search engine suggests that you might be able to do something like this...
    Code:
    GROUP 
          BY worker_id
            ,  DATEPART(yyyy,access_date)
            ,  DATEPART(mm,access_date) 
            ,  DATEPART(dd,access_date)

IMN logo majestic logo threadwatch logo seochat tools logo