How can I get the Average Datediff? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How can I get the Average Datediff?

Author

Topic

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-22 : 22:15:27

I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.

All applicants must go through stage 1 before he can go to stage 2 and so on.

The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.

I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.

Here is a sample output that I have.Each phase has a different color.

Here is the code that i used

SELECT     subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial,
		(CASE	phase	WHEN 'Initial/Data Collection' THEN 1
				WHEN 'Screening' THEN 2
				WHEN 'Assesment and Selection' THEN 3
				WHEN 'Placement' THEN 4
		END) AS Phasesort
	FROM	(
			SELECT     subject, regardingobjectidname, createdon, modifiedon, stat, initial,
				(CASE	stat	WHEN 'Application Recieved' THEN 'Initial/Data Collection'
						WHEN 'For Screening' THEN 'Screening'
						WHEN 'Shortlisted' THEN 'Screening' 
						WHEN 'For Assesment' THEN 'Assesment and Selection'
						WHEN 'For Assesment' THEN 'Assesment and Selection'
						WHEN 'Passed Initial Interview' THEN 'Assesment and Selection'
						WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection'
						WHEN 'Passed Technical Exam' THEN 'Assesment and Selection'
						WHEN 'For Placement' THEN 'Placement'
				END) AS phase
		FROM	(
				SELECT     subject, regardingobjectidname, createdon, modifiedon, initial,
					(CASE	initial	WHEN '1 stage' THEN 'Application Recieved'
							WHEN '2 stage' THEN 'Application Recieved' 
							WHEN '3 stage' THEN 'For Screening'
							WHEN '4 stage' THEN 'Shortlisted'
							WHEN '5 stage' THEN 'For Assesment'
							WHEN '6 stage' THEN 'Passed Initial Interview'
							WHEN '7 stage' THEN 'Passed Profiles Assesment'
							WHEN '8 stage' THEN 'Passed Technical Exam'
							WHEN '9 stage' THEN 'For Placement'
							WHEN '10 stage' THEN 'For Placement'
					END) AS stat
			FROM	(
					SELECT     subject, regardingobjectidname, createdon, modifiedon,
						(CASE	subject	WHEN N'application received' THEN '1 stage'
								WHEN 'process application' THEN '2 stage' 
								WHEN 'screen application' THEN '3 stage'
								WHEN 'Phone interview' THEN '4 stage'
								WHEN N'initial interview' THEN '5 stage'
								WHEN 'profiles assessment' THEN '6 stage'
								WHEN 'technical exam and interview' THEN '7 stage'
								WHEN 'background and reference check' THEN '8 stage'
								WHEN 'Job Offer' THEN '9 stage'
								WHEN 'Contract Signing' THEN '10 stage'
						END) AS initial
				FROM	(SELECT subject, regardingobjectidname, createdon, modifiedon
						FROM	FilteredTask
						WHERE     (subject IN ('application received', 'process application', 
								'screen application', 'initial interview', 'profiles assessment', 
								'technical exam and interview', 'background and reference check', 'job offer', 
								'contract signing'))
				UNION ALL
					SELECT subject, regardingobjectidname, createdon, modifiedon
						FROM	FilteredPhoneCall
						WHERE	subject = 'phone interview'
					) AS Orion
				) AS Initials
			) AS Phases
	) AS Stats
ORDER BY regardingobjectidname, initial

I appreciate your help.

Thanks a lot.

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 22:20:31

[code]
select Phasesort, avg(datediff(minute, createdon, modifiedon))
from (
< your existing query here>
) d
group by Phasesort
[/code]

KH

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-22 : 22:33:20

Thanks KH but i have this error message

I think I wrote your code incorrectly. Here is what i have done

SELECT Phasesort, avg(datediff(minute, createdon, modifiedon))
	FROM	(
	SELECT     subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial,
			(CASE	phase	WHEN 'Initial/Data Collection' THEN 1
					WHEN 'Screening' THEN 2
					WHEN 'Assesment and Selection' THEN 3
					WHEN 'Placement' THEN 4
			END) AS Phasesort
		FROM	(
				SELECT     subject, regardingobjectidname, createdon, modifiedon, stat, initial,
					(CASE	stat	WHEN 'Application Recieved' THEN 'Initial/Data Collection'
							WHEN 'For Screening' THEN 'Screening'
							WHEN 'Shortlisted' THEN 'Screening' 
							WHEN 'For Assesment' THEN 'Assesment and Selection'
							WHEN 'For Assesment' THEN 'Assesment and Selection'
							WHEN 'Passed Initial Interview' THEN 'Assesment and Selection'
							WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection'
							WHEN 'Passed Technical Exam' THEN 'Assesment and Selection'
							WHEN 'For Placement' THEN 'Placement'
					END) AS phase
			FROM	(
					SELECT     subject, regardingobjectidname, createdon, modifiedon, initial,
						(CASE	initial	WHEN '1 stage' THEN 'Application Recieved'
								WHEN '2 stage' THEN 'Application Recieved' 
								WHEN '3 stage' THEN 'For Screening'
								WHEN '4 stage' THEN 'Shortlisted'
								WHEN '5 stage' THEN 'For Assesment'
								WHEN '6 stage' THEN 'Passed Initial Interview'
								WHEN '7 stage' THEN 'Passed Profiles Assesment'
								WHEN '8 stage' THEN 'Passed Technical Exam'
								WHEN '9 stage' THEN 'For Placement'
								WHEN '10 stage' THEN 'For Placement'
						END) AS stat
				FROM	(
						SELECT     subject, regardingobjectidname, createdon, modifiedon,
							(CASE	subject	WHEN N'application received' THEN '1 stage'
									WHEN 'process application' THEN '2 stage' 
									WHEN 'screen application' THEN '3 stage'
									WHEN 'Phone interview' THEN '4 stage'
									WHEN N'initial interview' THEN '5 stage'
									WHEN 'profiles assessment' THEN '6 stage'
									WHEN 'technical exam and interview' THEN '7 stage'
									WHEN 'background and reference check' THEN '8 stage'
									WHEN 'Job Offer' THEN '9 stage'
									WHEN 'Contract Signing' THEN '10 stage'
							END) AS initial
					FROM	(SELECT subject, regardingobjectidname, createdon, modifiedon
							FROM	FilteredTask
							WHERE     (subject IN ('application received', 'process application', 
									'screen application', 'initial interview', 'profiles assessment', 
									'technical exam and interview', 'background and reference check', 'job offer', 
									'contract signing'))
					UNION ALL
						SELECT subject, regardingobjectidname, createdon, modifiedon
							FROM	FilteredPhoneCall
							WHERE	subject = 'phone interview'
						) AS Orion
					) AS Initials
				) AS Phases
		) AS Stats
	ORDER BY regardingobjectidname, initial
	) d
GROUP BY Phasesort

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 22:38:17

"i have this error message"
What is the error message ?

KH

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-22 : 22:41:26

Its this error message which I posted with the image file
It says "An error occured while executing the query.
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specifiec."

Thank You.

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 22:43:55

remove the entire ORDER BY statement in your query

ORDER BY regardingobjectidname, initial

KH

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 22:45:07

I think it's pretty clear error message, right?
It informed you what is wrong and what you needs to do to resolve it.

SELECT ...
	FROM	(
                  SELECT TOP 100 PERCENT ...
                  ....
                  ORDER BY ...
                )
GROUP BY ...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-23 : 01:06:31

Would it be possible that i can still see the table from my existing query?

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 01:52:08

quote:
Originally posted by drewgeezmoe

Would it be possible that i can still see the table from my existing query?

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

What do you mean ?

KH

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-23 : 02:12:42

I already got my table working.

Thanks a lot to you harsh and kh

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources