2

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 at 16:18
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.