Average minutes between dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Average minutes between dates

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-06-30 : 06:51:09

I have a helpdesk system that was developed in-house. I am now writing reports for the managers and one of them I'm struggling with.

I have 2 tables in question (I've left out non relevant fields)

maint_jobs table has:
job_number (pk)
date_added (date time)

maint_job_responses
id (pk)
job_number (joined to job number above)
date_added (date_time)

Each "job" in the maint_jobs table can have many responses against it in the maint_job_responses (inner join) but here is my problem:

I need to work out the average response time between the two date_added fields. My problem is that there can be multiple records against one job but I only want to use the first job response for the calculation (if that makes sense)

So the output would be something like between 1st June and 30th June, the average response time was x hours (or minutes etc)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 07:11:57

[code]
declare @maint_jobs table (job_number int, date_added datetime)
declare @maint_job_responses table (id int identity(1,1), job_number int, date_added datetime)

insert @maint_jobs
select 1, '2010-06-29T09:00:00.000' union all
select 2, '2010-06-30T09:30:55.000'

insert @maint_job_responses (job_number, date_added)
select 1, '2010-06-29T09:45:16.000' union all
select 1, '2010-06-30T11:00:00.000' union all
select 2, '2010-06-30T11:59:59.000'

select AVG(diff_minute) as avg_diff_minute from
(
select
jobs.job_number,
jobs.date_added,
datediff(minute,jobs.date_added,resp.date_added) as diff_minute
from @maint_jobs as jobs
join
(
select
job_number,
min(date_added) as date_added
from @maint_job_responses
group by job_number
) as resp
on jobs.job_number = resp.job_number
where jobs.date_added between '20100531' and '20100701' -- job added in june...
)dt
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-06-30 : 07:19:50

Excellent, if I wanted to only show the jobs logged between 1st June and 30th June, where in that query would I add that please?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 07:24:04

Above is an example for jobs added in june.
If you need jobs responses in june instead then this:


declare @maint_jobs table (job_number int, date_added datetime)
declare @maint_job_responses table (id int identity(1,1), job_number int, date_added datetime)

insert @maint_jobs
select 1, '2010-05-29T09:00:00.000' union all
select 2, '2010-06-30T09:30:55.000' union all
select 3, '2010-06-30T11:45:00.000'

insert @maint_job_responses (job_number, date_added)
select 1, '2010-06-29T09:45:16.000' union all
select 1, '2010-06-30T11:00:00.000' union all
select 2, '2010-06-30T11:56:50.000' union all
select 3, '2010-06-30T11:59:59.000'


select AVG(diff_minute) as avg_diff_minute from
(
select
jobs.job_number,
jobs.date_added,
datediff(minute,jobs.date_added,resp.date_added) as diff_minute
from @maint_jobs as jobs
join
(
select
job_number,
min(date_added) as date_added
from @maint_job_responses
where date_added between '20100531' and '20100701' -- job response in june...
group by job_number
) as resp
on jobs.job_number = resp.job_number
)dt