CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DooDoo
DooDoo

Reputation: 13437

Average of Difference in SQL Server DateTime column

I have a data entry form that anybody must fill up 3 forms. I log the entry time for form 1, form 2, form 3. Now I want to know what is the average of time to fill up the forms for all people. How can I do that?

Upvotes: 1

Views: 3995

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

100% guesswork based on extremely vague requirements.

DECLARE @x TABLE
(
    UserID    INT,
    FormID    TINYINT,
    StartTime DATETIME,
    EndTime   DATETIME
);

INSERT @x VALUES

-- a user who has completed all three forms
    (1,1,'20110820 04:25:01','20110820 04:33:07'),
    (1,2,'20110820 04:34:05','20110820 04:38:33'),
    (1,3,'20110820 04:39:02','20110820 04:47:55'),

-- a user who has completed two forms
    (2,1,'20110820 04:25:54','20110820 04:29:32'),
    (2,2,'20110820 04:30:55','20110820 04:34:27'),

-- the same user who has completed 1.5 forms
    (2,1,'20110820 04:35:23','20110820 04:37:15'),
    (2,2,'20110820 04:38:34',NULL),

-- the same user who has completed all three forms
    (2,1,'20110820 04:45:12','20110820 04:49:07'),
    (2,2,'20110820 04:50:26','20110820 04:55:31'),
    (2,3,'20110820 04:56:41','20110820 05:01:23'),

-- a slow user who has completed all three forms
    (3,1,'20110820 05:25:04','20110820 05:43:07'),
    (3,2,'20110820 05:44:09','20110820 05:55:21'),
    (3,3,'20110820 05:59:41','20110820 06:24:23');

Here are some typical aggregations that I'm guessing might hit the one you're looking for:

SELECT -- avg by form regardless of user
    FormID, 
    completed_forms = COUNT(*), 
    average = AVG(DATEDIFF(SECOND, StartTime, EndTime))
FROM @x GROUP BY FormID;

SELECT -- avg by form and user
    UserID,
    FormID, 
    completed_forms = COUNT(*),
    [seconds] = AVG(DATEDIFF(SECOND, StartTime, EndTime))
FROM @x GROUP BY UserID, FormID;

SELECT -- avg by user regardless of form
    UserID,
    completed_forms = COUNT(*),
    [seconds] = AVG(DATEDIFF(SECOND, StartTime, EndTime))
FROM @x GROUP BY UserID;

-- if you want hh:mm:ss format and the form never takes > 24 hours to complete,
-- you can do this kind of thing to any of the above queries:

;WITH x(FormID, completed_forms, average) AS
(
    SELECT
        FormID,
        COUNT(*),
        AVG(DATEDIFF(SECOND, StartTime, EndTime))
    FROM @x GROUP BY FormID
)
SELECT 
    FormID, 
    completed_forms,
    [hh:mm:ss] = CONVERT(CHAR(8), (CONVERT(TIME(0), DATEADD(SECOND, average, '19000101'))))
FROM x;

Upvotes: 2

JSJ
JSJ

Reputation: 5691

Ok As i have understand you question. say you are putting all three form data into database at the last when user clicks on the finish button. Follow below steps to achive your goal.

  1. When the First Navigation or open on your wizard hold the Wizard start time.

  2. let user finish their wizard and when he clicks on the finish button hold the finish time in memory

Condition's

  1. if user don't finish there wizard then clear memroy releted to this wizard.

Example : user have started wizard at 6:00:45 PM and finish it at 6:12:15 PM so to calculate this time between this use datediff function of SQL

use below lines as per your requirement

SELECT DATEDIFF(hour, @StartTime, @EndTime); Return hours taken for wizard fillup

SELECT DATEDIFF(minute, @StartTime,@EndTime); Return Minutes taken for wizard fillup

SELECT DATEDIFF(second, @StartTime, @EndTime); Return Seconds taken for wizard fillup

SELECT DATEDIFF(millisecond,@StartTime, @EndTime); Return Milliseconds taken for wizard fillup

Upvotes: 1

Related Questions