SQL Merge 2结果集

I have a query that gets the sum of a user's activity hours and I also have a query that gets the sum of activity hours of each person that THAT specific user requested for.

For example..
John Doe requested 2 hours of activity for Jane Doe,Steve Job,and John Doe(Himself)
Steve Job requested 4 hours of activity for John Doe

So.. when John Doe generates a report of his activities
The report should look like this...

|    Users     |  Hours    | //JOHN DOE's ACTIVITIES                                
----------------------------                                                               
|  John Doe    |  6 Hours  |--> 2 hrs from his own request + 4 hrs from Steve Job's Request   
|  Jane Doe    |  2 Hours  |--> 2 hrs from John Doe's request                              
|  Steve Jobs  |  2 Hours  |--> 2 hrs from John Doe's request    

相反,我得到了...。

|  Users       |  Hours   | //JOHN DOE's ACTIVITIES
---------------------------
| John Doe     |  8 Hours |-->2 hrs from his own request + 4 hrs from Steve Job's Request + (2 hrs again from his own request)
| Jane Doe     |  2 Hours |--> 2 hrs from John Doe's request
| Steve Jobs   |  2 Hours |--> 2 hrs from John Doe's request

这应该适用于其他用户,显示其所有活动时间(由他们或其他用户创建),并显示他们请求活动的用户的所有活动时间。

Basically, I have a pivot table, event_table and event_table_users

declare @hrs as decimal(18,3) = (select sum(hrs) from event_table_users where user_id = @user_id and (date_start between @date_start and @date_end) )

SELECT userID, sum(Hours) from (
    SELECT @user_id as userID, @hrs as Hours
         UNION ALL
    SELECT event_table_user.user_id as userID, 
         sum(event_table_user.hrs) 
    FROM event_table INNER JOIN event_table_users ON event_table.id = event_table_user.request_id
    WHERE event_table.requested_by = @requested_by 
    AND (date_start between @date_start and @date_end)
    ) GROUP BY userID

SAMPLE DATA
Let's say their respective user_id are John Doe = 001, Jane Doe = 002, Steve Jobs = 003

<!--  event_table -->
ID |  user_id       |   requested_by  |  hrs   |
------------------------------------------------
1  |  002,003,001   |     001         |  2.000 |
2  |  001           |     003         |  4.000 |

<!-- event_table_user -->
ID | request_id     |  user_id  | hrs   |
-----------------------------------------
1  |     1          |  002      | 2.000 |
2  |     1          |  003      | 2.000 |
3  |     1          |  001      | 2.000 |
4  |     2          |  001      | 4.000 |

期望的结果

|    Users     |  Hours    | //JOHN DOE's ACTIVITIES                                
----------------------------                                                               
|  John Doe    |  6 Hours  |--> 2 hrs from his own request + 4 hrs from Steve Job's Request   
|  Jane Doe    |  2 Hours  |--> 2 hrs from John Doe's request                              
|  Steve Jobs  |  2 Hours  |--> 2 hrs from John Doe's request

实际结果

|  Users       |  Hours   | //JOHN DOE's ACTIVITIES
---------------------------
| John Doe     |  8 Hours |-->2 hrs from his own request + 4 hrs from Steve Job's Request + (2 hrs again from his own request)
| Jane Doe     |  2 Hours |--> 2 hrs from John Doe's request
| Steve Jobs   |  2 Hours |--> 2 hrs from John Doe's request
评论