Sql-server – T-SQL Calculate average value for Duration Field

sql servert-sql

Can anyone please help me on this..

I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'.

How do I calculate the average duration? I read some other blogs and also used cast techniques like the one below but couldn't solve my problem…

cast(cast(avg(cast(CAST(Duration as datetime) as float)) as datetime) as time) AvgTime

Please help…

Best Answer

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle?

Don't be tempted to change this from nvarchar to time. Again, time is a point in time, not an interval. You should be storing start and end values as datetime; you can always calculate duration from that (typically I see duration as an additional nonsense column that doesn't need to be stored in the first place).

The formatting as hh:mm:ss should not happen at storage time, only at presentation time. And no idea why you would ever use nvarchar for this in the first place - what Unicode characters do you expect to support in hh:mm:ss?

In the meantime:

SELECT CONVERT(TIME(0), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(DATETIME, 
  CASE WHEN ISDATE(Duration) = 1 THEN Duration END))),0)) -- in case of garbage data 
FROM dbo.your_table_name;

See how messy that is? Wouldn't it be much easier as:

SELECT AVG(Duration) FROM dbo.your_table_name;

Or:

SELECT AVG(DATEDIFF(SECOND, [start], [end])) FROM dbo.your_table_name;

And then format as hh:mm:ss in the client language? Yes, I think so.

Related Solutions

Sql-server – MS SQL : Use Computed value to Compute other values

Can you not do this with a CROSS APPLY?

SQL Server – Query Duration Differences with Data Value Changes

This solution assumes there is only a single Value for each RecordDate. Performance will be better with tuned indexes, and probably breaking up the process using a temporary table or table variable. I used your script for test data. The output may not be exactly how you want it, but the process to get you close is what's important -- it works by filtering the rows to only the rows that start an interval, and then fabricates the end date by offsetting the results from the previous step.

WITH a AS
(
    SELECT
        Value,
        RecordDate,
        ROW_NUMBER() OVER(ORDER BY RecordDate) AS RN
        FROM [dbo].[RateTable] rt
),
b AS
(
    SELECT
        a1.Value,
        a1.RecordDate,
        ROW_NUMBER() OVER(ORDER BY a1.RecordDate) AS RN
        FROM a a1
        LEFT OUTER JOIN a a2 ON a2.RN = a1.RN - 1
        WHERE
            (a1.Value != a2.Value) OR
            (a2.RN IS NULL)
)
SELECT
    b1.Value,
    b1.RecordDate AS StartDate,
    b2.RecordDate AS EndDate,
    DATEDIFF(DAY, b1.RecordDate, b2.RecordDate) + 1 AS Duration /* Fixme? */
    FROM b b1
    LEFT OUTER JOIN b b2 ON b2.RN = b1.RN + 1
    ORDER BY b1.RecordDate;

Output:

Value   StartDate                EndDate                  Duration
1       2012-01-01 00:00:00.000  2012-01-04 00:00:00.000  4
5       2012-01-04 00:00:00.000  2012-01-06 00:00:00.000  3
12      2012-01-06 00:00:00.000  2012-01-07 00:00:00.000  2
1       2012-01-07 00:00:00.000  NULL                     NULL

Best Answer

Related Solutions

Sql-server – MS SQL : Use Computed value to Compute other values

SQL Server – Query Duration Differences with Data Value Changes

Related Question