0

I have been thrown in over my head with some MS SQL work and am humbly requesting assistance. I am learning the basics but was given an assignment far above my skill level. I have been asked to provide a query that will show how many hours have been worked for each jobcode using our timeclock database. Below is a sample of the table.

employeeid  Jobcode      TimeIn               TimeOut
18405          4    2014-08-08 06:55:00 2014-08-08 09:14:00
19000          4    2014-08-08 09:27:00 2014-08-08 11:04:00
20205          4    2014-08-08 11:33:00 NULL
18406         13    2014-08-08 06:57:00 2014-08-08 09:01:00
18405         13    2014-08-08 09:16:00 2014-08-08 11:03:00
18406         13    2014-08-08 11:33:00 NULL
18407         19    2014-08-08 08:25:00 2014-08-08 12:03:00
18411         19    2014-08-08 07:59:00 2014-08-08 11:01:00

What I want to be displayed is the total hours for each jobcode at the time the query is run. 

 Job Code     Minutes
    1         500
    2         700
    3         200

Here is the query I working with :

select eh.jobcode,  datediff(mi, eh.TimeIn, isnull(eh.TimeOut, DateAdd(HH,2,Getdate())))   as Minutes

FROM [TimeClockPlus].[dbo].EmployeeHours as eh  inner join 
[TimeClockPlus].    [dbo].employeelist as el ON eh.employeeid = el.employeeid   

WHERE el.department = 'WAREHOUSE' and eh.timein  
>= dateadd(dd, datediff(dd, 0, getdate()), 0)
and eh.timein < dateadd(dd, datediff(dd, 0, getdate()), +1)

Group by  datediff(mi, eh.TimeIn, isnull(eh.TimeOut, DateAdd(HH,2,Getdate()))) , eh.Jobcode

But I am getting multiple rows for each jobcode, am I not understanding GROUP BY correctly?

Example of results with 'order by eh.jobcode' added on the end:

jobcode Minutes
   1    69
   1    127
   1    112
   1    24
   1    105
   2    134
   2    53
   2    101
   4    80
   4    9
   4    105
   4    94
   4    129
   4    119
   5    15
   5    103
   5    23
   5    28
   5    33
   5    91
Mill3niumThr33
  • 3
  • 2
  • Both of the below posters had the right answer technically. I just needed to do a sum on the datediff and remove it from the group by. `SELECT eh.jobcode, SUM( DATEDIFF(mi, eh.TimeIn, ISNULL(eh.TimeOut, DATEADD(HH,2,GETDATE()))) )AS Minutes FROM [TimeClockPlus].[dbo].EmployeeHours AS eh inner join [TimeClockPlus].[dbo].employeelist AS el ON eh.employeeid = el.employeeid WHERE el.department = 'WAREHOUSE' AND eh.timein >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) and eh.timein < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), +1) GROUP BY eh.Jobcode ` – Mill3niumThr33 Aug 08 '14 at 21:09

2 Answers2

0

Your query is almost done. I believe it's enough if you group by the jobcode and add a sum over the datediff. If you want it to look good you can build your select with datediff in a CTE and do the group by and sum on your newly created CTE.

Paul Lucaciu
  • 134
  • 3
  • Thanks for your reply. Are you saying I should change the select and group by sections to be like this : SUM(DATEDIFF(mi, eh.TimeIn, ISNULL (eh.TimeOut,GETDATE()))) AS Hours When I run this it errors saying "Incorrect syntax near the keyword 'FROM'." – Mill3niumThr33 Aug 08 '14 at 18:47
0

The problem appears to me that you have employee IDs using the same Job Code which is why you get duplicates. To get around this, I suggest you use the datediff(minute function in order to get the minutes but also total it by using the SUM() function in the select statement if that makes sense.

If you can't apply SUM(datediff(minute maybe try looking into this:

Sum datediff in minutes with MySQL

Community
  • 1
  • 1
programnub112
  • 171
  • 1
  • 10
  • When I try to wrap the datediff in a sum it errors saying : Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. I will look at that link and try to adapt my query. I also edited the initial table to show that multiple employees are using the same jobcodes. – Mill3niumThr33 Aug 08 '14 at 20:30