T-SQL Get last daily timestamp by ID

We have a table of user activities. Each activity is logged by userID, timestamp, and activityID. There can be multiple rows for each activityID by user each day.

What we are trying to do is get the last time a specific activity was performed by a user for the last X days, like this example:

userID   timestamp  activityID
3241    10/14/2017 7:17 2
3241    10/15/2017 8:17 2
3241    10/16/2017 8:17 2
4355    10/15/2017 8:17 2
4355    10/16/2017 8:17 2
4355    10/17/2017 8:17 2
1234    10/15/2017 8:17 2
2236    10/15/2017 8:17 2
2236    10/16/2017 8:17 2
2002    10/17/2017 8:17 2

I can pull the very last time the activity was performed by user ID, but not for each day within the last X days:

select t1.[userID], 
       t2.[mxdate]
from TableA t1
inner join
(
  select max([timestamp]) AS mxdate, 
         [userID]
    from TableA
    where activityType = 2
group by [userID]
) t2
  on t1.[userID] = t2.[userID]
  and t1.[timestamp] = t2.mxdate

I've been trying various ways to GROUP BY the dates portion of the timestamps, but so far have not been successful. I keep getting every single activity and timestamp for it.

Am I headed in the right direction at least? If so, what piece/clause am I missing?

Thanks in advance for any and all help!

Solution

By grouping by the DATE portion of your timestamp, you can get the maximum timestamp for each activity:

;WITH cte ( userid, [day], activityid, [timestamp] ) AS (
    SELECT t.userid, [day] = CONVERT(NVARCHAR, t.timestamp, 101), t.activityid, MAX(t.timestamp)
      FROM MyTable t
     GROUP BY t.userid, CONVERT(NVARCHAR, t.timestamp, 101), t.activityid
)
SELECT *
  FROM cte
 WHERE cte.activityid = 2

It was necessary to convert the timestamp (presumably a DATETIME construct) to a DATE so that as the table grows and covers mutliple months you don't group over the same day in two different months.

Using a derived table in a single query:

SELECT t.userid, t.activityid, t.timestamp
  FROM (SELECT i.userid, i.activityid, maxTS = MAX(i.timestamp)
          FROM MyTable i
         GROUP BY i.userid, i.activityid, CONVERT(DATE, i.timestamp)) maxRows
         INNER JOIN MyTable t
           ON maxRows.userid = t.userid
          AND maxRows.activityid = t.activityid
          AND maxRows.maxTS = t.timestamp
 WHERE t.activity = 2