Post reply

Calcluate avg # of days between dates?

  • November 13, 2007 at 12:31 pm

    #183850

    I'm hoping someone can help me with this...

    Given a dataset like this:

    Customer Order Date

    ====== ======

    Jones 1/5/2007

    Jones 2/8/2007

    Smith 8/31/2006

    Smith 5/2/2007

    Jones 10/21/2007

    Smith 9/4/2007

    How would I calculate the average number of days between orders for each customer? I'm using SQL Server 2005.

    Thanks!

  • November 13, 2007 at 12:46 pm

    #750810

    DECLARE@SampleTABLE (Customer VARCHAR(5), OrderDate DATE)

    INSERT@Sample

    SELECT'Jones', '1/5/2007' UNION ALL

    SELECT'Jones', '2/8/2007' UNION ALL

    SELECT'Smith', '8/31/2006' UNION ALL

    SELECT'Smith', '5/2/2007' UNION ALL

    SELECT'Jones', '10/21/2007' UNION ALL

    SELECT'Smith', '9/4/2007'

    SELECTCustomer,

    DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) / (COUNT(*) - 1) AS AvgDays

    FROM@Sample

    GROUP BYCustomer

    N 56°04'39.16"
    E 12°55'05.25"

  • November 13, 2007 at 1:30 pm

    #750827

    Thank you, Peter!

    Works like a charm, although I did have to modify it slightly to compensate for the fact there are some customers in my actual list who have only placed one order, which caused divide-by-zero errors to occur.

    case

    when count(*) < 2 then 'N/A'

    else convert(varchar(20), DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) / (COUNT(*) - 1))

    end AS AvgDays

  • November 13, 2007 at 2:16 pm

    #750840

    Great!

    I think you should keep integer as datatype, and format the result at the frontend

    case

    when count(*) < 2 then CAST(NULL AS INT)

    else DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) / (COUNT(*) - 1)

    end AS AvgDays

    N 56°04'39.16"
    E 12°55'05.25"

  • November 13, 2007 at 2:24 pm

    #750841

    Right!

    Thanks again!

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

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