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
 Script Library
 Looking for a more optimized code

Author  Topic 

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-21 : 01:38:26
is there a better code for 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 = '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


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

Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 01:41:14
maybe, but please post in appropriate forum. This is the forum for working script.

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





KH

Go to Top of Page

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-21 : 01:44:36
sorry. my bad

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

Andrew
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 01:48:13
your query is just a simple select with derived table. Are you experiencing slow performance with the query ?


KH

Go to Top of Page

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-21 : 01:51:43
sometimes. im running it in the reporting services of sql in .net 2005. maybe its my hardware performance.

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

Andrew
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-21 : 02:16:37
You can create a lookup table with columns for Subject, Stat, Phase, PhasePort and join it with your filteredtask table to avoid nested subqueries. Also multiple ORs can be rewritten as single IN condition.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 02:44:40
Maybe it is taking ling time because you are mixing INTEGERS and VARCHAR for PhaseSort
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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -