2 Replies - 4070 Views - Last Post: 03 September 2012 - 10:27 PM

#1 jimmyo88  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 147
  • Joined: 27-February 11

Matching two dates correctly within Inner Join

Posted 31 August 2012 - 04:48 PM

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.

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


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

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

This post has been edited by jimmyo88: 31 August 2012 - 06:44 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Matching two dates correctly within Inner Join

#2 jimmyo88  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 147
  • Joined: 27-February 11

Re: Matching two dates correctly within Inner Join

Posted 02 September 2012 - 08:37 AM

I managed to fix my other problem but am now having difficulties rounding a number. When I execute the datediff function, the numbers are returned such as 7.2333333 and 5.445555555. I want them to be rounded to two decimal places. I've tried using the round() function around the datediff() function but i am getting an error saying round expects between 2 and 3 arguments.
.

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
	 , DATEDIFF (n, MAX(CASE WHEN entering_site = 1 THEN access_date END),
                     MAX(CASE WHEN entering_site = 0 THEN access_date END)) / 60.0 as hours_worked
     

  FROM access_transaction 
  WHERE worker_id = 1
 
     GROUP 
      BY worker_id
        ,  DATEPART(yyyy,access_date)
        ,  DATEPART(mm,access_date) 
        ,  DATEPART(dd,access_date)




Can anyone help woth this please?


P.S. mods, i'm not sure if i should have put this in a new thread because i've sort of answered my OP but their pretty much related.

This post has been edited by jimmyo88: 02 September 2012 - 08:38 AM

Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 799
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Matching two dates correctly within Inner Join

Posted 03 September 2012 - 10:27 PM

Have you read up on the ROUND() function? The format is this:
ROUND(numeric_expression, length)
Alternately, you could use CONVERT or CAST to convert/cast the result to a decimal type with two decimal places.

This post has been edited by e_i_pi: 03 September 2012 - 10:28 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1