SELECT     phase, stat, subject, CASE WHEN phase = 'Initial/Data Collection' THEN '1' 
                                      WHEN phase = 'Screening' THEN '2' 
                                      WHEN phase = 'Assessment and Selection' THEN '3' 
                                      WHEN phase = 'Placement' THEN 4 END AS PhaseSort

	FROM  (SELECT subject, stat, CASE WHEN stat = 'Application Received' THEN 'Initial/Data Collection' 
                                  	WHEN stat = 'Shortlisted' OR
                                       	stat = 'For Screening' THEN 'Screening' 
	                                  WHEN stat = 'For Assessment' OR
	                                       stat = 'Passed Initial Evaluation' OR
        	                               stat = 'Passed Profiles Exam' OR
                	                       stat = 'Passed Technical Exam' THEN 'Assessment and Selection' 
                        	          WHEN stat = 'For Placement' THEN 'Placement' END AS phase

       		FROM (SELECT subject, CASE WHEN subject = 'Process Application' OR
                	                 	      subject = 'Application Received' THEN 'Application Received'
	                        	          WHEN subject = 'Screen Application' THEN 'For Screening' 
        	                        	  WHEN subject = 'Phone interview' THEN 'Shortlisted' 
 	        	                          WHEN subject = 'Initial Interview' THEN 'For Assessment' 
        	        	                  WHEN subject = 'Profiles assessment'THEN 'Passed Initial Evaluation'
                	        	          WHEN subject = 'Technical Exam and Interview' THEN 'Passed Profiles exam' 
                        	        	  WHEN subject = 'background and reference check' THEN 'Passed Technical Exam' 
                                		  WHEN subject = 'Job Offer' OR
                                 	      	  subject = 'Contract Signing' THEN 'For Placement' END AS stat

           		  FROM   dbo.filteredtask
        	  WHERE  (subject = 'application received') OR
	            (subject = 'process application') OR
                    (subject = 'screen application') OR
                    (subject = 'initial interview') OR
                    (subject = 'profiles assessment') OR
                    (subject = 'technical exam and interview') OR
                     subject = 'background and reference check' OR
                     subject = 'phone interview' OR
                     subject = 'shortlisted' OR
                     subject = 'For Placement' OR
                     subject = 'job offer' OR
                     subject = 'contract signing') Phases) stats
ORDER BY phasesort

quote:

---

This section is for any originally written scripts you'd like to share with the group. Please don't repost content from books or other web sites unless you are the original author or copyright holder.

---

quote:

---

hereafter avoid this

---

SELECT		Phase,
		Stat,
		Subject,
		CASE
			WHEN Phase = 'Initial/Data Collection' THEN '1' 
			WHEN Phase = 'Screening' THEN '2' 
			WHEN Phase = 'Assessment and Selection' THEN '3' 
			WHEN Phase = 'Placement' THEN '4'
		END AS PhaseSort
FROM		(
			SELECT	Subject,
				Stat,
				CASE
					WHEN Stat = 'Application Received' THEN 'Initial/Data Collection'
					WHEN Stat IN ('Shortlisted', 'For Screening') THEN 'Screening' 
					WHEN Stat IN ('For Assessment', 'Passed Initial Evaluation', 'Passed Profiles Exam', 'Passed Technical Exam') THEN 'Assessment and Selection'
					WHEN Stat = 'For Placement' THEN 'Placement'
				END AS Phase
       			FROM	(
					SELECT	Subject,
						CASE
							WHEN Subject IN ('Process Application', 'Application Received') THEN 'Application Received'
							WHEN Subject = 'Screen Application' THEN 'For Screening' 
							WHEN Subject = 'Phone interview' THEN 'Shortlisted' 
							WHEN Subject = 'Initial Interview' THEN 'For Assessment' 
							WHEN Subject = 'Profiles assessment'THEN 'Passed Initial Evaluation'
							WHEN Subject = 'Technical Exam and Interview' THEN 'Passed Profiles exam' 
							WHEN Subject = 'background and reference check' THEN 'Passed Technical Exam' 
							WHEN Subject IN ('Job Offer', 'Contract Signing') THEN 'For Placement'
						END AS Stat
           				FROM	dbo.FilteredTask
        				WHERE	Subject = 'application received'
						OR Subject = 'process application'
						OR Subject = 'screen application'
						OR Subject = 'initial interview'
						OR Subject = 'profiles assessment'
						OR Subject = 'technical exam and interview'
						OR Subject = 'background and reference check'
						OR Subject = 'phone interview'
						OR Subject = 'shortlisted'
						OR Subject = 'For Placement'
						OR Subject = 'job offer'
						OR Subject = 'contract signing'
				) AS Phases
			) AS Stats
ORDER BY	PhaseSort