May 5, 2009 at 9:46 am
I need to run the same select statement with different where clauses against 4 different databases. I inititally created one sproc with 6 selects and UNION ALL to create a total result set. I've learned this may not have been the best appoach and I'm trying to decide how to improve my design before it gets even messier. The end goal is to provide a set of data from 4 dbs to a reporting services report. Any suggestions would be greatly appreciated. I'm thinking that I could pass in the name of the database as a parameter to a sproc or a udf, but I'm not sure which to use or how to create the necessary dynamic sql.
A code snippet showing the 1st two of 6 similar select statement is below. :
SELECT LD.Library
, LD.Cost_Period_Year
, LD.Cost_Period_Number
, SUM(LD.Total_Hours) AS 'Sum of Total_Hours'
, OP.Description1 AS Division
, OP.Pool_Master AS DivisionSortOrder
, 'Direct Labor' AS [Labor Group]
, 6 AS LaborGroupSortOrder
, LD.Posting_Date
, dbo.udf_WorkingDaysInFinPer (LD.Cost_Period_Year, LD.Cost_Period_Number, dbo.udf_LastFriday(CONVERT( varchar, GETDATE() , 101))) as 'WorkingDays'
FROM PARFLS.dbo.OverheadPool AS OP
INNER JOIN PARFLS.dbo.WorkLocation AS WL
ON OP.Pool_Master = WL.Overhead_Pool
INNER JOIN PARFLS.dbo.HrEmployee AS HrE
INNER JOIN PARFLS.dbo.LaborDetail AS LD
ON HrE.Employee_Number = LD.Employee_Number
INNER JOIN PARFLS.dbo.JobMaster AS JM
ON LD.Job_Number = JM.Job_Number
INNER JOIN PARFLS.dbo.HrMyData AS HrMD
ON HrE.Employee_Number = HrMD.Employee_Number
ON WL.Work_Location = JM.Office_Number
WHERE (JM.Job_Number BETWEEN 1 AND 799999)
AND (JM.Office_Number BETWEEN '100' AND '899')
AND LD.Posting_Date BETWEEN @datPosting and DATEADD(d,1,dbo.udf_LastFriday(CONVERT( varchar, GETDATE() , 101)))
GROUP BY LD.Library
, JM.Office_Number
, LD.Employee_HR_Division_Number
, LD.Cost_Period_Year
, LD.Cost_Period_Number
, HrMD.Division
, LD.Posting_Date
, OP.Description1
, OP.Pool_Master
--Non BD Hours
UNION ALL
SELECT
LD.Library
, LD.Cost_Period_Year
, LD.Cost_Period_Number
, SUM(LD.Total_Hours) AS 'Sum of Total_Hours'
, OP.Description1 AS Division
, OP.Pool_Master AS DivisionSortOrder
, 'Non BD' AS [Labor Group]
, 1 AS LaborGroupSortOrder
, LD.Posting_Date
, dbo.udf_WorkingDaysInFinPer (LD.Cost_Period_Year, LD.Cost_Period_Number, dbo.udf_LastFriday(CONVERT( varchar, GETDATE() , 101))) as 'WorkingDays'
FROM PARFLS.dbo.OverheadPool AS OP
INNER JOIN PARFLS.dbo.WorkLocation AS WL
ON OP.Pool_Master = WL.Overhead_Pool
INNER JOIN PARFLS.dbo.HrEmployee AS HrE
INNER JOIN PARFLS.dbo.LaborDetail AS LD
ON HrE.Employee_Number = LD.Employee_Number
INNER JOIN PARFLS.dbo.JobMaster AS JM
ON LD.Job_Number = JM.Job_Number
INNER JOIN PARFLS.dbo.HrMyData AS HrMD
ON HrE.Employee_Number = HrMD.Employee_Number
ON WL.Work_Location = JM.Office_Number
WHERE ((JM.Office_Number BETWEEN '100' AND '899')
AND (LD.Job_Number BETWEEN 800100 AND 899999)
OR(JM.Office_Number BETWEEN '100' AND '899')
AND (LD.Job_Number BETWEEN 800100 AND 899999))
AND LD.Posting_Date BETWEEN @datPosting and DateAdd(d,1,dbo.udf_LastFriday(CONVERT( varchar, GETDATE() , 101)))
GROUP BY LD.Library
, JM.Office_Number
, LD.Employee_HR_Division_Number
, LD.WBS_Number
, LD.Cost_Period_Year
, LD.Cost_Period_Number
, HrMD.Division
, LD.Posting_Date
, OP.Description1
, OP.Pool_Master
HAVING (LD.WBS_Number BETWEEN '00000' AND '05999') AND (SUM(LD.Total_Hours) <> $0) OR
(LD.WBS_Number BETWEEN '08000' AND '99999') AND (SUM(LD.Total_Hours) <> $0)
thanks
Dean
May 12, 2009 at 10:58 am
SSIS into a reporting database, and do your reporting from that a viable option?
May 12, 2009 at 11:22 pm
Hey Dean,
Could post a brief snippet or two to illustrate the similarities and differences between the queries please?
There are a number of options here, but most depend on how similar things are...
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply