Add column to the existing report

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Add column to the existing report

Author

Topic

nikoo56
Starting Member

26 Posts

Posted - 2011-10-24 : 19:47:16

I have a report with this query:

select source, created_by, first_name, last_name, type, dt_pro, dt_enrolled,counselor, email_1, wk_phone, address_1, city, state, zip, military_status, prospect_group, web_source
from vwInternetProspects3 p with (nolock)
where dt_pro between @start and @end
and source in (@Source)
and created_by in (@created_by)
and prospect_group in (@prospect_group)
order by p.counselor

I have 3 queries to add 3 column to this report. I am not sure how merge these 3 queries with the query in the top to generate a report.

select v.prospect_id, first_name, last_name, min(datediff(hour, v.dt_created, pc.dt_made)) as time_response
from vwInternetProspects3 v
left join tblC5ProspectCalls pc on pc.prospect_id = v.prospect_id
left join tblC5ProspectCallResultChoices cc on cc.call_result_id=pc.call_result_id
where dt_pro > '10/1/11' and call_result = 'tt'
group by v.prospect_id, first_name, last_name

select v.prospect_id, first_name, last_name, count(*) as total_attempt
from vwInternetProspects3 v
left join tblC5ProspectCalls pc on pc.prospect_id = v.prospect_id
left join tblC5ProspectCallResultChoices cc on cc.call_result_id=pc.call_result_id
where dt_pro > '10/1/11' and call_type = 'OB'
group by v.prospect_id, first_name, last_name

select v.prospect_id, first_name, last_name, count(*) as total_talk
from vwInternetProspects3 v
left join tblC5ProspectCalls pc on pc.prospect_id = v.prospect_id
left join tblC5ProspectCallResultChoices cc on cc.call_result_id=pc.call_result_id
where dt_pro > '10/1/11' and call_result = 'tt'
group by v.prospect_id, first_name, last_name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:07:43

you mean you need to add time_response,total_attempt & total_talk to main query results? what are common columns the different queries have?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nikoo56
Starting Member

26 Posts

Posted - 2011-10-25 : 17:54:47

Ok I figure out the query in the report is like this but count(*) as total_attempt and count(*) as total_talk right now show same value.

select source, created_by, first_name, last_name, type, dt_pro, dt_enrolled,

counselor, email_1, wk_phone, address_1, city, state, zip, military_status, prospect_group, web_source,p.prospect_id,min(datediff(hour, p.dt_created, pc.dt_made)) as time_response,count(*) as total_attempt,count(*) as total_talk

from vwInternetProspects3 p with (nolock)

left join tblC5ProspectCalls pc on pc.prospect_id = p.prospect_id

left join tblC5ProspectCallResultChoices cc on cc.call_result_id=pc.call_result_id

where dt_pro between @start and @end

and source in (@Source)

and created_by in (@created_by)

and prospect_group in (@prospect_group)

and (dt_pro > '10/1/11' and call_result = 'tt') or

(dt_pro > '10/1/11' and call_type = 'OB')or

(dt_pro > '10/1/11' and call_result = 'tt' )

group by source, created_by, first_name, last_name, type, dt_pro, dt_enrolled,

counselor, email_1, wk_phone, address_1, city, state, zip, military_status, prospect_group, web_source,p.prospect_id

order by p.counselor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 01:08:03

[code]
select *
from
(
select source, created_by, first_name, last_name, type, dt_pro, dt_enrolled,counselor, email_1, wk_phone, address_1, city, state, zip, military_status, prospect_group, web_source
from vwInternetProspects3 p with (nolock)
where dt_pro between @start and @end
and source in (@Source)
and created_by in (@created_by)
and prospect_group in (@prospect_group)
)t
inner join(select v.prospect_id, first_name, last_name, min(case when call_result = 'tt'
then datediff(hour, v.dt_created, pc.dt_made) else null end) as time_response,
count(case when call_type = 'OB' then 1 else null end) as total_attempt,
count(case when call_result = 'tt' then 1 else null end) as total_talk
from vwInternetProspects3 v
left join tblC5ProspectCalls pc on pc.prospect_id = v.prospect_id
left join tblC5ProspectCallResultChoices cc on cc.call_result_id=pc.call_result_id
where dt_pro > '10/1/11'
group by v.prospect_id, first_name, last_name
)u
on u.prospect_id = t.prospect_id
order by t.counselor
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources