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.
Author |
Topic |
Padmanabhan
Starting Member
1 Post |
Posted - 2013-11-15 : 09:06:29
|
I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example: Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-15 : 11:59:19
|
Sorry didnt get that. You mean you dont need to consider date part at all?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-11-15 : 11:59:33
|
If you can supply some sample data and expected output we can probably help you better. Below are some links to help you present your question with the information necessary for us to be able to run queries against your sample data, thus getting you a better answer:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-11-16 : 07:44:06
|
[code]DECLARE @Sample TABLE ( Data TIME(0) );INSERT @Sample ( Data )VALUES ('04:15'), ('04:25');WITH cte(Data, Peso)AS ( SELECT Data, 1410 + DATEDIFF(MINUTE, '23:45:00', Data) / 30 * 30 FROM @Sample)SELECT Data, SUM(Peso) OVER () / 60E AS [Total Hours]FROM cte;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|