April 13, 2009 at 5:11 pm
This is probably very basic but here goes...
I've created a report in SQL 2005 reporting services that fuses together four types of data out of an accounting system. The data source for the report is based on four distinct views built for each of the four categories.
The "drilldown" part of this is easy - basic grouping and subtotaling in SQL2k5 reporting services. The summary that fuses together the four data sources has me stumped.
The report calls a stored procedure with two parameters - start and end date. The stored procedure uses those values to run four queries based on views to arrive at four separate tables. Each of these tables has just two columns: employee number and dollar amount. These four tables have ID's that are common to a fifth table that is a list of all employees in the system.
Here's an example of a result showing each result table then how I need it to appear.
tablename: Job
297 $267.66
tablename: Labor
297 $3536.14
236 $108.00
tablename: Materials
297 $3806.25
tablename: Revenue
297 $8696.82
(it's not that simple, the tables have some number of rows... the number of rows is totally different in each based on the result set.)
I need the result to be
Employee_ID Job Labor Materials Revenue
297 $267,66 $1536.14 $3806.25 $8696.82
236 $108.00
(For the row with 236, job, materials and revenue have null results with labor as $108)
I'm "sort of" arriving at that result with this query:
SELECT SY_Employee.First_Name, SY_Employee.Last_Name, SGA_Employee_Job.Amount AS Job,
CASE WHEN SY_Employee.Employee_Id = 394 THEN SGA_Labor.Amount - 1 ELSE SGA_Labor.Amount END AS Labor,
SGA_Materials.Amount AS Materials, SGA_Revenue.Amount AS Revenue, SY_Employee.Employee_Id
FROM SGA_Labor FULL OUTER JOIN
SGA_Revenue ON SGA_Labor.Employee_Id = SGA_Revenue.Employee_Id FULL OUTER JOIN
SGA_Materials ON SGA_Labor.Employee_Id = SGA_Materials.Employee_Id FULL OUTER JOIN
SGA_Employee_Job ON SGA_Labor.Employee_Id = SGA_Employee_Job.Employee_Id FULL OUTER JOIN
SY_Employee ON SGA_Labor.Employee_Id = SY_Employee.Employee_Id
The result set winds up being some 500 rows with data in maybe 30 of them at most. I suppress the rows that show only an employee name with conditional visibility in the report. My result sets always vary in size depending on the date range so there's really no way to choose the result of one of the tables starting with SGA to base the join off of and create a cleaner result. I've tried various types of joins but the one above gets "closest" to what I need.
Is there a better way to do this? The four tables have common employee ID's. I'd really like to arrive at the result on the SQL server and just use the report to present it but I can't figure out how to write a query that treats the employee ID's as "joined" and then puts the four columns next to the employee ID.
April 13, 2009 at 6:29 pm
I'm making some minor assumptions, and changed your query accordingly;
if there is an employee id in SGA_Labor, we KNOW he has a name, right? so we can assume that the join to get the name is an INNER join.
the other three amounts, i take it they can be null/don't exist for every employee, but since they will not exist unless there is an employee id, i made those LEFT OUTER JOINS instead of FULL;
finally any time an amount doesn't exist, I use ISNULL to return a zero instead.
see what this does for you...is it closer to your expected results?:
SELECT
SY_Employee.First_Name,
SY_Employee.Last_Name,
SGA_Employee_Job.Amount AS Job,
CASE
WHEN SY_Employee.Employee_Id = 394
THEN ISNULL(SGA_Labor.Amount,0.0) - 1
ELSE ISNULL(SGA_Labor.Amount,0.0)
END AS Labor,
ISNULL(SGA_Materials.Amount,0.0) AS Materials,
ISNULL(SGA_Revenue.Amount,0.0) AS Revenue,
SY_Employee.Employee_Id
FROM SGA_Labor
INNER JOIN SY_Employee ON SGA_Labor.Employee_Id = SY_Employee.Employee_Id
LEFT OUTER JOIN SGA_Revenue ON SGA_Labor.Employee_Id = SGA_Revenue.Employee_Id
LEFT OUTER JOIN SGA_Materials ON SGA_Labor.Employee_Id = SGA_Materials.Employee_Id
LEFT OUTER JOIN SGA_Employee_Job ON SGA_Labor.Employee_Id = SGA_Employee_Job.Employee_Id
Lowell
April 14, 2009 at 11:53 am
THANKS! 😀
That got me almost there. The result was still dropping rows where the employee number didn't exist in the table that inner joined SY_Employee. It occurred to me that if I join on the UNION of the employee numbers in that table to SY_Employee - that will get me all of the individual unique Employee_Id's.
The "final" (in quotes because not accepted by end user just yet, it ain't over 'til it's over...) result wound up being a view of those four databases which was simply:
SELECT Employee_Id
FROM dbo.SGA_Labor
WHERE (Employee_Id IS NOT NULL)
UNION
SELECT Employee_Id
FROM dbo.SGA_Revenue
WHERE (Employee_Id IS NOT NULL)
UNION
SELECT Employee_Id
FROM dbo.SGA_Materials
WHERE (Employee_Id IS NOT NULL)
UNION
SELECT Employee_Id
FROM dbo.SGA_Employee_Job
WHERE (Employee_Id IS NOT NULL)
Assume that SQL is simply called View for the "final" 😉 version of the query:
SELECT SY_Employee.First_Name, SY_Employee.Last_Name, ISNULL(SGA_Employee_Job.Amount, 0.0) AS Job,
CASE WHEN SY_Employee.Employee_Id = 394 THEN ISNULL(SGA_Labor.Amount, 0.0) - 1 ELSE ISNULL(SGA_Labor.Amount, 0.0) END AS Labor,
ISNULL(SGA_Materials.Amount, 0.0) AS Materials, ISNULL(SGA_Revenue.Amount, 0.0) AS Revenue, SY_Employee.Employee_Id
FROM View INNER JOIN
SY_Employee ON View.Employee_Id = SY_Employee.Employee_Id LEFT OUTER JOIN
SGA_Labor ON View.Employee_Id = SGA_Labor.Employee_Id LEFT OUTER JOIN
SGA_Revenue ON View.Employee_Id = SGA_Revenue.Employee_Id LEFT OUTER JOIN
SGA_Materials ON View.Employee_Id = SGA_Materials.Employee_Id LEFT OUTER JOIN
SGA_Employee_Job ON View.Employee_Id = SGA_Employee_Job.Employee_Id
That fused together the four result tables into a useful (and accurate) output for a report. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply