avg datetime - 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 2005 Forums

Transact-SQL (2005)

avg datetime

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-10 : 05:14:44

Hi,
The query below shows several fields.
At present you only see the first row of the repated KeyIDs which shows the latest DateTime.

Question:
I would like to work out the average Datetime in say seconds for each KeyID.

Thanks

select
a.RowNum,
a.KeyID,
a.KeyName,
a.DateTime
from
(select
ROW_NUMBER() over (partition by ckk.KeyID order by dcd.DateTime desc) as RowNum,
kck.KeyID,
kck.KeyName,
dcd.DateTime,
--avg(dcd.DateTime)
from
yKey as kck
inner join aData as dcd on kck.KeyID = dcd.KeyID
where
StatusEnum = 3) as a
where
a.RowNum = 1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 05:32:12

Depends a bit on what datetime is. I'm assuming a duration.

;with cte as
(select
ROW_NUMBER() over (partition by ckk.KeyID order by dcd.DateTime desc) as RowNum,
kck.KeyID,
kck.KeyName,
dcd.DateTime,
--avg(dcd.DateTime)
from
yKey as kck
inner join aData as dcd on kck.KeyID = dcd.KeyID
where
StatusEnum = 3
)
select
a.RowNum,
a.KeyID,
a.KeyName,
a.DateTime
from
cte a
join (select KeyID, avg(datediff(ss,0,a.DateTime)) from cte group by KeyID) b
on a.KeyID = b.KeyID
where a.RowNum = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-10 : 05:38:37

datetime field refers to a date with time i.e. 2008-04-12 17:11:50.647

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 05:40:33

What do you want the average of then in seconds?
Presumably not the average of those datetime values.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-10 : 05:44:34

I get this error when running your query:
The multi-part identifier "a.DateTime" could not be bound.
No column was specified for column 2 of 'b'.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-10 : 05:57:26

I would like to work out the average in say seconds or minutes so that I can see the average time between each datetime for each keyID.
For example:

KeyID dateTime
1 2010-12-06 10:57:54:844
1 2010-12-06 10:47:54:844
1 2010-12-06 10:33:54:844
2 2010-12-06 10:57:54:844
2 2010-12-05 10:47:54:844
2 2010-12-04 10:33:54:844
...
...

You can see fom the above example that the average datetime for each KeyID is about 10 minutes for KeyID 1 and about a day for KeyID 2
Hope you see what I mean.
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 06:06:32

So it's the difference between the first and last divided by the number

;with cte as
(select
ROW_NUMBER() over (partition by ckk.KeyID order by dcd.DateTime desc) as RowNum,
kck.KeyID,
kck.KeyName,
dcd.DateTime
from
yKey as kck
inner join aData as dcd on kck.KeyID = dcd.KeyID
where
StatusEnum = 3
)
select
a.RowNum,
a.KeyID,
a.KeyName,
a.DateTime,
avgdur = avg(datediff(ss,mindatetime,maxdatetime))/num
from
cte a
join (select KeyID, mindatetime=min(datetime),maxdaetime=max(datetime),num=count(*) from cte group by KeyID) b
on a.KeyID = b.KeyID
where a.RowNum = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-10 : 06:43:39

Hi,
Trying to implement your sql.
I get this error:
Column 'cte.RowNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any thoughts please?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 06:49:03

doh
No need for the avg() now

;with cte as
(select
ROW_NUMBER() over (partition by ckk.KeyID order by dcd.DateTime desc) as RowNum,
kck.KeyID,
kck.KeyName,
dcd.DateTime
from
yKey as kck
inner join aData as dcd on kck.KeyID = dcd.KeyID
where
StatusEnum = 3
)
select
a.RowNum,
a.KeyID,
a.KeyName,
a.DateTime,
avgdur = 1.0*datediff(ss,mindatetime,maxdatetime)/num
from
cte a
join (select KeyID, mindatetime=min(datetime),maxdaetime=max(datetime),num=count(*) from cte group by KeyID) b
on a.KeyID = b.KeyID
where a.RowNum = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-10 : 09:41:55

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources