Sum of Hours in sql server 2008, RSS

• • Reply
  

  deepakaitr

  Member

  85 Points

  41 Posts

  Sum of Hours in sql server 2008,

  Jan 25, 2012 05:05 AM|LINK

  Hi friends

  I have data like

  Resource Name            Billable Hours Non-Billable Hours OTHER CAPITAL    Total Hours
  Madhuri                              0                  0:10                 0          0:10        0:10
  Madhuri                              0                  0:25                 0:25         0        0:25
  Madhuri                              0                  16:15               16:15       0       16:15
  Madhuri                              0                  0:25                0:25         0       0:25

  and the time for capital and other are in varchar how to convert them,

  and i want to make the above formate as given below

  Resource Name      |      Billable Hours |  Non-Billable Hours | OTHER  |  CAPITAL |   Total Hours

  Madhuri                                0             17:15                   17:5        00:10        17:15

   how to do this in sql server 2008 please suggest me.

  Thanks.

   

   

  The observer cannot be left out of the description of the observation.
• • Reply
  

  amitpatel.it

  Star

  8070 Points

  1880 Posts

  Re: Sum of Hours in sql server 2008,

  Jan 25, 2012 05:30 AM|LINK

  In SQL server you can use directly sum with time object so you can use below logic where first you have to convert tall in minutes and then applying sum and then convert in hour and minute base time forate.

  create table #temp(Tm time)
  insert into #temp values ( CAST('0:10' as time))
  insert into #temp values (CAST('0:55' as time))
  
  select * from #temp
  select cast(SUM(DateDiff(MINUTE,  CAST('0:00' as time), Tm))/60 as nvarchar(3)) + ':' + cast(SUM(DateDiff(MINUTE,  CAST('0:00' as time), Tm))%60 as nvarchar(2))  from #temp

  Let me know if you need any more assitance.

  My Tech Blogs
  MCPD Enterprise and Web Application
  MCTS Web, Window and Enterprise Application
  • Marked as answer by Young Yang - MSFT on Feb 20, 2012 11:16 AM
• • Reply
  

  Sankalpa

  Contributor

  2134 Points

  494 Posts

  Re: Sum of Hours in sql server 2008,

  Jan 25, 2012 05:47 AM|LINK

  hi chek the below query

  SELECT DATEADD(hh,SUM(DATEPART(hh,CONVERT(DATETIME,[ColName],108))), DATEADD(n,SUM(DATEPART(n,CONVERT(DATETIME,[ColName],108))),'1900.01.01') ) FROM dbo.[TableName]

  But this will not work on the value like 0 if you can update your table values into 0:0 this is working,

  Please mark the replies as answers if it help.
• • Reply
  

  jeeveshfulor...

  Participant

  1576 Points

  289 Posts

  Re: Sum of Hours in sql server 2008,

  Feb 15, 2012 01:14 PM|LINK

  try with this:-

  create table Resourcetbl([Resource Name] varchar(10),[Billable Hours] varchar(10),[Non-Billable] varchar(10),[Hours]varchar(10),[OTHER CAPITAL] varchar(10),[Total Hours] varchar(10))
  insert into Resourcetbl values
  ('Madhuri',                              '0'  ,                '0:10'    ,             '0'    ,      '0:10' ,      '0:10'),
  ('Madhuri' ,                             '0'  ,                '0:25'   ,              '0:25'   ,      '0'  ,      '0:25'),
  ('Madhuri' ,                             '0'  ,                '16:15' ,              '16:15'   ,    '0'   ,    '16:15' ),
  ('Madhuri' ,                             '0'  ,                '0:25'  ,              '0:25'   ,      '0'  ,     '0:25')
  
  
  select * from Resourcetbl
  
  
  SELECT [Resource Name],
  STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12))) [Billable Hours],
  STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours1 + theMinutes1, '19000101'), 8), 1, 2, CAST((theHours1 + theMinutes1) / 3600 AS VARCHAR(12))) [Non-Billable],
  STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours2 + theMinutes2, '19000101'), 8), 1, 2, CAST((theHours2 + theMinutes2) / 3600 AS VARCHAR(12))) [Hours],
  STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours3 + theMinutes3, '19000101'), 8), 1, 2, CAST((theHours3 + theMinutes3) / 3600 AS VARCHAR(12))) [OTHER CAPITAL],
  STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours4 + theMinutes4, '19000101'), 8), 1, 2, CAST((theHours4 + theMinutes4) / 3600 AS VARCHAR(12)))[Total Hours]
  FROM
  (
  SELECT [Resource Name],
  ABS(SUM(CASE CHARINDEX(':', [Billable Hours]) WHEN 0 THEN 0 ELSE 3600 * LEFT([Billable Hours], CHARINDEX(':', [Billable Hours]) - 1) END)) AS theHours,
  ABS(SUM(CASE CHARINDEX(':', [Billable Hours]) WHEN 0 THEN 0 ELSE 60 * SUBSTRING([Billable Hours], CHARINDEX(':', [Billable Hours]) + 1, 2) END)) AS theMinutes,
  
   ABS(SUM(CASE CHARINDEX(':', [Non-Billable]) WHEN 0 THEN 0 ELSE 3600 * LEFT([Non-Billable], CHARINDEX(':', [Non-Billable]) - 1) END)) AS theHours1,
  ABS(SUM(CASE CHARINDEX(':', [Non-Billable]) WHEN 0 THEN 0 ELSE 60 * SUBSTRING([Non-Billable], CHARINDEX(':', [Non-Billable]) + 1, 2) END)) AS theMinutes1,
  
   ABS(SUM(CASE CHARINDEX(':', [Hours]) WHEN 0 THEN 0 ELSE 3600 * LEFT([Hours], CHARINDEX(':', [Hours]) - 1) END)) AS theHours2,
  ABS(SUM(CASE CHARINDEX(':', [Hours]) WHEN 0 THEN 0 ELSE 60 * SUBSTRING([Hours], CHARINDEX(':', [Hours]) + 1, 2) END)) AS theMinutes2,
  
   ABS(SUM(CASE CHARINDEX(':', [OTHER CAPITAL]) WHEN 0 THEN 0 ELSE 3600 * LEFT([OTHER CAPITAL], CHARINDEX(':', [OTHER CAPITAL]) - 1) END)) AS theHours3,
  ABS(SUM(CASE CHARINDEX(':', [OTHER CAPITAL]) WHEN 0 THEN 0 ELSE 60 * SUBSTRING([OTHER CAPITAL], CHARINDEX(':', [OTHER CAPITAL]) + 1, 2) END)) AS theMinutes3,
  
   ABS(SUM(CASE CHARINDEX(':', [Total Hours]) WHEN 0 THEN 0 ELSE 3600 * LEFT([Total Hours], CHARINDEX(':', [Total Hours]) - 1) END)) AS theHours4,
  ABS(SUM(CASE CHARINDEX(':', [Total Hours]) WHEN 0 THEN 0 ELSE 60 * SUBSTRING([Total Hours], CHARINDEX(':', [Total Hours]) + 1, 2) END)) AS theMinutes4
  
  FROM Resourcetbl
  group by [Resource Name]
  ) AS d