Sprocs OR Functions

  • dean gross

    SSCrazy

    Points: 2632

    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

  • Andrew Gothard-467944

    Hall of Fame

    Points: 3908

    SSIS into a reporting database, and do your reporting from that a viable option?

  • Paul White

    SSC Guru

    Points: 150442

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply