Post reply

AVG days between max and second last date

  • May 23, 2018 at 12:57 am

    #353226

    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

  • May 23, 2018 at 2:29 am

    #1991566

    Have you run it to test it as looks ok to me apart from the fact that cust_id will be ambiguous in the first select and the group by. Oh, and so will visit_date, prefix them and you should be good to go, unless you have more than one identical row at position 2.

  • 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

  • May 23, 2018 at 6:45 am

    #1991597

    I don't have access to the tables as it was a test this morning and I wanted to work it out. You have put the max inside the inner query is the only difference I see.
    Thanks for the reply.

  • May 23, 2018 at 10:11 am

    #1991654

    I don't think "AVG" really has any meaning for the diff between only 2 dates?!

    SELECT cust_id, DATEDiff(day,Min(visit_date),Max(visit_date))
    FROM (
      SELECT cust_id, visit_date
      , ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
      FROM customer_table
    ) AS tmp
    WHERE RowNumber <= 2
    Group By cust_id

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 23, 2018 at 11:18 am

    #1991665

    ScottPletcher - Wednesday, May 23, 2018 10:11 AM

    I don't think "AVG" really has any meaning for the diff between only 2 dates?!

    SELECT cust_id, DATEDiff(day,Min(visit_date),Max(visit_date))
    FROM (
      SELECT cust_id, visit_date
      , ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
      FROM customer_table
    ) AS tmp
    WHERE RowNumber <= 2
    Group By cust_id

    Nice answer. I also thought avg doesn't mean anything here. Since OP want avg and didn't post sample data to test. I included avg in my query

    Saravanan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply