How do I Calculate Average Leadtime in sqlserver...

ASP Free Forums Sponsor:

September 2nd, 2007, 05:51 AM

yasinirshad

Contributing User

Join Date: Jul 2007

Posts: 158

Time spent in forums: 13 h 8 m 6 sec
Reputation Power: 0

Transact-SQL - How do I Calculate Average Leadtime in sqlserver...

Hi,
How do I Calculate Average Leadtime...
I have a Table named "iCalls_Calls" which has 2 Columns (start_Date and Closed_Date).I need to calculate average leadtime based on the columns from this table . I have a query and i need to add this ( calculate average leadtime) to this query.

Code:

SELECT B.USER_DIVISION,B.USER_DEPARTMENT,COUNT(*) FROM iCalls_Calls A INNER JOIN iCalls_Users B on A.REQUESTOR = B.USER_ID
GROUP BY USER_DIVISION,USER_DEPARTMENT

Can anyone send me the correct query to calculate the average time ?
Thanks..

September 5th, 2007, 11:27 AM

Lauramc

SQL Slarentice

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,835

Time spent in forums: 2 Weeks 2 Days 3 h 21 m 33 sec
Reputation Power: 1264

I am not sure where you'd put this. I guess as another column, but you could try:

sql Code:

Original - sql Code
SELECT [avg_time] = DATEDIFF(day, start_date, closed_date) FROM iCalls_Calls

SELECT
    [avg_time] = DATEDIFF(day, start_date, closed_date)
FROM
    iCalls_Calls

This will give you the number of days between the start and close date. Then if you wanted to average them accross the entire table you could use the AVG function in sql.

__________________
Slarentice (origin:Shadow Wizard of ASP Free) [noun] A slave and apprentice of the Wizard's Circle (specifically of mehere) at ASP Free.
----
If shemzilla takes over, it's best to be on his good side

September 6th, 2007, 07:50 AM

yasinirshad

Contributing User

Join Date: Jul 2007

Posts: 158

Time spent in forums: 13 h 8 m 6 sec
Reputation Power: 0

Re:Please help me with the Syntax for this Sql Query.

Hi..Thanks for the query..
But i need to calculate average time in " Hours and Minutes" and not in days... So can you please help me with the syntax for returning Hours and minutes.
Thanks.

Quote:

Originally Posted by Lauramc

I am not sure where you'd put this. I guess as another column, but you could try:

sql Code:

Original - sql Code
SELECT [avg_time] = DATEDIFF(day, start_date, closed_date) FROM iCalls_Calls

SELECT
    [avg_time] = DATEDIFF(day, start_date, closed_date)
FROM
    iCalls_Calls

This will give you the number of days between the start and close date. Then if you wanted to average them accross the entire table you could use the AVG function in sql.

September 6th, 2007, 08:03 AM

jmurrayhead

Moderator

ASP Free God 17th Plane (13000 - 13499 posts)

Join Date: Feb 2004

Location: Reston, VA, USA

Posts: 13,099

Folding Points: 98171 Folding Title: Advanced Folder

Time spent in forums: 3 Months 1 Week 19 h 23 m 20 sec
Reputation Power: 1643

Then change it to "hour":

SQL Code:

Original - SQL Code
SELECT [avg_time] = DATEDIFF(hour, start_date, closed_date) FROM iCalls_Calls

 
SELECT    
    [avg_time] = DATEDIFF(hour, start_date, closed_date)
FROM    
    iCalls_Calls

__________________
jmurrayhead

Did I help you out? Make me popular by clicking the

icon!

New Members:Proper way to post a question

.Net Help

September 6th, 2007, 08:14 AM

yasinirshad

Contributing User

Join Date: Jul 2007

Posts: 158

Time spent in forums: 13 h 8 m 6 sec
Reputation Power: 0

Re:Please help me with the Syntax for this Sql Query.

Well ..this will only give me in hours..
I need it in "Hours and Minutes"...
Hope you got my requirement.

Quote:

Originally Posted by jmurrayhead

Then change it to "hour":

SQL Code:

Original - SQL Code
SELECT [avg_time] = DATEDIFF(hour, start_date, closed_date) FROM iCalls_Calls

 
SELECT    
    [avg_time] = DATEDIFF(hour, start_date, closed_date)
FROM    
    iCalls_Calls

September 6th, 2007, 10:44 AM

Lauramc

SQL Slarentice

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,835

Time spent in forums: 2 Weeks 2 Days 3 h 21 m 33 sec
Reputation Power: 1264

You can do this, just do it as two separate queries. Such as this for hours:

sql Code:

Original - sql Code
SELECT [avg_time] = DATEDIFF(hour, start_date, closed_date) FROM iCalls_Calls

SELECT
        [avg_time] = DATEDIFF(hour, start_date, closed_date)
FROM
        iCalls_Calls

and this for minutes

sql Code:

Original - sql Code
SELECT [avg_time] = DATEDIFF(minute, start_date, closed_date) FROM iCalls_Calls

SELECT
        [avg_time] = DATEDIFF(minute, start_date, closed_date)
FROM
        iCalls_Calls

Viewing: ASP Free Forums > Database > SQL Development > Transact-SQL - How do I Calculate Average Leadtime in sqlserver...

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!