November 7, 2019 at 2:42 pm
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')
November 7, 2019 at 2:54 pm
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
November 7, 2019 at 3:50 pm
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
November 7, 2019 at 4:02 pm
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.
November 7, 2019 at 4:11 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply