SQL HELP - TIME CALCULATION - 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

SQL HELP - TIME CALCULATION

Author

Topic

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2012-04-28 : 09:00:33

Timatable:
date id intime outime
04/01/2012 35 4/28/2012 08:25 AM 4/28/2012 10:25 AM
04/01/2012 35 4/28/2012 11:25 AM 4/28/2012 01:25 AM
04/01/2012 35 4/28/2012 02:25 PM 4/28/2012 07:25 AM
04/02/2012 35 4/28/2012 08:25 AM 4/28/2012 10:25 AM
04/03/2012 85 4/28/2012 11:25 AM 4/28/2012 01:25 AM
04/03/2012 85 4/28/2012 02:25 PM 4/28/2012 07:25 AM

Result would be
date id Totalworkingtime in minutes
04/03/2012 35 360

How can I calculate for multiple rows?

____________
Praba

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-28 : 09:12:58

What does intime and outtime represent? They don't seem to be the time someone starts work and ends work - on row 1 intime is before outtime, but on row 2, intime is later than outtime.

Also, there is no row with id = 35 and date = 4/3/2012, so what was the rule you used to calculate the result for id=35, date=4/3/12 to be 360?

If you simply want to add up all the minutes, you can do this, but wouldn't give you 360 minutes:

SELECT Date,id, SUM(DATEDIFF(minute, inttime,outtime))
FROM YourTable
GROUP BY Date,ID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-28 : 19:23:10

i think your sample data is wrong. please post correct data for us to help you with a solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2012-05-02 : 04:23:17

Sorry, my sample data are wrong...

04/01/2012 35 4/01/2012 08:25 AM 4/01/2012 10:25 AM
04/01/2012 35 4/01/2012 11:25 AM 4/01/2012 01:25 PM
04/01/2012 35 4/01/2012 02:25 PM 4/01/2012 07:25 PM
04/02/2012 35 4/02/2012 08:25 AM 4/02/2012 10:25 AM
04/03/2012 85 4/03/2012 11:25 AM 4/03/2012 01:25 PM
04/03/2012 85 4/03/2012 02:25 PM 4/03/2012 07:25 PM

I want to calculate the total time of minutes done by id 35 on date 04/01

____________
Praba

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-02 : 06:45:36

Query I posted on 04/28/2012 09:12:58 would do that. If you just want the data for Id=35 and date = 4/1/2012, add a where clause

SELECT Date,id, SUM(DATEDIFF(minute, inttime,outtime))
FROM YourTable
WHERE [Date] = '20120401' AND ID = 35
GROUP BY Date,ID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources