AVG Function - 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

General SQL Server Forums

New to SQL Server Programming

AVG Function

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2009-08-12 : 14:33:26

what is the average syntax in SQL for a query when you have two tables joined and the datediff function used to calculate the number of days for example between participationdate and actualbegindate??

Roger DeFour

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-12 : 14:36:54

select avg(datediff(minute, participationdate, actualbegindate) / 1440.0)

N 56°04'39.26"
E 12°55'05.63"

Trininole
Yak Posting Veteran

83 Posts

Posted - 2009-08-13 : 10:15:57

where exactly in the query would i place that select avg statement?
Here is my query:

select distinct cp.col_appid, cp.col_participationdate, e.col_actbegindate, e.col_programtypeid,
datediff(dd,cp.col_participationdate, e.col_actbegindate)as calc_days
from tbl_caseparticipation cp join tbl_enrollment e
ON cp.col_appid=e.col_appid
and e.col_programtypeid='2'
and convert(smalldatetime, convert(varchar(10), cp.col_participationdate, 101)) >='2009-01-01'
and convert(smalldatetime, convert(varchar(10), e.col_actbegindate, 101)) >='2009-01-01'

Roger DeFour

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 10:24:19

Here is one shot because you didn't tell me what the expected output should look like

SELECT		cp.col_appid,
		cp.col_participationdate,
		e.col_actbegindate,
		e.col_programtypeid,
		AVG(DATEDIFF(DAY, cp.col_participationdate, e.col_actbegindate) / 1440.0) OVER (PARTITION BY cp.col_appid) AS calc_days
from		tbl_caseparticipation as cp 
inner join	tbl_enrollment as e ON e.col_appid = cp.col_appid
			and e.col_programtypeid = 2
WHERE		cp.col_participationdate >= '20090101'
		e.col_actbegindate >= '20090101'

N 56°04'39.26"
E 12°55'05.63"

Trininole
Yak Posting Veteran

83 Posts

Posted - 2009-08-13 : 10:49:36

here is the output of what it would look like prior to you giving me the
avg syntax.

col_appid col_participationdate col_actbegindate col_programtypeid

16066167 2009-03-23 2009-04-01 2

calc_days
9

the calc_days heading is meant to be on the right of the col_programtype id but i didnt have any space to put it there but here is an example of 1 of the results after running the original query i sent to you prior to adding the avg syntax in there. Hope this helps.

Roger DeFour

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 10:51:57

[code]SELECT cp.col_appid,
cp.col_participationdate,
e.col_actbegindate,
e.col_programtypeid,
AVG(DATEDIFF(HOUR, cp.col_participationdate, e.col_actbegindate) / 24.0E) OVER (PARTITION BY cp.col_appid) AS calc_days
from tbl_caseparticipation as cp
inner join tbl_enrollment as e ON e.col_appid = cp.col_appid
and e.col_programtypeid = 2
WHERE cp.col_participationdate >= '20090101'
e.col_actbegindate >= '20090101'[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources