I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits.

With Visits AS
SELECT [uid],[visit_date]
            ,(SELECT MIN(visit_date) 
             FROM Visit E2 
             WHERE E2.visit_date > E1.visit_date AND 
                   E2.uid= E1.uid ) AS next_visit_date
FROM Visit E1
SELECT uid, DATEDIFF(day, visit_date, next_visit_date)
FROM Visits

I know that a CTE or a temp table is not a memory efficient way, so looking to increase efficiency. Thank you

  • 5
    There is nothing in a cte (disregarding recursive CTE) that is more or less efficient that than straight SQL. When query is executed, SQL Server will look at all parts of the query and make best plan, it does not "materialize" the CTE separately.
    – Bob Klimes
    Mar 13, 2019 at 16:18

1 Answer 1


I think efficiency is more reliant on the fact that you are doing 2 scans against the table. You could try using a window function instead:

SELECT uid, visit_date
     , LEAD(visit_date) OVER (PARTITION BY uid 
                              ORDER BY visit_date) as next_visit_date
FROM Visit E1

Now you can calculate the diff from there

SELECT uid, DATEDIFF(day, visit_date, 
                          LEAD(visit_date) OVER (
                              PARTITION BY uid 
                              ORDER BY visit_date
FROM Visit E1

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .