T-SQL Get last daily timestamp by ID

Question

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!

What is your desired result based on the sample data in the question? — Tab Alleman, Dec 13 '17 at 19:16
Sorry, I might have been clear as mud! What I posted *is* the desired result. — luser3228561, Dec 13 '17 at 21:33

Forty3 · Accepted Answer · 2017-12-13T20:49:56.543

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

I like your answer better but you can simplify it more. Take out the CTE and put your activity filter in the main query, then only `GROUP BY CONVERT(DATE,timestamp)` instead of also selecting it. — Aaron Dietz, Dec 13 '17 at 19:54
Thanks! The derived table answer is perfect! There are more columns we definitely need to include, or have constraints on, and that's a lot easier for me to adapt/update. And more importantly, understand! — luser3228561, Dec 13 '17 at 21:35

T-SQL Get last daily timestamp by ID

1 Answers1