CODEWITHSUNDEEP
sqlsql-serverreporting-services
Vishnu Suresh
Vishnu Suresh

Reputation: 187

Creating a SSRS report from 2 Tables

I Have two tables in a sql server database. Here's my First table,Table1

 +------------+------------------+----------------+
| Project ID | Project Manager  | Approved Hours |
+------------+------------------+----------------+
|          1 |             Mr.A |            120 |
|          2 |             Mr.B |            100 |
+------------+------------------+----------------+

Here's my Second Table,Table 2

+-----------+-----------------+-----------+----------+---------------+
| ProjectID | Project Manager | Personnel | Week No. | Working Hours |
+-----------+-----------------+-----------+----------+---------------+
|         1 | Mr.A            | Tom       |        1 |            20 |
|         1 | Mr.A            | Tom       |        2 |            20 |
|         1 | Mr.A            | Tom       |        3 |            10 |
|         1 | Mr.A            | Harry     |        1 |            20 |
|         1 | Mr.A            | Harry     |        2 |            20 |
|         1 | Mr.A            | Harry     |        3 |            20 |
|         2 | Mr.B            | Tom       |        1 |            20 |
|         2 | Mr.B            | Tom       |        2 |            10 |
|         2 | Mr.B            | Tom       |        3 |            20 |
|         2 | Mr.B            | Harry     |        1 |            20 |
|         2 | Mr.B            | Harry     |        2 |            15 |
+-----------+-----------------+-----------+----------+---------------+

I would like to create a ssrs report that looks like this.I'm using the 2012 version. Actual Hours being the sum of working Hours for each Project.

 +------------+-----------------+----------------+--------------+
| Project ID | Project Manager | Approved Hours | Actual Hours |
+------------+-----------------+----------------+--------------+
|          1 | Mr.A            |            120 |          110 |
|          2 | Mr.B            |            100 |           85 |
+------------+-----------------+----------------+--------------+

I'm kind of new to SQL, Can I get this done with a single query.

Upvotes: 0

Views: 584

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

As @jarlh Suggest simply do INNER JOIN & group by as below :

SELECT T.[Project ID],
       T.[Project Manager],
       T.[Approved Hours],
       SUM(T1.[Working Hours]) [Actual Hours]
FROM Table1 T
     INNER JOIN Table2 T1 ON T.[Project ID] = T1.[Project ID]
GROUP BY T.[Project ID],
         T.[Project Manager],
         T.[Approved Hours];

Result :

+------------+-----------------+----------------+--------------+
| Project ID | Project Manager | Approved Hours | Actual Hours |
+------------+-----------------+----------------+--------------+
|          1 | Mr.A            |            120 |          110 |
|          2 | Mr.B            |            100 |           85 |
+------------+-----------------+----------------+--------------+

Upvotes: 2

Related Questions