SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL HELP - TIME CALCULATION		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cutepraba
Yak Posting Veteran

India
53 Posts
Posted - 04/28/2012 :  09:00:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/28/2012 :  09:12:58  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 04/28/2012 :  19:23:10  Show Profile  Reply with Quote
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/
Go to Top of Page

cutepraba
Yak Posting Veteran

India
53 Posts
Posted - 05/02/2012 :  04:23:17  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 05/02/2012 :  06:45:36  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000