WITH reqcom AS (
SELECT p.Id, p.NickName, p.LastName, COUNT(cr.Id) AS RequestsCompleted
  FROM ConnectionRequest cr
	JOIN PersonAlias pa ON (cr.ConnectorPersonAliasId = pa.Id)
	JOIN Person p ON (pa.PersonId = p.Id)
 WHERE cr.ConnectionOpportunityId IN (49, 50, 51) -- CHANGE THESE IDS
   AND cr.ConnectionState IN (1, 3) -- 1 = Inactive, 3 = Connected
 GROUP BY p.Id, p.NickName, p.LastName
 ), totreq AS (
	 SELECT p.Id, p.NickName, p.LastName, COUNT(cr.Id) AS TotalRequests
	  FROM ConnectionRequest cr
		JOIN PersonAlias pa ON (cr.ConnectorPersonAliasId = pa.Id)
		JOIN Person p ON (pa.PersonId = p.Id)
	 WHERE cr.ConnectionOpportunityId IN (49, 50, 51) -- CHANGE THESE IDS
	 GROUP BY p.Id, p.NickName, p.LastName
), dayssince AS (
	SELECT p.Id, p.NickName, p.LastName, rownums.DaysSince
	  FROM (
		SELECT row_number() OVER(PARTITION BY ConnectorPersonAliasId ORDER BY DaysSince ASC) AS rownum, DaysSince, ConnectorPersonAliasId
		  FROM
			(
				SELECT DATEDIFF(day,cr.ModifiedDateTime, GETDATE()) AS DaysSince, cr.ConnectorPersonAliasId
				  FROM ConnectionRequest cr
				 WHERE cr.ConnectionOpportunityId IN (49, 50, 51) -- CHANGE THESE IDS
			) DAYSSINCEREQUESTS
		) rownums
		JOIN PersonAlias pa ON (rownums.ConnectorPersonAliasId = pa.Id)
		JOIN Person p ON (pa.PersonId = p.Id)
	 WHERE rownums.rownum = 1
), oldestrequests AS (
	SELECT p.Id, p.NickName, p.LastName, rownums.ModifiedDateTime
	  FROM (
		SELECT row_number() OVER(PARTITION BY ConnectorPersonAliasId ORDER BY ModifiedDateTime ASC) AS rownum, ConnectorPersonAliasId, ModifiedDateTime
		  FROM
			(
				SELECT cr.ModifiedDateTime, cr.ConnectorPersonAliasId
				  FROM ConnectionRequest cr
				 WHERE cr.ConnectionOpportunityId IN (49, 50, 51) -- CHANGE THESE IDS
				   AND cr.ConnectionState = 0 -- 0 = Active
			) OLDESTREQUESTS
		) rownums
		JOIN PersonAlias pa ON (rownums.ConnectorPersonAliasId = pa.Id)
		JOIN Person p ON (pa.PersonId = p.Id)
	 WHERE rownums.rownum = 1
)
SELECT tr.Id, tr.NickName, tr.LastName, tr.TotalRequests - rc.RequestsCompleted AS RequestsAssigned, rc.RequestsCompleted, tr.TotalRequests AS TotalRequestsAssigned, ds.DaysSince AS DaysSinceTheyLastUpdated, ore.ModifiedDateTime AS OldestRequestUpdate
  FROM totreq tr
	LEFT OUTER JOIN reqcom rc ON (tr.Id = rc.Id)
	LEFT OUTER JOIN dayssince ds ON (tr.Id = ds.Id)
	LEFT OUTER JOIN oldestrequests ore ON (tr.Id = ore.Id)