Post reply

Replicate Excel's date difference for each record in SQL

  • July 21, 2016 at 9:37 am

    #328563

    Hello,

    I have got data table as below and I am struggling to get the date difference what Excel can do easily when done manually.

    The refID are unique for each duplicate email and several dates exist for them. (there could be more than 2 records for each email)

    I want to show in one record for each Email both min and max dates and difference in days.

    I don't know if I have to use pivot table or not.

    Any ideas will help.

    Thank you in advance,

    Vin

    create table trial_contacts

    ( refId int

    , refEmail varchar(100)

    , LastContactDate datetime

    )

    insert into trial_contacts

    select 100, 'abc@test.com', '13/06/2014' union all

    select 210, 'abc@test.com', '23/05/2016' union all

    select 250, 'xy@dummytest.com', '11/01/2014' union all

    select 270, 'xy@dummytest.com', '18/06/2015' union all

    select 295, 'xy@dummytest.com', '25/01/2016' union all

    select 350, 'rtc@test.com', '03/02/2012' union all

    select 390, 'rtc@test.com', '07/08/2015'

  • July 21, 2016 at 9:47 am

    #1891372

    Should be relatively straight forward.

    SELECT refEmail, MAX(LastContactDate), MIN(LastContactDate), DATEDIFF(day, MAX(LastContactDate), MIN(LastContactDate)) FROm trial_contacts GROUP BY refEmail

  • July 21, 2016 at 10:10 am

    #1891379

    ZZartin,

    Wow. so simple yet elegant.

    It didn't occur to me to use min and max within the dateDiff function directly.

    I was going completely off the tangent using two temp tables, etc.

    thank you so much.

    Vin

Viewing 3 posts - 1 through 2 (of 2 total)

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