May 25, 2012 at 12:10 am
SELECT
completed_count = ( SELECT COUNT(1)
FROM dbo.[RESPONDENT] r WITH ( NOLOCK )
WHERE r.acct_id = 3279
AND r.acct_id = s.acct_id
AND r.survey_stub = s.survey_stub
AND r.respondent_status_id = 4
) ,
invited_count = ( SELECT COUNT(1)
FROM dbo.[RESPONDENT] r WITH ( NOLOCK )
WHERE r.acct_id = 3279
AND r.acct_id = s.acct_id
AND r.survey_stub = s.survey_stub
)
FROM dbo.[SURVEY] s WITH ( NOLOCK )
WHERE s.acct_id = 3279
my requirement is to use RESPONDENT table only once
i want to avoid double can of table here , i tried to use CTE but couldnt , please help.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 25, 2012 at 12:55 am
Did you try using a left join ?
SELECT completed_count = sum(case r.respondent_status_id
when 4 then 1
else 0
end)
, invited_count = sum(case when r.acct_id is null then 0
else 1
end)
FROM dbo.[SURVEY] s WITH ( NOLOCK )
left join dbo.[RESPONDENT] r WITH ( NOLOCK )
on r.acct_id = s.acct_id
AND r.survey_stub = s.survey_stub
WHERE s.acct_id = 3279
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 25, 2012 at 2:11 am
thanks johan
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply