server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.
select a.id, isnull(b.advisement_satisfaction_yes, 0) as
advisement_satisfaction_yes,
isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,
case
when isnull(b.advisement_satisfaction_yes, 0) >
isnull(c.advisement_satisfaction_no, 0) then 'YES'
when isnull(b.advisement_satisfaction_yes, 0) <
isnull(c.advisement_satisfaction_no, 0) then 'NO'
when isnull(b.advisement_satisfaction_yes, 0) =
isnull(c.advisement_satisfaction_no, 0) then 'TIE'
end as Satisfied_With_Advisement
from a
left Join
(select id, count(answer_text) as Advisement_Satisfaction_yes from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'yes'
GROUP BY id) b
on a.id = b.id
left join
(select id, count(answer_text) as Advisement_Satisfaction_NO from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'NO'
GROUP BY id) c
on a.id = b.id
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
Razvan|||AMAZING...THANK YOU SO MUCH!
Razvan Socol wrote:
Quote:
Originally Posted by
The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
>
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
>
Razvan
