SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Dynamic selection of table in SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jawad.hassan
Starting Member

Pakistan
6 Posts

Posted - 07/27/2013 :  05:08:17  Show Profile  Reply with Quote
Hi,
I am using SQL Server 2008 R2 and trying to develop below query for my report development in BIDS.

SELECT A.Date, A.Phone, B.Name, A.Campaign, A.Campaign_ID, A.Dialing_ID, B.Comments, B.Duration
FROM
(
SELECT d.Date, d.Phone, d.Campaign_ID, c.Campaign, d.Dialing_ID,
d.Skill_ID , (d.Key + d.Key_Day) AS Call_ID
FROM Results AS d INNER JOIN
Campaign AS c ON d.Campaign_ID = c.Campaign_ID INNER JOIN
Rule AS r ON d.Rule_ID = r.Rule_ID INNER JOIN
Import_Rule AS ir ON r.Rule_ID = ir.Rule_ID
WHERE (d.Date BETWEEN DATEADD(hour, CONVERT(INT, @StartHour), DATEADD(minute, CONVERT(INT, @StartMinute), @DateFrom))
AND DATEADD(hour, CONVERT(INT, @EndHour), DATEADD(minute, CONVERT(INT, @EndMinute), @DateTo)))
AND d.Campaign_ID IN (@Campaign_List)
) AS A LEFT OUTER JOIN

(
SELECT cd.Date, cd.Agent_ID, cd.Skill_ID,
(cd.Key_Day + cd.Key) AS Call_ID, cd.Duration, cd.Comments,
a.Name, a.Login_ID
FROM Call_Detail AS cd INNER JOIN
Agent AS a ON cd.Agent_ID = a.ID
WHERE (cd.Date BETWEEN DATEADD(hour, CONVERT(INT, @StartHour), DATEADD(minute, CONVERT(INT, @StartMinute), @DateFrom))
AND DATEADD(hour, CONVERT(INT, @EndHour), DATEADD(minute, CONVERT(INT, @EndMinute), @DateTo)))
) AS B ON A.Call_ID = B.Call_ID AND A.Skill_ID = B.Skill_ID LEFT OUTER JOIN
(
SELECT Dialing_ID, Phone
FROM DL_5017
)AS N ON N.Dialing_ID = A.Dialing_ID

In above query @StartHour, @EndHour, @StartMinute, @EndMinute, @DateFrom, @DateTo and @Campaign_List are all
report parameters created in BIDS and attached to their respective Datasets.

The problem i am facing is that i want to make the selection of tables for below query dynamic based on value selected in
@Campaign_List.

SELECT Dialing_ID, Phone
FROM DL_5017_5017
)AS N ON N.Dialing_ID = A.Dialing_ID

I am not getting how to use the selected value of @Campaign_List with case statement for dynamic selection of tables
in above query.

The possible values for @Campaign_List are (5017,5018,5019,5021) etc.

A prompt response in this regard will be highly appreciated.

Thanks.

Regards,
Jawad Hassan


Edited by - jawad.hassan on 07/27/2013 05:13:13

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/27/2013 :  08:41:14  Show Profile  Reply with Quote
you need dynamic sql for that. but its really ugly to use dynamic sql as report source. why do you need so many tables? why names like 5017 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jawad.hassan
Starting Member

Pakistan
6 Posts

Posted - 07/27/2013 :  11:01:06  Show Profile  Reply with Quote
To fulfill the report field requirements i need all these tables. These are not the names but the IDs. Any solution for this via dynamic query?

Jawad Hassan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/28/2013 :  03:11:29  Show Profile  Reply with Quote
do you mean they're values within the table field? or tablename themselves?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jawad.hassan
Starting Member

Pakistan
6 Posts

Posted - 07/28/2013 :  03:44:08  Show Profile  Reply with Quote
No these are not the tables but the values of report parameter and will be used in where clause as Campaign_ID IN (@Campaign_List).

Jawad Hassan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/28/2013 :  11:30:43  Show Profile  Reply with Quote
quote:
Originally posted by jawad.hassan

No these are not the tables but the values of report parameter and will be used in where clause as Campaign_ID IN (@Campaign_List).

Jawad Hassan



ok..in that case you could do something like below condition


WHERE ',' + @Campaign_List + ',' LIKE '%,' + CAST(Campaign_ID AS varchar(10)) + ',%'

If you pass comma separated va;ue through parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000