locked
AGGREGATION IN HH:MM RRS feed

  • Question

  • Hi,

    I have the data as following in source table

    want to move the data to target table as following

    how to form a query?

    Thanks in advance

    Regards,

    Priviya .P

    Wednesday, July 24, 2019 1:16 PM

Answers

  • Hi Priviya,

    Use following code to get the working hours

    --drop table #calctime
    create table #calctime
    (id int, whours varchar(50))
    
    insert into #calctime
    select 1, '1:30'
    union all
    select 1,'02:30'
    union all
    select 1,'5:00'
    union all
    select 2,'01:00'
    union all
    select 2,'01:30'
    
    select id, 
    convert(char(8),dateadd(second,SUM (DATEPART(hh,(convert(datetime,whours,1))) * 3600 +
    DATEPART(mi, (convert(datetime,whours,1))) * 60 + DATEPART(ss,(convert(datetime,whours,1)))),0),108) as totalhours
    from #calctime
    group by id
     


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Wednesday, July 24, 2019 1:51 PM

All replies

  • Hi Priviya,

    Use following code to get the working hours

    --drop table #calctime
    create table #calctime
    (id int, whours varchar(50))
    
    insert into #calctime
    select 1, '1:30'
    union all
    select 1,'02:30'
    union all
    select 1,'5:00'
    union all
    select 2,'01:00'
    union all
    select 2,'01:30'
    
    select id, 
    convert(char(8),dateadd(second,SUM (DATEPART(hh,(convert(datetime,whours,1))) * 3600 +
    DATEPART(mi, (convert(datetime,whours,1))) * 60 + DATEPART(ss,(convert(datetime,whours,1)))),0),108) as totalhours
    from #calctime
    group by id
     


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Wednesday, July 24, 2019 1:51 PM
  • 
    
    DECLARE @T TABLE (
    	ID int,
    	WorkingHours varchar(50)
    );
    
    INSERT INTO @T VALUES 
    (1, '01:30'), (1, '02:30'), (1, '05:30'), (2, '01:00'), (2, '01:30');
    
    SELECT ID, 
    	RIGHT('00' + CAST(SUM(DATEPART(HOUR, WorkingHours) * 60 + DATEPART(MINUTE, WorkingHours)) / 60 AS varchar(20)), 2) + ':' + 
    	RIGHT('00' + CAST(SUM(DATEPART(HOUR, WorkingHours) * 60 + DATEPART(MINUTE, WorkingHours)) % 60 AS varchar(20)), 2)
    FROM @T
    GROUP BY ID



    A Fan of SSIS, SSRS and SSAS


    Wednesday, July 24, 2019 3:24 PM
  • What you are talking about is actually "duration".

    Please see:

    https://sqlpathy.com/2012/07/15/performing-sum-over-a-column-of-datatype-time/

    Wednesday, July 24, 2019 3:48 PM
    Answerer
  • select id ,
    FORMAT(DATEADD(ss,(SUM(DATEDIFF(ss,0,whours))%(24 * 3600)),0),'HH:mm:ss') AS TotalTime
    from #calctime
    group by id

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 25, 2019 4:28 AM