Calculation of Time for Multiple Rows

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

Calculation of Time for Multiple Rows

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-08-10 : 02:35:13

Dear Sir,
I am Working on Attendance System my data is Like This

ID - Date - TimeIn - Timeout - Spend Time
101- 12/2/2011- 8:00 - 10:00 - 2:00
101- 12/2/2011- 11:00 - 18:00 - 7:00
101- 13/2/2011- 08:00 - 18:00 - 10:00

I want To Calculate Spend Time of Same Date and Same ID and Display it in front of First Row of same Date
Data Like this

ID - Date - TimeIn - Timeout - Spend Time
101- 12/2/2011- 8:00 - 10:00 - 9:00
101- 12/2/2011- 11:00 - 18:00 -
101- 13/2/2011- 08:00 - 18:00 - 10:00

ANY IDEAS
THANKS IN ADVANCE

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 02:58:16

Are the timein and timeouts going to have a minute factor ?

PBUH

asif372
Posting Yak Master

100 Posts

Posted - 2011-08-10 : 03:02:44

Yes Time In and Time Out have a Minute Factor Also

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 03:18:55

So your spend time is saved with minutes factor to it.

PBUH

asif372
Posting Yak Master

100 Posts

Posted - 2011-08-10 : 04:00:30

Yeah

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 04:58:11

Something like this



select id,
(sum(datepart(hh,spendtime)) + sum(datepart(mi,spendtime))/60)hrs, 
(sum(datepart(mi,spendtime))%60)minutes from yourtable
group by id

PBUH

asif372
Posting Yak Master

100 Posts

Posted - 2011-08-12 : 04:07:09

I want To Select my All Other Fields Also Timein and Time Out How can i do that without mentioning in groupby
THANKS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 04:18:11

Are you using SQL 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources