I have a table with multiple access entries - within a 10 seconds of each other - need to group these so only the first customer ID is shown in a report with the access time - how do you group / where if you have multiple adjacent entries, the custome rcould access a number of times within the same day/hour.
asked Nov 02, 2011 at 09:00 AM in Default
A different approach....
Blatantly stealing @Fatherjack's test setup script and modifying it slightly (the original script didn't give enough different data)
This uses the EXCEPT operator to get the set of AccessTimes that are not within the second set of AccessTimes defined as ones that are within an hour of another one.
Runs subsecond against base data of 10k rows.
answered Nov 10, 2011 at 01:49 AM
Kev Riley ♦♦
Just been playing around and came up with this.
It uses a recursive CTE to build all the possible combinations of accesstimes that are within an hour of each other. Then by counting up the combinations, based on the 'first' access time and discarding any times that appear more than once (using
I used a row_number() windowing function over the original data, but this could be removed if access times where guaranteed to be unique.
answered Nov 03, 2011 at 05:22 AM
Kev Riley ♦♦
You could always remove the insignificant portion of the datetime field you are referencing.
Example (strip time portion out of column, to return the day only)
If you are on SQL Server 2008 and above, you can use the
create table #TimeBookings (CustId int, AccessTime datetime)
INSERT INTO #TimeBookings SELECT 1, '20111102 19:37:10' UNION ALL SELECT 2, '20111102 19:37:27'
with cte as (select ,row_number() over(partition by CustId, CustId order by AccessTime) as rowcnt from #TimeBookings) select from cte where rowcnt=1
---this will give you just the first row inserted for your custID without the reftime... let me know if i understand it wrong... :(
answered Nov 03, 2011 at 12:17 AM