locked
Sum of Hours in sql server 2008, RRS feed

  • Question

  • User1651488731 posted

    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.

     

     

    Wednesday, January 25, 2012 1:05 AM

Answers

  • User-1011137159 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 25, 2012 1:30 AM

All replies

  • User-1011137159 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 25, 2012 1:30 AM
  • User522486851 posted

    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,
    Wednesday, January 25, 2012 1:47 AM
  • User-1051986203 posted

    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
    
    Wednesday, February 15, 2012 9:14 AM