Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL AVG days between max and second last date RE: AVG days between max and second last date

  • May 23, 2018 at 2:57 am

    #1991572

    ringovski - Wednesday, May 23, 2018 12:57 AM

    Hi I am trying to work out a script to return the average number of days between the most recent date and the second most recent date for each client id. I have come up with the below I think it's pretty close but not sure about the where row number.


    SELECT cust_id, AVG(DATEDiff(days,Max(visit_date),tmp.visit_date))
    FROM
    Customer_table c
    JOIN
    ( SELECT cust_id,visit_date
       , ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
      FROM customer_table
    ) AS tmp
    ON tmp.cust_id = c.cust_id
    WHERE RowNumber = 2
    Group By cust_id

    Is this what your looking for?


    create table customers
    (
    custid int,
    visit_date date
    );
    insert into customers values(1,'01-13-2017');
    insert into customers values (1,'02-25-2017');
    select
    custid,
      avg(datediff(day,visit_date,mx))
    from
    (select
    custid,
    visit_date,
    max(visit_date) over (partition by custid order by visit_date desc) as mx,
    row_number ()over(partition by custid order by visit_date desc) as scmx
    from customers)avg_visit
    where scmx=2
    group by custid

    Saravanan