July 9, 2018 at 6:58 am
I have a requirement to return the top record of each group. The groups are rolling time interval in seconds. Example data is below.
RowNum Id IndividualId MediaID Score LastHitDate JobID
1 1223510 1104952 1223509 6.33589029312134 2018-07-06 15:15:54.000 456360
2 1223508 1104952 1223507 6.33589029312134 2018-07-06 15:13:46.000 456359
3 1223506 1104952 1223505 6.33589029312134 2018-07-06 15:13:30.000 456358
4 1223502 1104952 1223501 6.33589029312134 2018-07-06 15:13:28.000 456356
5 1223504 1104952 1223503 6.33589029312134 2018-07-06 15:13:28.000 456357
6 1223500 1104952 1223499 6.33589029312134 2018-07-06 13:44:42.000 456355
7 1223498 1104952 1223497 6.33589029312134 2018-07-06 13:41:51.000 456353
8 1223494 1104952 1223493 6.33589029312134 2018-07-03 15:34:18.000 456351
If the time interval was 15 seconds then the highlighted rows would be selected.
The following query:select max(LastHitDate), max(rownum) from @temp where IndividualId = @indId group by DATEPART(YEAR, LastHitDate),
DATEPART(MONTH, LastHitDate),
DATEPART(DAY, LastHitDate),
DATEPART(HOUR, LastHitDate),
Datepart(minute, lasthitdate),
(DATEPART(SECOND, LastHitDate) / 15)
Results in:
LastHitDate RowNum
2018-07-03 15:34:18.000 8
2018-07-06 13:41:51.000 7
2018-07-06 13:44:42.000 6
2018-07-06 15:13:28.000 5
2018-07-06 15:13:30.000 3
2018-07-06 15:13:46.000 2
2018-07-06 15:15:54.000 1
The red row in the result is not wanted. The query above breaks the minute up into quarters but I need a rolling 15 seconds here.
Any help or ideas would be appreciated.
July 9, 2018 at 7:28 am
You can use the Lead function to get it
Drop table if exists #t
go
Create table #T (
RowNum int
,Id int
,IndividualId int
,MediaID int
,Score decimal(15,10)
,LastHitDate datetime2(7)
,JobID int)
insert into #T values
(1,1223510,1104952,1223509,6.33589029312134,'2018-07-06 15:15:54.000',456360),
(2,1223508,1104952,1223507,6.33589029312134,'2018-07-06 15:13:46.000',456359),
(3,1223506,1104952,1223505,6.33589029312134,'2018-07-06 15:13:30.000',456358),
(4,1223502,1104952,1223501,6.33589029312134,'2018-07-06 15:13:28.000',456356),
(5,1223504,1104952,1223503,6.33589029312134,'2018-07-06 15:13:28.000',456357),
(6,1223500,1104952,1223499,6.33589029312134,'2018-07-06 13:44:42.000',456355),
(7,1223498,1104952,1223497,6.33589029312134,'2018-07-06 13:41:51.000',456353),
(8,1223494,1104952,1223493,6.33589029312134,'2018-07-03 15:34:18.000',456351)
--,(9,1223504,1104953,1223503,6.33589029312134,'2018-07-06 15:13:28.000',456357),
--(10,1223500,1104953,1223499,6.33589029312134,'2018-07-06 13:44:42.000',456355),
--(11,1223498,1104954,1223497,6.33589029312134,'2018-07-06 13:41:51.000',456353),
--(12,1223494,1104955,1223493,6.33589029312134,'2018-07-03 15:34:18.000',456351)
select RowNum, ID, IndividualID, MediaID, Score, LastHitDate, JobID, Interval from (
select RowNum, ID, IndividualID, MediaID, Score, LastHitDate, JobID
,datediff(second, LastHitDate, lead(LastHitDate) over (partition by IndividualID order by LastHitDate)) Interval
from #T) X
where Interval > 15 or Interval is null
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2018 at 8:06 am
That worked perfectly.
Thanks for the quick response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply