SUM problem - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SUM problem

Author

Topic

abhi123
Starting Member

10 Posts

Posted - 2009-06-02 : 17:50:10

Hi,
I am currently working on employee database. Scenario is that an employee can come to office many times in a day. I need the all timein, timeout of the employee and the total number of hours an employee worked in a particular day. i can get the time difference by datediff(mi,timein,timeout). but i don't know how to get the sum.
For example
[CODE]
Date Emp_Id TimeIn Timeout TimeDiff
11-17-2008 1 9:00 11:00 2
11-17-2008 1 13:00 15:00 2
11-17-2008 1 16:00 17:00 1
__________________
5
Date Emp_Id TimeIn Timeout TimeDiff
11-17-2008 2 10:00 12:00 2
11-17-2008 2 13:00 15:00 2
11-17-2008 2 16:00 17:00 1
__________________
5
[/CODE]

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 18:17:25

select emp_id, sum(datediff(minute, timein, timeout))
from table1
group by emp_id

E 12°55'05.63"
N 56°04'39.26"

abhi123
Starting Member

10 Posts

Posted - 2009-06-02 : 21:21:37

Thanks for the reply. But i also need to show TimeIn , Timeout and TimeDiff in my query..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 00:34:09

select emp_id, timei, timeout, datediff(minute, timein, timeout) as timediff, sum(datediff(minute, timein, timeout)) over (partition by emp_id) as emptotal
from table1

E 12°55'05.63"
N 56°04'39.26"

abhi123
Starting Member

10 Posts

Posted - 2009-06-03 : 09:44:54

Thanks for you reply again. But when i am trying to run your query i get this error
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Let me explain little bit better.
All the timin, timeout of an employee are stored in different table called Time.
All the employee information like employee name, employee id are stored in table called Employee.
They both related to each with emp_id.
so what i was looking for on a particular date say 11-24-09, how many employees say 3 did time in and time out, their time difference and total time an employee worked in that day.
Thanks once again man. It's been a great help

Regards
Abhi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 10:11:49

I don't know what you are testing.
In my suggstion made 06/03/2009 : 00:34:09 there are no subqueries.

E 12°55'05.63"
N 56°04'39.26"

abhi123
Starting Member

10 Posts

Posted - 2009-06-03 : 11:11:38

Hi
I made some changes, now your query works fine, there is no error. But the result i am getting right now is different. What i am getting now is total time the employee worked in a company for the all the days. But i want the total time an employee worked in a day.
I am posting my example again with little bit modification.


Date          Emp_Id     TimeIn       Timeout         TimeDiff
11-17-2008     1          9:00         11:00           2
11-17-2008     1          13:00        15:00           2
11-17-2008     1          16:00        17:00           1
                                        __________________
                                                       5
Date          Emp_Id     TimeIn       Timeout         TimeDiff
11-17-2008     2          10:00        12:00           2
11-17-2008     2          13:00        15:00           2
11-17-2008     2          16:00        17:00           1
                                        __________________
                                                       5
Date          Emp_Id     TimeIn       Timeout         TimeDiff
11-18-2008     1          09:00        12:00           3
11-18-2008     1          13:00        15:00           2
11-18-2008     1          16:00        18:00           2
                                        __________________
                                                       7
Date          Emp_Id     TimeIn       Timeout         TimeDiff
11-18-2008     2          10:00        12:00           2
11-18-2008     2          13:00        15:00           2
11-18-2008     2          16:00        17:00           1
                                        __________________
                                                       5

I am sorry not to able explain properly. I hope this time you will get a better picture what i am trying to say.
Thanks for your help peso.

Regards
Abhishek Madaan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 13:06:56

select date, emp_id, timein, timeout, datediff(minute, timein, timeout) as timediff, sum(datediff(minute, timein, timeout)) over (partition by date, emp_id) as emptotal
from table1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources