0

I need to group this SQL string by the hour for one USER_ID but I am unsure how to use the DATEPART function I've been reading about. Basically, I want to get the performance data for every hour of this user from the time they start work till the time they go home. If the user comes in at 6am I want to see what their performance data is at 7 am,8am,9am...etc grouped vertically so that I can import the data into excel and make a chart.

-------EmpPerf--------

select u.user_id, SUM(tl.elapsed_time) AS ELAPSED, SUM(CASE WHEN 
(tl.standard_time = 0) THEN 0 ELSE (tl.elapsed_time) END)AS PERFTIME, 
SUM(tl.standard_time)as Standard_time

from perfplus.tale tl, perfplus.task tk, perfplus.users u

WHERE tl.task_id = tk.ID
AND tl.facility_id = tk.facility_id
AND tk.start_time BETWEEN {ts '2017-05-04 04:00:00'} and {ts '2017-05-05 03:59:59'}
AND tk.facility_id = '130'
AND tk.user_id = u.user_id
AND u.shift_name NOT IN ('9WMS','9','T2016','T2017')
and tk.status like ('TS_PROCESSED')
and u.user_id = 'LBRSHALL'

group by u.user_id
Chris Hall
  • 11
  • 2

1 Answers1

0

Got it figured out:

-------EmpPerf--------

select hour(tk.start_time), SUM(tl.elapsed_time) AS ELAPSED, SUM(CASE WHEN 
(tl.standard_time = 0) THEN 0 ELSE (tl.elapsed_time) END)AS PERFTIME, 
SUM(tl.standard_time)as Standard_time
from perfplus.tale tl, perfplus.task tk, perfplus.users u
WHERE tl.task_id = tk.ID
AND tl.facility_id = tk.facility_id
AND tk.start_time BETWEEN {ts '2017-05-04 04:00:00'} and {ts '2017-05-04 
23:00:00'}

AND tk.facility_id = '130'
AND tk.user_id = u.user_id
AND u.shift_name NOT IN ('9WMS','9','T2016','T2017')
and tk.status like ('TS_PROCESSED')
and u.user_id = 'LBRSHALL'

group by hour(tk.start_time)
order by hour(tk.start_time)
Chris Hall
  • 11
  • 2