x
login about faq Site discussion (meta-askssc)

Get an Avergae and Total from the Time Collected

Now that you all helped on the query as in below. How can I get the total time hh:mm:ss and get the average of it?


SELECT CONVERT(varchar(8),cast(
    cast(
       substring(right('000000'+cast(rptime as varchar),6),1,2)
       + ':' +
       substring(right('000000'+cast(rptime as varchar),6),3,2)
       + ':' +
       substring(right('000000'+cast(rptime as varchar),6),5,2) 
    as datetime) - --Do the math on the rptime(End) and rfotime(start) time to get the elapsed time
    cast(
       substring(right('000000'+cast(rfotime as varchar),6),1,2)
       + ':' +
       substring(right('000000'+cast(rfotime as varchar),6),3,2)
       + ':' +
       substring(right('000000'+cast(rfotime as varchar),6),5,2) 
    as datetime)
  as time)) AS AvgTimePick
    FROM OPENQUERY (GSFL2K,'SELECT rfco
                    ,rfloc
                    ,rfcust
                   /*   ,rfoord#
                    ,rforel#    */
                    ,rfotime
                    ,rptime
                   FROM rfwillchst
                   WHERE rfloc = 50
                    AND rfodate = CURRENT_DATE - 1 days
                    AND rpstat = ''T''
                    AND rfobin# != ''SHIPD''
                   --   AND rfoord# = 34804
              ')
GROUP BY rfco
       ,rfloc
       ,rfcust
       --,rfoord#
       --,rforel#
       ,rfotime
       ,rptime
ORDER BY rfcust
       ,rfotime
more ▼

asked Apr 27 '12 at 10:05 PM in Default

jaymz69 gravatar image

jaymz69
172 3 4 9

Welcome to the site. I edited this with the code tag to make it easier to read.

Apr 27 '12 at 11:58 PM TimothyAWiseman

Thank you

Apr 30 '12 at 03:24 PM jaymz69

I get this message error. I know follow the error and debug it, but FYI...

Msg 8115, Level 16, State 5, Line 3 Arithmetic overflow error converting numeric to data type varchar.

Apr 30 '12 at 03:39 PM jaymz69
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

taking your previous sample data, this is the way you'd get the average, and the sum is gotten simply by changing AVG to SUM

    DECLARE @YourTable TABLE (StartTime int,  EndTime int)

    INSERT  INTO @YourTable SELECT  70500, 71002
    INSERT  INTO @YourTable SELECT  120501, 121845


    Select CONVERT(CHAR(8), DATEADD(SECOND, AVG(DATEDIFF(SECOND,
          CONVERT(DATETIME,CONVERT(VarCHAR(02),Starttime/10000)+':'
         +CONVERT(VARCHAR(02),(StartTiME/100)%100)+':'
         +CONVERT(VARCHAR(02),StartTime%100),8),
          CONVERT(DATETIME,CONVERT(VarCHAR(02),Endtime/10000)+':'
         +CONVERT(VARCHAR(02),(EndTime/100)%100)+':'
          +CONVERT(VARCHAR(02),EndTime%100),8))),
       CONVERT(DATETIME, '00:00:00', 113)),8)                   
     AS ElapsedTime
    FROM    @YourTable 
more ▼

answered Apr 30 '12 at 02:23 PM

Phil Factor gravatar image

Phil Factor
3.2k 8 9 14

It will need to be min:Sec

Apr 30 '12 at 03:14 PM jaymz69

in what sense is my solution not in that format? OK, it will be in hh:mm:ss but you might need that.

Apr 30 '12 at 03:35 PM Phil Factor
(comments are locked)
10|1200 characters needed characters left

This is an example of using aggregate function on sets of data that you can hopefully use to apply the same logic to your dataset

USE [AdventureWorks]
GO
-- all details, no aggregation
SELECT [sod].[SalesOrderID] ,
    [sod].[OrderQty] ,
    [sod].[UnitPrice] ,
    [sod].[LineTotal]
FROM [Sales].[SalesOrderDetail] AS sod
WHERE [sod].[SalesOrderID] BETWEEN 43659 AND 43670

-- using SUM and AVG aggregation functions
SELECT [sod].[SalesOrderID] ,
    AVG([sod].[OrderQty]) AS [Average quantity] ,
    AVG([sod].[UnitPrice]) AS [Average price],
    SUM([sod].[LineTotal]) AS [Total Cost]
FROM [Sales].[SalesOrderDetail] AS sod
WHERE [sod].[SalesOrderID] BETWEEN 43659 AND 43670
GROUP BY [sod].[SalesOrderID]

here is an example that works with time data

IF OBJECT_ID('Times') IS NOT NULL 
    DROP TABLE [dbo].[Times]
    GO
    -- create an example table
CREATE TABLE Times
    (
      itemID INT ,
      dtStart DATETIME ,
      dtEnd DATETIME
    )
GO
-- add 100 rows
WHILE ( SELECT COUNT(*)
         FROM [dbo].[Times]
      ) < 100 
    BEGIN
       INSERT [dbo].[Times]
          ( itemID ,
            [dtStart] 
          )
          SELECT ABS(CHECKSUM(NEWID())) % 7 + 1 AS [itemid] ,
                 DATEADD(s, ABS(CHECKSUM(NEWID())) % 1000 + 1,
                    '20120428') AS dtastart
    END
    GO

-- add end times - a random number of seconds after the start time
UPDATE [dbo].[Times]
    SET    [dtEnd] = DATEADD(s, ABS(CHECKSUM(NEWID())) % 1000 + 1, [t].dtstart)
    FROM [dbo].[Times] AS t
go
-- show data
SELECT [dbo].[Times].[itemID] ,
       [dbo].[Times].[dtStart] ,
       [dbo].[Times].[dtEnd] ,
       DATEDIFF(s, [dbo].[Times].[dtStart], [dbo].[Times].[dtEnd]) AS [duration]
    FROM times
    ORDER BY [dbo].[Times].[itemID] ,
       duration
go
-- show aggregated data
SELECT [dbo].[Times].[itemID] ,
       AVG(DATEDIFF(s, [dbo].[Times].[dtStart], [dbo].[Times].[dtEnd])) AS [avg_duration] ,
       SUM(DATEDIFF(s, [dbo].[Times].[dtStart], [dbo].[Times].[dtEnd])) AS [sum_duration]
    FROM times
    GROUP BY [dbo].[Times].[itemID]

you will need to consider whether to use seconds (as I have) or whether minutes or hours or even milliseconds are more useful for you.

more ▼

answered Apr 28 '12 at 11:09 AM

Fatherjack gravatar image

Fatherjack ♦♦
38.8k 55 69 104

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x28
x18

asked: Apr 27 '12 at 10:05 PM

Seen: 507 times

Last Updated: Apr 30 '12 at 04:02 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.