I have a transaction table that I want to pull data from. The transaction table records a timestamp for each record sent to the table in addition to other fields including the user it is associated to.

I want the output to group the transactions by day for each person, but I also want to calculate the duration between each timestamp for each specific user. I am able to get the duration between MAX and MIN timestamps for the entire day for each person, but I am unable to get the duration between each individual transaction for each day. I’ve tried CTE scripts using row numbers without success. Here is a working script that pulls duration for the entire day:

SELECT 
    MAX(TRANSACTION_DATE),
    MIN(TRANSACTION_DATE),
    [TRANSACTION_DESCRIPTION],
    [TRANSACTION_LOCATION],
    [TRANSACTION_ID],
    [LAST_NAME],
    [FIRST_NAME],
    DATEDIFF(HOUR,MIN(TRANSACTION_DATE),MAX(TRANSACTION_DATE)) AS [DURATION]
FROM [DB].[DBO].[TRANSACTION_TABLE]
GROUP BY
    CAST(TRANSACTION_DATE AS DATE),
    [TRANSACTION_DESCRIPTION],
    [TRANSACTION_LOCATION],
    [TRANSACTION_ID],
    [LAST_NAME],
    [FIRST_NAME]
ORDER BY
    [LAST_NAME]