Query Question 1

[shannanl]

I have a table (Time_T] with two fields; time_in and time_out. The fields are date format. The entries would look like this:

time_in time_out
5/7/2005 10:21:14 A.M. 5/7/2005 4:21:14 P.M.

I would like to query this table and return time_in, time_out and total_time. The total_time would be the difference, in hours and minutes. This is, as you guessed, a time and attendance thing. I tried the datediff but could not get it to work correctly. The 2nd part of the question is that we work in 15 minute increments. How can I get it to round to the nearest 15 minute increment. Such as total time would be 7.45 or 8.00, etc.

Thanks in advance for your help.

Shannan

 

[camaleonCHILE]

I hope be a help for you, regards cmn
try this:


SELECT TIME_IN,TIME_OUT,(DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4 AS HOURS,
DATEDIFF(MINUTE,DATEADD(HOUR,((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4),TIME_IN),TIME_OUT) AS MINUTES,
TOTAL_TIME=CASE WHEN DATEDIFF(MINUTE,DATEADD(HOUR,((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4),TIME_IN),TIME_OUT)=0
THEN CAST(((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4) AS VARCHAR(3)) +':00'
WHEN DATEDIFF(MINUTE,DATEADD(HOUR,((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4),TIME_IN),TIME_OUT)
BETWEEN 1 AND 15 THEN CAST((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4 AS VARCHAR(3)) + ':15' WHEN DATEDIFF(MINUTE,DATEADD(HOUR,((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4),TIME_IN),TIME_OUT)
BETWEEN 16 AND 30 THEN CAST((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4 AS VARCHAR(3)) + ':30' WHEN DATEDIFF(MINUTE,DATEADD(HOUR,((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4),TIME_IN),TIME_OUT)
BETWEEN 31 AND 45 THEN CAST((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4 AS VARCHAR(3)) + ':45' WHEN DATEDIFF(MINUTE,DATEADD(HOUR,((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4),TIME_IN),TIME_OUT)
BETWEEN 46 AND 59 THEN CAST(((DATEDIFF(minute,TIME_IN,TIME_OUT)/15)/4)+1 AS VARCHAR(3)) +':00' END
FROM TIME_T

 

[shannanl]

That works very well. I really appreciate the help.

Thanks,

Shannan