How can I pivot two inner joins

Question

I've been tasked to provide the number of orders completed per Employee for every hour that they have worked for throughout the day. The code that I created provide me that requirement but it is an eye-sore and difficult to understand.

To help management in reading my table I am trying to pivot the results.

I've read the Microsoft example but those don't include examples with joins.

Select 
t1.id
,t1.EmpName
,datepart(hour,t1.TimeWorked) as theHour
,CAST(t1.TimeWorked as date) as theDay
,t2.Ready
,Sum(t1.Tot_WT) as AccountsWorked
from Transaction as t1
INNER JOIN
(
    SELECT
    agent,id,Sum(Ready) as Ready,cast(Endtime as DATE) as theDay,
    FROM Login_Data
    WHERE [READY] <> [NOT_READY]
    GROUP BY agent,agentid,cast(Endtime as DATE)
)as t2
on t1.id = t2.id and t2.theDay = cast(t1.TimeWorked as date)
INNER JOIN
(
    Select 
    id, EmpName
    From EmployeeRoster
    Where Department in ( 'A','B','C','D')
)as t3
on t1.id = t3.id
Where CAST(t1.TimeWorked as date) = cast(GETDATE() as date)
group by t1.id,t1.EmpName,DATEPART(hour,t1.TimeWorked),CAST(t1.TimeWorked as date)  

Honestly the Ready column I can remove.

When executing this logic my results display one row for every hour for each agent

+-----+---------+---------+----------+-------+----------------+
| ID  | EmpName | thehour |  theday  | ready | AccountsWorked |
+-----+---------+---------+----------+-------+----------------+
| 123 | Austin  |       7 | 08-20-19 | 16001 |              7 |
+-----+---------+---------+----------+-------+----------------+

What I am trying to achieve with the pivot

+-----+---------+---+---+---+----+----+
| ID  | EmpName | 7 | 8 | 9 | 10 | 11 |
+-----+---------+---+---+---+----+----+
| 123 | Austin  | 7 | 6 | 9 |  2 | 16 |
+-----+---------+---+---+---+----+----+

Answer 1

You can try below query -

SELECT * FROM
(  Select t1.id
         ,t1.EmpName
         ,datepart(hour,t1.TimeWorked) as theHour
         ,CAST(t1.TimeWorked as date) as theDay
         ,t2.Ready
         ,Sum(t1.Tot_WT) as AccountsWorked
   from Transaction as t1
   INNER JOIN ( SELECT agent
                      ,id
                      ,Sum(Ready) as Ready
                      ,cast(Endtime as DATE) as theDay
                FROM Login_Data
                WHERE [READY] <> [NOT_READY]
                GROUP BY agent,agentid,cast(Endtime as DATE))as t2 on t1.id = t2.id
                                                                   and t2.theDay = cast(t1.TimeWorked as date)
   INNER JOIN ( Select id
                      ,EmpName
                From EmployeeRoster
                Where Department in ( 'A','B','C','D'))as t3 on t1.id = t3.id
Where CAST(t1.TimeWorked as date) = cast(GETDATE() as date)
group by t1.id
        ,t1.EmpName
        ,DATEPART(hour,t1.TimeWorked)
        ,CAST(t1.TimeWorked as date)) AS TEMP
PIVOT(
       SUM(AccountsWorked) FOR theHour IN (7,8,9,10,11)
     ) PVT;