The query profiler won't be able to do much with this mess. You have three levels of queries. As an example:
select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName in( select UserName from tbl_Panelist where tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' and tbl_Panelist.UserName in (select UserName from tbl_PanelistActivity where tbl_PanelistActivity.ActivityDate>(GETDATE()-180)) )
) as Active,
can be rewritten as
select count(id) from tbl_SurveyAssign
inner join tbl_Panelist on tbl_Panelist.xxx = tbl_SurveyAssign.xxx
inner join tbl_PanelistActivity on tbl_PanelistActivity.UserName = tbl_Panelist.Username
where ListName=a.ListName and tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0'
and tbl_PanelistActivity.ActivityDate>(GETDATE()-180)
And then that can be factored back out as:
with Active
(
select uniqueId, count(id) as Active from tbl_SurveyAssign
inner join tbl_Panelist on tbl_Panelist.xxx = tbl_SurveyAssign.xxx
inner join tbl_PanelistActivity on tbl_PanelistActivity.UserName = tbl_Panelist.Username
where ListName=a.ListName and tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0'
and tbl_PanelistActivity.ActivityDate > (GETDATE()-180)
)
select ListName, Active.Active
from tbl_SurveyAssign sa
inner join Active a on a.uniqueId = sa.uniqueId
group by ListName
This won't run, I don't know enough about your schema. But you can use CTEs to create tables that represent each value you want to pull out, and then join them all together.
Some more points:
Don't join on a username, which is a string. create a userid for each user, so that if they change their username, the whole DB does not need updating, and so things are faster.
Dont call tables tbl_xxx. Everyone knows it's a table.
Use aliases all the way through, instead of
inner join tbl_Panelist on tbl_Panelist.xxx = tbl_SurveyAssign.xxx
do
inner join tbl_Panelist p on p.xxx = sa.xxx
OF course, xxx is the column that can be used to join records. Without your schema, I don't know what that is.