sql - Select and group records 30 minutes apart from each other -

have table in sql server 2008 in page visits captured, similiar iis logs or google analytics, nothing fancy.

the table has columns such as:

[id], [username], [url], [created], [browser], [browserversion], [hostname], [ipaddress], [operatingsystem], [urlreferrer]

below picture illustrates query output sorted creation date(created column) in descending format representing/listing page hits. urls , usernames omitted privacy purposes.

what write query group rows in 1 time difference last record occurance less 30 minutes identical ipaddress , username or in other words select/return last record same ipaddress , username , eliminate other rows prior that.

desired outcome rows arrow next them(image below):

first, requirement can interpreted more 1 way let me stat think wanting... think saying session ends when given ip address has 30 minutes of inactivity. if ip address hits site every minute 2 hours , takes 30 minute break represents 1 session. assuming intended...

you can use lead , lag identify sessions. test data consisted of id column, ipaddress column, , created column. here code, explanation follows...

with t1  (     select          *          , datediff(minute, lag(created, 1, 0) on (partition ipaddress order created), [created]) sincelastaccess              iislog ), sessionstarts  (     select          *               t1              sincelastaccess >= 30 ), sessioninfo  (     select          ipaddress         , created sessionstart         , lead(created, 1, '2025-01-01') on (partition ipaddress order created) sessionend              sessionstarts )  select * sessioninfo 

the first cte (t1) selects data, adds column called sincelastaccess. new column uses lag function @ value in previous row , calculate how many minutes have passed. partition by constrains calculation each ip address.

the second cte (sessionstarts) selects rows t1 sincelastaccess value greater 30. tells beginning of every session.

finally, `sessioninfo' cte builds upon second. using lead function forward see next session begins. value taken when current row's session ends. end ip address, session start, , session end. have these, should easy join original table , group up.