Select database then run query

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 2005 Forums

Transact-SQL (2005)

Select database then run query

Author

Topic

d9cody
Starting Member

2 Posts

Posted - 2011-08-29 : 11:01:33

I'm looking for a way to select a database and then run a query based on the database selected. Basically, I want to pick a client (database) and then run a query to see results just for the specified client. I want this to eventually be a spreadsheet in excel where a user can choose a client and then the workbook is updated accordingly for just that client. Any help would be appreciated, here is an example of what I have currently:

-- Trim Audit PAT Data Iteration
select
a.Run_ID as [Run ID],
a.Trim_Audit_ID as [Trim Audit ID],
a.Trim_ID as [Trim ID],
a.Step,
a.Target_Table as [Target Table],
b.Run_Database as [Run Database],
a.Trim_Step_Name as [Trim Step Name],
a.Case_or_Visits as [Case or Visits],
a.Case_or_Visits_Remaining as [Case or Visits Remaining],
a.Case_or_Lines as [Case or Lines],
a.Case_or_Lines_Remaining as [Case or Lines Remaining],
a.Allowed_Amt as [Allowed Amount],
a.Allowed_amt_Remaining as [Allowed Amount Remaining],
a.Charges as [Charges],
a.Charges_Remaining as [Charges Remaining],
a.Costs,
a.Costs_Remaining as [Costs Remaining],
a.LOS as [Length of Stay],
a.LOS_Remaining as [Length of Stay Remaining],
a.Average_LOS as [Average Length of Stay],
a.Average_LOS_Remaining as [Average Length of Stay Remaining],
b.Run_Date as [Run Date],
c.Project_Name as [Project]
from Project_BCBSMT_IP_Payment_20110615_Dev.dbo.App_Trim_Audit a
JOIN Treo_PAT_Repository.dbo.app_Job_Task_Log b on (a.Run_ID = b.Run_ID) --and b.Task_Order = a.Step)
JOIN Treo_PAT_Repository.dbo.app_Project c on (b.Run_Database = c.Database_Name)
WHERE b.Status = 'Job Ended'
group by
a.Trim_Audit_ID,
a.Trim_ID,
a.Run_ID,
a.Step,
a.Target_Table,
a.Trim_Step_Name,
a.Case_or_Visits,
a.Case_or_Visits_Remaining,
a.Case_or_Lines,
a.Case_or_Lines_Remaining,
a.Allowed_Amt,
a.Allowed_amt_Remaining,
a.Charges,
a.Charges_Remaining,
a.Costs,
a.Costs_Remaining,
a.LOS,
a.LOS_Remaining,
a.Average_LOS,
a.Average_LOS_Remaining,
b.Run_Date,
b.Run_Database,
c.Project_Name
order by b.Run_Date desc

Any help would be much appreciated! Thank you

-Dan

-Dan Cody

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 11:03:33

sorry didnt understand why db changes at runtime? i dont think this is a good approach. why cant all tables reside in same db?

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

d9cody
Starting Member

2 Posts

Posted - 2011-08-29 : 11:12:32

sorry, maybe I wasn't clear enough...

Each client has a different database. The example uses Project_BCBSMT_IP_Payment_20110615_Dev but there are other clients such as Project_BCBSMA_IP_Payment_20110818_Dev. I would like to run the SQL based on a prompt to the end user in excel. I also have a table in Treo_PAT_Repository that contains all of the current client names...maybe a join to that would work?

-Dan Cody

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 11:38:08

why are keeping it in separate db? why cant you put all the client details in single table with client id as a field to denote related client id.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources