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
 Time Total Calculation...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Padmanabhan
Starting Member

India
1 Posts

Posted - 11/15/2013 :  09:06:29  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/15/2013 :  11:59:19  Show Profile  Reply with Quote
Sorry didnt get that. You mean you dont need to consider date part at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/15/2013 :  11:59:33  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/16/2013 :  07:44:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.14 seconds. Powered By: Snitz Forums 2000