Find records from the last 24 hours

  • NineIron

    SSChampion

    Points: 12523

    How do I get the records for the last 24 hours for each VisitID, based on the ActivityDateTime? So, beginning at the most recent ActivityDateTime for each VisitID, go back 24 hours and pull those records.

     

     

    create table #T1
    (
    VisitID varchar(30),
    IdentifierID varchar(30),
    ActivityDateTime datetime
    )

    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20907277','2019-10-24 10:18:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20916935.0000001','2019-10-24 15:30:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20921128','2019-10-25 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20929776','2019-10-25 12:34:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20934262','2019-10-25 15:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20939337.000002','2019-10-26 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20944012','2019-10-26 10:30:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20950273','2019-10-26 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20952136.0000016','2019-10-27 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20957202','2019-10-27 10:48:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20960215','2019-10-27 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20964781.0000034','2019-10-28 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20974278.0000001','2019-10-28 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20981359','2019-10-28 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20984351.0000002','2019-10-29 01:49:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20990720','2019-10-29 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21088202.0000002','2019-11-04 04:39:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21100250','2019-11-04 17:12:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21106086','2019-11-05 02:57:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21130466','2019-11-06 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21141009','2019-11-06 22:24:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21142345','2019-11-07 01:04:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','20988600.0000008','2019-10-29 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','20998053.0000001','2019-10-29 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21003567.0000005','2019-10-29 23:46:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21009280','2019-10-30 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21020944.0000001','2019-10-30 19:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21023284','2019-10-31 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21026664.0000012','2019-10-31 07:39:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21036959.0000003','2019-10-31 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21042642.0000011','2019-11-01 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21050650.0000007','2019-11-01 12:11:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21054048.000002','2019-11-01 15:49:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21060014','2019-11-02 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21064111.0000001','2019-11-02 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21073317','2019-11-03 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191028230554994','21078138.0000001','2019-11-03 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029104148724','21141868.0000001','2019-11-07 00:14:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21093360.0000001','2019-11-04 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21099473','2019-11-04 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21106689','2019-11-05 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21110737','2019-11-05 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21119499','2019-11-05 16:51:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21124364.0000004','2019-11-05 23:52:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21129720.0000003','2019-11-06 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21138752','2019-11-06 18:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21141775','2019-11-07 00:05:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21003774.0000002','2019-10-30 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21009460','2019-10-30 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21018775','2019-10-30 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21023349.0000001','2019-10-31 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21027477','2019-10-31 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21037635.0000001','2019-10-31 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21043039.0000003','2019-11-01 02:17:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21051328.0000001','2019-11-01 08:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21057025','2019-11-01 16:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21061522.0000001','2019-11-02 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21063790','2019-11-02 07:30:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21067653','2019-11-02 14:24:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21073532.0000001','2019-11-03 00:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21076831.0000001','2019-11-03 08:29:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21080805','2019-11-03 14:41:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21084886','2019-11-03 20:00:00.000')
    insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191029132826907','21087679.0000001','2019-11-04 00:00:00.000')
  • John Mitchell-245523

    SSC Guru

    Points: 148745

    WITH Last24Hrs AS (
    SELECT
    VisitID
    , IdentifierID
    , ActivityDateTime
    , DATEADD(HOUR,-24,MAX(ActivityDateTime) OVER (PARTITION BY VisitID)) AS _24HrsBefore
    FROM #T1
    )
    SELECT
    VisitID
    , IdentifierID
    , ActivityDateTime
    FROM Last24Hrs t
    WHERE t.ActivityDateTime >= t._24HrsBefore;

    John

  • drew.allen

    SSC Guru

    Points: 76721

    Depending on your indexes, this may perform better.

    WITH Last24Hrs AS (
    SELECT
    VisitID
    , IdentifierID
    , ActivityDateTime
    , DATEADD(HOUR,-24,LAST_VALUE(ActivityDateTime) OVER (PARTITION BY VisitID ORDER BY ActivityDateTime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) AS _24HrsBefore
    FROM #T1
    )
    SELECT
    VisitID
    , IdentifierID
    , ActivityDateTime
    FROM Last24Hrs t
    WHERE t.ActivityDateTime >= t._24HrsBefore;

    The only difference is using LAST_VALUE() -- which also requires adding a frame -- instead of MAX().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jonathan AC Roberts

    SSCoach

    Points: 17273

    Another method:

    ;WITH CTE AS 
    (
    SELECT t.VisitID,
    MAX(t.ActivityDateTime) MaxActivityDateTime
    FROM #T1 t
    GROUP BY t.VisitID
    )
    SELECT t.*
    FROM CTE
    INNER JOIN #T1 t
    ON t.VisitID = CTE.VisitID
    AND t.ActivityDateTime >= DATEADD(hh,-24,CTE.MaxActivityDateTime)

    Though I think Drew's answer will win on performance.

  • drew.allen

    SSC Guru

    Points: 76721

    As with all things SQL, it depends.  I did a quick test using an index on VisitID and ActivityDateTime and Jonathan's original query won hands down based on the query plan (I did not check IO), but when I changed the index to VisitID and ActivityDateTime DESC, then my query won.  There are a lot of factors that go into performance, so I recommend testing everything.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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