Calculate the subtotal of hour and minutes

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate the subtotal of hour and minutes

Author

Topic

tm555
Starting Member

5 Posts

Posted - 2011-11-09 : 04:01:17

Hi,

Can any one help me out here i attached my table i need to calculate the subtotal between MinimumHour of CheckIn and MaximumHour of CheckOut the per day and calculate the subtotal between MinimumMinutes of CheckIn to MaximumMinutes of CheckOut per day and group by salesperson.

Declare @Route Table(
Salesperson varchar(40),
CheckIn varchar(50),
CheckOut varchar(50),
CustomerName varchar(50))

INSERT INTO @Route SELECT 'A', '09/13/2011 6:04:12 AM', '09/13/2011 6:10:43 AM','ABC'
INSERT INTO @Route SELECT 'B', '09/13/2011 6:20:12 AM', '09/13/2011 7:04:43 AM','CDE'
INSERT INTO @Route SELECT 'A', '09/14/2011 8:04:12 AM', '09/14/2011 8:10:43 AM','FGI'
INSERT INTO @Route SELECT 'B', '09/14/2011 9:04:12 AM', '09/14/2011 10:04:43 AM','JKL'
INSERT INTO @Route SELECT 'C', '09/15/2011 8:04:12 AM', '09/15/2011 8:10:43 AM','MNC'
INSERT INTO @Route SELECT 'C', '09/15/2011 9:04:12 AM', '09/15/2011 9:10:43 AM','ARC'

select * from @Route

I need the following output

Salesperson CheckIn CheckOut CustomerName

A 09/13/2011 6:04:12 AM 09/13/2011 6:10:43 AM ABC

A 09/13/2011 8:04:12 AM 09/13/2011 8:10:43 AM FGI

SubTotal 2:06 minutes ( if min more than 60 add the 1 hr to Hour)

B 09/14/2011 6:20:12 AM 09/14/2011 7:04:43 AM CDE
B 09/14/2011 9:04:12 AM 09/14/2011 10:04:43 AM JKL

SubTotal 4:16 minutes ( if min more than 60 add the 1 hr to Hour)

C 09/15/2011 8:04:12 AM 09/15/2011 8:10:43 AM MNC
C 09/15/2011 9:04:12 AM 09/15/2011 9:10:43 AM ARC

SubTotal 1:6 minutes ( if min more than 60 add the 1 hr to Hour)

Thanks and regards

brite

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 04:10:19

can you explain logic for getting subtotal values?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 04:28:21

Your sample data in DML and output does not match at all. You have both A on different date in the sample data but in the required output, it is same date.

And not sure you did get 2:06 minutes for Salesperson A, shouldn't it be just 2:00 ?


select 	*, 
	SubTotal 	= convert(varchar(5), 
					dateadd(minute, 
						datediff(minute, min(CheckIn) over (partition by Salesperson), 
						max(CheckIn) over (partition by Salesperson)), 
						0), 
				   108)
from 	@Route r
order by Salesperson, CheckIn

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

tm555
Starting Member

5 Posts

Posted - 2011-11-13 : 22:14:26

Hi khtan,

Thanks lot that was i want.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-13 : 22:56:31

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources