datetime - Finding the average time (in seconds) for multiple rows in SQL -

datetime - Finding the average time (in seconds) for multiple rows in SQL -

i'm trying find average between 2 different dates multiple rows. each created date subtract assigned date. find average across datediff (looking result in seconds)

declare @offset int; declare @st_date date; declare @en_date date; set @offset = (@browsertimezoneoffset); set @st_date = (@st_datein); set @en_date = (@en_datein); select avg(subtract) [avg assigned time] (select datediff(ss, ign.createddate, ign.assigndate) subtract (select dateadd(mi,@offset,s.createddatetime) createddate ,dateadd(mi,@offset,w.audithistorydatetime) assigndate servicereq s, audit_servicereq w w.ownerteam_ischanged = n'true' --owner changed @ point , s.subject = n'general request' , w.audithistoryuser != n'internalservices' --doesn't include done automagically scheme , w.audithistoryeventtype != 1 --doesn't include creation , dateadd(mi,@offset,s.createddatetime) >= @st_date --greater start date , dateadd(mi,@offset,s.createddatetime) <= @en_date --less end date , s.createdbyteam in ('it helpdesk','unassigned') --check team ) ign come in code here ) dp

the above isn't returning accurate info i'm pretty new @ , i'm not sure i'm doing wrong.

any help appreciated

while might able create work, it's not best way approach it.

i'd either retrieve rows , calculation in code (if it's guaranteed relatively little number of rows), or write stored procedure retrieves rows cursor , iterates them calculate want.

either approach much more readable, , hence much more maintainable extremely complex sql query.

i'd expect perform better, too. on years, i've consistently seen simple selects cursor-based access calculations outperform complex select statements.

sql datetime