average for sql query for datediff function

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

average for sql query for datediff function

Author

Topic

ahmedpower
Starting Member

6 Posts

Posted - 2012-12-11 : 06:45:34

Dear Team

Please support me to get the average for below sql query for datediff function.

SELECT a.[ticket_id]
,a.[created_on]
,a.[created_by]
,b.[done_by]
,b.[status_changed_to]
,b.[update_time]
,dbo.CastTime(datediff(s,a.created_on,b.update_time)) "Time Ticket"
FROM [ticket] a (nolock)
INNER JOIN [ticket_history] b (nolock)
ON a.ticket_id = b.ticket_id
where (datepart(mm,a.created_on)>=4 and datepart(mm,b.update_time)<=4)
and (datepart(yyyy,a.created_on)=2012 and datepart(yyyy,b.update_time)=2012)
and status_changed_to ='Close'
ORDER BY a.created_on

I really tired to do it unless I get the "Time Ticket"
but i need plus the average of total Time in sperate field

Ahmed Soliman

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-12-11 : 14:54:16

Ahmed,

I see you are using dbo.castTime function. Unfortunately, I can not tell what this function does.

here is the average as I see it:


	SELECT 
			 a.[ticket_id]
			,a.[created_on]
			,a.[created_by]
			,b.[done_by]
			,b.[status_changed_to]
			,b.[update_time]
			--,dbo.CastTime(datediff(s,a.created_on,b.update_time)) as Time_Ticket
			,datediff(minute, a.create_on, b.update_time) as time_ticket
		FROM [ticket] a (nolock)
		INNER JOIN [ticket_history] b (nolock)
		ON a.ticket_id = b.ticket_id
		where 
			(datepart(mm,a.created_on)>=4 
		 and datepart(mm,b.update_time)<=4)
		 and (datepart(yyyy,a.created_on)=2012 
		 and datepart(yyyy,b.update_time)=2012)
		 and status_changed_to ='Close'
		 ORDER BY a.created_on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources