Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

dk
Starting Member

35 Posts

Posted - 2009-02-12 : 13:42:59

Hi again to all,

I am trying to make query which will result with total hours per jobno in the current month (or some other month). So I have USERS.ID, JOBNO, EVENT (can be different: IN, OUT). I have to calculate total hours per USER per day and then total in the month.

Easy one?

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-12 : 13:51:38

quote:
Originally posted by dk

Hi again to all,

I am trying to make query which will result with total hours per jobno in the current month (or some other month). So I have USERS.ID, JOBNO, EVENT (can be different: IN, OUT).I have to calculate total hours per USER per day and then total in the month.

Easy one?

You didn't specify an date column or what EVENT means (what is IN or OUT?).

Here is an example.. It is untested but should calculate Total Hours worked per user over the last day (it is generic). To get monthly totals just union this query with another one that looks at data across a month (modify the date_time in the WHERE clause).

SELECT SUM(TotalHours) OVER(PARTITION BY USERS) AS 'Daily Total'
FROM SomeTable
WHERE date_time > DATEADD(day, DATEDIFF(day, 0, GETDATE())-1,0)

Maybe someone can post a better solution?

r&r

dk
Starting Member

35 Posts

Posted - 2009-02-12 : 13:55:23

Thanks for help. IN is event showing that user come at work place, and OUT is event showing that user get out from working place. I don't have hours column, I have to calculate hours between IN and OUT event.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-12 : 14:02:12

quote:
Originally posted by dk

Thanks for help. IN is event showing that user come at work place, and OUT is event showing that user get out from working place. I don't have hours column, I have to calculate hours between IN and OUT event.

Here is how you can find the difference in hours between IN and OUT then.

DATEDIFF(day,IN,OUT)*24 as TotalHours

To use it in the query you want you might try this..


SELECT SUM(TotalHours) OVER(PARTITION BY JOBNO) AS 'Daily Total'
FROM SomeTable
WHERE DATEDIFF(day,IN,OUT)*24 <= 24 
AND USER.ID = SomeValue

again untested and I don't know how a userID is mapped to a JobNO

r&r

dk
Starting Member

35 Posts

Posted - 2009-02-12 : 14:22:50

I need total hours per month per jobno. It could be 50 USER at JOBNO having 10 hours per day, giving TOTAL hours per month per JOBNO.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-12 : 15:39:31

Well in that case its simpler ..

Since you have no hours column you need to calculate OUT-IN to get that right? So encapsulate the DateDiff function into the sum function. I think you can do that. Again untested..

You then need to use a WHERE clause to select only the last 30 days or a particular month.. I don't recall which you need.


SELECT USER,JOBNO SUM(DATEDIFF(day,IN,OUT)*24) as Monthly_Total
  FROM   YourTableName
 WHERE --Your date condition (30 days or monthly?) AND JOBNO = Somevalue  
 GROUP BY JOBNO,USER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 20:26:12

can events overlap multiple days?

dk
Starting Member

35 Posts

Posted - 2009-02-15 : 01:16:32

Rarely, but it's possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 21:30:52

in that case how should you sum?