x

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, 2012 at 10:05 PM in Default

avatar image

jaymz69
172 11 13 17

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

Apr 27, 2012 at 11:58 PM TimothyAWiseman

Thank you

Apr 30, 2012 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, 2012 at 03:39 PM jaymz69
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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, 2012 at 11:09 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left

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, 2012 at 02:23 PM

avatar image

Phil Factor
4.2k 8 23 20

It will need to be min:Sec

Apr 30, 2012 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, 2012 at 03:35 PM Phil Factor
(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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x45
x36

asked: Apr 27, 2012 at 10:05 PM

Seen: 1312 times

Last Updated: Apr 30, 2012 at 04:02 PM

Copyright 2016 Redgate Software. Privacy Policy