February 25, 2013 at 8:16 am
Hi all,
I need to script a query to discard access logs that not exceeding a predetermined margin (eg. 2 seconds). That is, if each record was made by a user on a especific date / time, I don't want to get those who its date / time does not exceed the range compared with the date / time of the previous record. I think an example is clearer.
Example:
LogIndex, UserID, Date / Time
1. 01551, 20.02.2013 17:41:45.000
2. 01551, 20.02.2013 17:41:45.900 *
3. 01551, 20.02.2013 17:41:46.150 *
4. 01551, 20.02.2013 20:41:47.000
5. 01552, 02/20/2013 17:42:45.000
6. 01552, 20.02.2013 17:42:46.000 *
7. 01552, 02/20/2013 19:45:45.000
*: Records to discard because its date / time does not exceed the margin of 2 seconds over the previous record. In the first case two records should be discarted because both not exceed this margin.
Here's the code that creates the temporary table and adds the previous records to test:
CREATE TABLE # TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into # temp select 1, '01551 ', '20 / 02/2013 17:41:45.000'
insert into # temp select 2, '01551 ', '20 / 02/2013 17:41:45.900'
insert into # temp select 3, '01551 ', '20 / 02/2013 17:41:46.150'
insert into # temp select 4, '01551 ', '20 / 02/2013 20:41:47.000'
insert into # temp select 5, '01552 ', '20 / 02/2013 17:42:45.000'
insert into # temp select 6, '01552 ', '20 / 02/2013 17:42:46.000'
insert into # temp select 7, '01552 ', '20 / 02/2013 19:45:45.000'
select * from # temp
DROP TABLE # temp
Thanks in advance!
February 25, 2013 at 9:08 am
Hi and welcome to the SSC. Nice job posting ddl and sample data!!!
I made a couple changes so that it will work for everybody. This query works for your sample data.
if Object_id('tempdb..#TEMP') is not null
DROP TABLE #temp
set dateformat dmy
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551 ', '20/02/2013 17:41:45.000'
insert into #temp select 2, '01551 ', '20/02/2013 17:41:45.900'
insert into #temp select 3, '01551 ', '20/02/2013 17:41:46.150'
insert into #temp select 4, '01551 ', '20/02/2013 20:41:47.000'
insert into #temp select 5, '01552 ', '20/02/2013 17:42:45.000'
insert into #temp select 6, '01552 ', '20/02/2013 17:42:46.000'
insert into #temp select 7, '01552 ', '20/02/2013 19:45:45.000'
select * from #temp
;with cte as
(
select *, ROW_NUMBER() over(partition by UserID order by LogTime) as RowNum
from #temp
)
select cte.*
from cte
outer apply
(
select top 1 cte2.*
from cte cte2
where cte2.UserID = cte.UserID
and cte2.LogTime < cte.LogTime
order by cte2.LogTime
) x
where x.LogTime is null
or datediff(second, x.LogTime, cte.LogTime) > 2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2013 at 9:47 am
SET DATEFORMAT DMY
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551 ', '20/02/2013 17:41:45.000' --
insert into #temp select 2, '01551 ', '20/02/2013 17:41:45.900'
insert into #temp select 3, '01551 ', '20/02/2013 17:41:46.150'
insert into #temp select 4, '01551 ', '20/02/2013 20:41:47.000'
insert into #temp select 5, '01552 ', '20/02/2013 17:42:45.000'
insert into #temp select 6, '01552 ', '20/02/2013 17:42:46.000'
insert into #temp select 7, '01552 ', '20/02/2013 19:45:45.000'
------------------------------------------------
SELECT t.*, x.*
FROM #temp t
OUTER APPLY (
SELECT ti.*, calc.Diff
FROM #temp ti
CROSS APPLY (SELECT Diff = DATEDIFF(ss, ti.LogTime, t.LogTime)) calc
WHERE ti.UserID = t.UserID
AND ti.LogIndex < t.LogIndex
AND calc.Diff BETWEEN 0 AND 2
) x
ORDER BY t.LogIndex
----------------------------------------------------
SELECT t.*
FROM #temp t
WHERE NOT EXISTS (
SELECT 1
FROM #temp ti
WHERE ti.UserID = t.UserID
AND ti.LogIndex < t.LogIndex
AND DATEDIFF(ss, ti.LogTime, t.LogTime) BETWEEN 0 AND 2
)
ORDER BY t.LogIndex
-------------------------------------------------------------
SELECT t.*, x.*
FROM #temp t
OUTER APPLY (
SELECT ti.*
FROM #temp ti
WHERE ti.UserID = t.UserID
AND ti.LogIndex <> t.LogIndex
AND t.LogTime BETWEEN ti.LogTime AND DATEADD(ss,2,ti.LogTime)
) x
ORDER BY t.LogIndex
SELECT t.*
FROM #temp t
WHERE NOT EXISTS (
SELECT 1
FROM #temp ti
WHERE ti.UserID = t.UserID
AND ti.LogIndex <> t.LogIndex
AND t.LogTime BETWEEN ti.LogTime AND DATEADD(ss,2,ti.LogTime)
)
ORDER BY t.LogIndex
--------------------------------------------------------------
DROP TABLE #Temp
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 8:05 am
Hi all, thanks for your answers,
if i put this records into temp table these solutions doesn't works.
insert into TEMP select 1, '01551', '20/02/2013 17:41:45.000' --ok
insert into TEMP select 19, '01551', '20/02/2013 17:41:45.400'
insert into TEMP select 20, '01551', '20/02/2013 17:41:45.700'
insert into TEMP select 8, '01551', '20/02/2013 17:41:45.800'
insert into TEMP select 2, '01551', '20/02/2013 17:41:45.900'
insert into TEMP select 9, '01551', '20/02/2013 17:41:45.950'
insert into TEMP select 21, '01551', '20/02/2013 17:41:46.100'
insert into TEMP select 3, '01551', '20/02/2013 17:41:46.150'
insert into TEMP select 22, '01551', '20/02/2013 17:41:46.990'
insert into TEMP select 31, '01551', '20/02/2013 17:41:47.140'--ok
insert into TEMP select 32, '01551', '20/02/2013 17:41:47.940'
insert into TEMP select 33, '01551', '20/02/2013 17:41:48.120'
insert into TEMP select 34, '01551', '20/02/2013 17:41:48.720'
insert into TEMP select 10, '01551', '20/02/2013 20:41:45.600'--ok
insert into TEMP select 11, '01551', '20/02/2013 20:41:45.900'
insert into TEMP select 4, '01551', '20/02/2013 20:41:47.000'
insert into TEMP select 35, '01551', '20/02/2013 20:41:47.100'
insert into TEMP select 36, '01551', '20/02/2013 20:41:47.600'--ok
insert into TEMP select 37, '01551', '20/02/2013 20:41:47.900'
insert into TEMP select 24, '01551', '20/02/2013 20:41:47.700'
insert into TEMP select 25, '01551', '20/02/2013 20:41:48.990'
insert into TEMP select 26, '01551', '20/02/2013 20:41:49.100'
insert into TEMP select 5, '01552', '20/02/2013 17:42:45.000'--ok
insert into TEMP select 12, '01552', '20/02/2013 17:42:45.500'
insert into TEMP select 6, '01552', '20/02/2013 17:42:46.000'
insert into TEMP select 13, '01552', '20/02/2013 17:42:46.800'
insert into TEMP select 12, '01552', '20/02/2013 17:42:45.500'
insert into TEMP select 27, '01552', '20/02/2013 17:42:46.100'
insert into TEMP select 28, '01552', '20/02/2013 17:42:46.600'
insert into TEMP select 29, '01552', '20/02/2013 19:45:45.400'--ok
insert into TEMP select 30, '01552', '20/02/2013 19:45:45.900'
insert into TEMP select 15, '01552', '20/02/2013 19:45:46.200'
insert into TEMP select 16, '01553', '20/02/2013 19:45:45.100'--ok
insert into TEMP select 17, '01553', '20/02/2013 19:45:45.600'
insert into TEMP select 18, '01553', '20/02/2013 23:45:45.000'--ok
insert into TEMP select 19, '01553', '20/02/2013 23:45:45.200'
the records with ok are the records to show
thanks for advance,
March 12, 2013 at 8:27 am
The requirements shown by your last post are different from the requirements from your first post.
In your first post you state you want only the records that are equal or more than 2 seconds apart.
In your last post your criteria shows that you want records that are equal or mor than 2 seconds from the last valid record.
Which is it?
If you run the following code you'll find that there are only 6records that meet your initial criteria rather than the 8 you've marked.
if Object_id('tempdb..#TEMP') is not null
DROP TABLE #temp
set nocount on
set dateformat dmy
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551', '20/02/2013 17:41:45.000' --ok
insert into #temp select 19, '01551', '20/02/2013 17:41:45.400'
insert into #temp select 20, '01551', '20/02/2013 17:41:45.700'
insert into #temp select 8, '01551', '20/02/2013 17:41:45.800'
insert into #temp select 2, '01551', '20/02/2013 17:41:45.900'
insert into #temp select 9, '01551', '20/02/2013 17:41:45.950'
insert into #temp select 21, '01551', '20/02/2013 17:41:46.100'
insert into #temp select 3, '01551', '20/02/2013 17:41:46.150'
insert into #temp select 22, '01551', '20/02/2013 17:41:46.990'
insert into #temp select 31, '01551', '20/02/2013 17:41:47.140'--ok
insert into #temp select 32, '01551', '20/02/2013 17:41:47.940'
insert into #temp select 33, '01551', '20/02/2013 17:41:48.120'
insert into #temp select 34, '01551', '20/02/2013 17:41:48.720'
insert into #temp select 10, '01551', '20/02/2013 20:41:45.600'--ok
insert into #temp select 11, '01551', '20/02/2013 20:41:45.900'
insert into #temp select 4, '01551', '20/02/2013 20:41:47.000'
insert into #temp select 35, '01551', '20/02/2013 20:41:47.100'
insert into #temp select 36, '01551', '20/02/2013 20:41:47.600'--ok
insert into #temp select 37, '01551', '20/02/2013 20:41:47.900'
insert into #temp select 24, '01551', '20/02/2013 20:41:47.700'
insert into #temp select 25, '01551', '20/02/2013 20:41:48.990'
insert into #temp select 26, '01551', '20/02/2013 20:41:49.100'
insert into #temp select 5, '01552', '20/02/2013 17:42:45.000'--ok
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 6, '01552', '20/02/2013 17:42:46.000'
insert into #temp select 13, '01552', '20/02/2013 17:42:46.800'
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 27, '01552', '20/02/2013 17:42:46.100'
insert into #temp select 28, '01552', '20/02/2013 17:42:46.600'
insert into #temp select 29, '01552', '20/02/2013 19:45:45.400'--ok
insert into #temp select 30, '01552', '20/02/2013 19:45:45.900'
insert into #temp select 15, '01552', '20/02/2013 19:45:46.200'
insert into #temp select 16, '01553', '20/02/2013 19:45:45.100'--ok
insert into #temp select 17, '01553', '20/02/2013 19:45:45.600'
insert into #temp select 18, '01553', '20/02/2013 23:45:45.000'--ok
insert into #temp select 19, '01553', '20/02/2013 23:45:45.200'
; with setrows
as
(
select *
, ROW_NUMBER() over (order by logtime asc) as rownum
from #TEMP
)
select sr1.*, sr2.Logtime, DATEDIFF(ss,sr1.logtime,sr2.logtime) timeDifferential
from setrows sr1
inner join setrows sr2
on sr1.rownum = sr2.rownum - 1
set nocount off
drop table #TEMP
March 12, 2013 at 8:34 am
Erin Ramsay (3/12/2013)
The requirements shown by your last post are different from the requirements from your first post.In your first post you state you want only the records that are equal or more than 2 seconds apart.
In your last post your criteria shows that you want records that are equal or mor than 2 seconds from the last valid record.
Which is it?
If you run the following code you'll find that there are only 6records that meet your initial criteria rather than the 8 you've marked.
if Object_id('tempdb..#TEMP') is not nullDROP TABLE #temp
set nocount on
set dateformat dmy
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551', '20/02/2013 17:41:45.000' --ok
insert into #temp select 19, '01551', '20/02/2013 17:41:45.400'
insert into #temp select 20, '01551', '20/02/2013 17:41:45.700'
insert into #temp select 8, '01551', '20/02/2013 17:41:45.800'
insert into #temp select 2, '01551', '20/02/2013 17:41:45.900'
insert into #temp select 9, '01551', '20/02/2013 17:41:45.950'
insert into #temp select 21, '01551', '20/02/2013 17:41:46.100'
insert into #temp select 3, '01551', '20/02/2013 17:41:46.150'
insert into #temp select 22, '01551', '20/02/2013 17:41:46.990'
insert into #temp select 31, '01551', '20/02/2013 17:41:47.140'--ok
insert into #temp select 32, '01551', '20/02/2013 17:41:47.940'
insert into #temp select 33, '01551', '20/02/2013 17:41:48.120'
insert into #temp select 34, '01551', '20/02/2013 17:41:48.720'
insert into #temp select 10, '01551', '20/02/2013 20:41:45.600'--ok
insert into #temp select 11, '01551', '20/02/2013 20:41:45.900'
insert into #temp select 4, '01551', '20/02/2013 20:41:47.000'
insert into #temp select 35, '01551', '20/02/2013 20:41:47.100'
insert into #temp select 36, '01551', '20/02/2013 20:41:47.600'--ok
insert into #temp select 37, '01551', '20/02/2013 20:41:47.900'
insert into #temp select 24, '01551', '20/02/2013 20:41:47.700'
insert into #temp select 25, '01551', '20/02/2013 20:41:48.990'
insert into #temp select 26, '01551', '20/02/2013 20:41:49.100'
insert into #temp select 5, '01552', '20/02/2013 17:42:45.000'--ok
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 6, '01552', '20/02/2013 17:42:46.000'
insert into #temp select 13, '01552', '20/02/2013 17:42:46.800'
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 27, '01552', '20/02/2013 17:42:46.100'
insert into #temp select 28, '01552', '20/02/2013 17:42:46.600'
insert into #temp select 29, '01552', '20/02/2013 19:45:45.400'--ok
insert into #temp select 30, '01552', '20/02/2013 19:45:45.900'
insert into #temp select 15, '01552', '20/02/2013 19:45:46.200'
insert into #temp select 16, '01553', '20/02/2013 19:45:45.100'--ok
insert into #temp select 17, '01553', '20/02/2013 19:45:45.600'
insert into #temp select 18, '01553', '20/02/2013 23:45:45.000'--ok
insert into #temp select 19, '01553', '20/02/2013 23:45:45.200'
; with setrows
as
(
select *
, ROW_NUMBER() over (order by logtime asc) as rownum
from #TEMP
)
select sr1.*, sr2.Logtime, DATEDIFF(ss,sr1.logtime,sr2.logtime) timeDifferential
from setrows sr1
inner join setrows sr2
on sr1.rownum = sr2.rownum - 1
set nocount off
drop table #TEMP
Hi Erin
Yes, I would show the records thar are equal or more than 2 secons from the last valid record,
sorry for the error,
March 12, 2013 at 9:42 am
Le Sigh.. for the life of me I cannot figure out how to do this in a set-based statement so slap me now.
The following code will do what you want.
if Object_id('tempdb..#TEMP') is not null
DROP TABLE #temp
set nocount on
set dateformat dmy
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551', '20/02/2013 17:41:45.000' --ok
insert into #temp select 19, '01551', '20/02/2013 17:41:45.400'
insert into #temp select 20, '01551', '20/02/2013 17:41:45.700'
insert into #temp select 8, '01551', '20/02/2013 17:41:45.800'
insert into #temp select 2, '01551', '20/02/2013 17:41:45.900'
insert into #temp select 9, '01551', '20/02/2013 17:41:45.950'
insert into #temp select 21, '01551', '20/02/2013 17:41:46.100'
insert into #temp select 3, '01551', '20/02/2013 17:41:46.150'
insert into #temp select 22, '01551', '20/02/2013 17:41:46.990'
insert into #temp select 31, '01551', '20/02/2013 17:41:47.140'--ok
insert into #temp select 32, '01551', '20/02/2013 17:41:47.940'
insert into #temp select 33, '01551', '20/02/2013 17:41:48.120'
insert into #temp select 34, '01551', '20/02/2013 17:41:48.720'
insert into #temp select 10, '01551', '20/02/2013 20:41:45.600'--ok
insert into #temp select 11, '01551', '20/02/2013 20:41:45.900'
insert into #temp select 4, '01551', '20/02/2013 20:41:47.000'
insert into #temp select 35, '01551', '20/02/2013 20:41:47.100'
insert into #temp select 36, '01551', '20/02/2013 20:41:47.600'--ok
insert into #temp select 37, '01551', '20/02/2013 20:41:47.900'
insert into #temp select 24, '01551', '20/02/2013 20:41:47.700'
insert into #temp select 25, '01551', '20/02/2013 20:41:48.990'
insert into #temp select 26, '01551', '20/02/2013 20:41:49.100'
insert into #temp select 5, '01552', '20/02/2013 17:42:45.000'--ok
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 6, '01552', '20/02/2013 17:42:46.000'
insert into #temp select 13, '01552', '20/02/2013 17:42:46.800'
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 27, '01552', '20/02/2013 17:42:46.100'
insert into #temp select 28, '01552', '20/02/2013 17:42:46.600'
insert into #temp select 29, '01552', '20/02/2013 19:45:45.400'--ok
insert into #temp select 30, '01552', '20/02/2013 19:45:45.900'
insert into #temp select 15, '01552', '20/02/2013 19:45:46.200'
insert into #temp select 16, '01553', '20/02/2013 19:45:45.100'--ok
insert into #temp select 17, '01553', '20/02/2013 19:45:45.600'
insert into #temp select 18, '01553', '20/02/2013 23:45:45.000'--ok
insert into #temp select 19, '01553', '20/02/2013 23:45:45.200'
declare @validlogtime datetime
set @validlogtime = (select top 1 logtime from #temp order by Logtime asc)
select top 1 *
into #resultset
from #temp order by LogTime
declare @test-2 varchar(10)
declare dangit cursor for select Logtime from #temp order by Logtime asc
open dangit
fetch next from dangit into @validlogtime
while @@FETCH_STATUS = 0
begin
if DATEDIFF(ss,(select max(logtime) from #resultset),@validlogtime)>=2
begin
insert into #resultset select * from #temp where Logtime = @validlogtime
set @validlogtime = (select max(logtime) from #resultset)
end
fetch next from dangit into @validlogtime
end
select * from #resultset order by LogTime
set nocount off
close dangit
deallocate dangit
drop table #resultset
drop table #TEMP
March 12, 2013 at 9:57 am
sorry but my results are these...
1015512013-02-20 17:41:45.000
31015512013-02-20 17:41:47.140
5015522013-02-20 17:42:45.000
16015532013-02-20 19:45:45.100
10015512013-02-20 20:41:45.600
4015512013-02-20 20:41:47.000
26015512013-02-20 20:41:49.100
18015532013-02-20 23:45:45.000
for userId = 01552 only has one record when it should have two
and for userid = 01551 has 5 records whe it should have 4...
I know that it's so difficult and so piffling
March 12, 2013 at 10:00 am
it's due to the milliseconds.. let me take another look. I also overlooked that you were grouping by user so the criteria changes per user.
March 12, 2013 at 10:36 am
Don't use cursors!
You can do it with quirky update method:
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #TEMP select 1, '01551', '20/02/2013 17:41:45.000' --ok
insert into #TEMP select 19, '01551', '20/02/2013 17:41:45.400'
insert into #TEMP select 20, '01551', '20/02/2013 17:41:45.700'
insert into #TEMP select 8, '01551', '20/02/2013 17:41:45.800'
insert into #TEMP select 2, '01551', '20/02/2013 17:41:45.900'
insert into #TEMP select 9, '01551', '20/02/2013 17:41:45.950'
insert into #TEMP select 21, '01551', '20/02/2013 17:41:46.100'
insert into #TEMP select 3, '01551', '20/02/2013 17:41:46.150'
insert into #TEMP select 22, '01551', '20/02/2013 17:41:46.990'
insert into #TEMP select 31, '01551', '20/02/2013 17:41:47.140'--ok
insert into #TEMP select 32, '01551', '20/02/2013 17:41:47.940'
insert into #TEMP select 33, '01551', '20/02/2013 17:41:48.120'
insert into #TEMP select 34, '01551', '20/02/2013 17:41:48.720'
insert into #TEMP select 10, '01551', '20/02/2013 20:41:45.600'--ok
insert into #TEMP select 11, '01551', '20/02/2013 20:41:45.900'
insert into #TEMP select 4, '01551', '20/02/2013 20:41:47.000'
insert into #TEMP select 35, '01551', '20/02/2013 20:41:47.100'
insert into #TEMP select 36, '01551', '20/02/2013 20:41:47.600'--ok
insert into #TEMP select 37, '01551', '20/02/2013 20:41:47.900'
insert into #TEMP select 24, '01551', '20/02/2013 20:41:47.700'
insert into #TEMP select 25, '01551', '20/02/2013 20:41:48.990'
insert into #TEMP select 26, '01551', '20/02/2013 20:41:49.100'
insert into #TEMP select 5, '01552', '20/02/2013 17:42:45.000'--ok
insert into #TEMP select 12, '01552', '20/02/2013 17:42:45.500'
insert into #TEMP select 6, '01552', '20/02/2013 17:42:46.000'
insert into #TEMP select 13, '01552', '20/02/2013 17:42:46.800'
insert into #TEMP select 12, '01552', '20/02/2013 17:42:45.500'
insert into #TEMP select 27, '01552', '20/02/2013 17:42:46.100'
insert into #TEMP select 28, '01552', '20/02/2013 17:42:46.600'
insert into #TEMP select 29, '01552', '20/02/2013 19:45:45.400'--ok
insert into #TEMP select 30, '01552', '20/02/2013 19:45:45.900'
insert into #TEMP select 15, '01552', '20/02/2013 19:45:46.200'
insert into #TEMP select 16, '01553', '20/02/2013 19:45:45.100'--ok
insert into #TEMP select 17, '01553', '20/02/2013 19:45:45.600'
insert into #TEMP select 18, '01553', '20/02/2013 23:45:45.000'--ok
insert into #TEMP select 19, '01553', '20/02/2013 23:45:45.200'
SELECT IDENTITY(INT,1,1) RID,*, CAST(NULL AS BIT) AS Flag INTO #Work
FROM #TEMP ORDER BY UserId, LogTime
CREATE UNIQUE CLUSTERED INDEX ix_work ON #Work(RID, UserId, LogTime) -- very important!
DECLARE @Flag BIT, @userid INT, @LastFlaggedLogTime DATETIME
UPDATE w
SET @Flag = Flag = CASE WHEN @userid IS NULL
OR @userid != UserId
OR DATEDIFF(millisecond,@LastFlaggedLogTime, Logtime) >= 2000
THEN 1
ELSE 0
END
,@LastFlaggedLogTime = CASE WHEN @userid IS NULL
OR @userid != UserId
OR DATEDIFF(millisecond,@LastFlaggedLogTime, Logtime) >= 2000
THEN Logtime
ELSE @LastFlaggedLogTime
END
,@UserId = UserId
FROM #work AS w WITH (TABLOCKX) -- very important!
OPTION (MAXDOP 1) -- very important!
SELECT * FROM #Work WHERE Flag = 1
Actually, you should be able to do it with using triangular join, but it will be much slower for sure.
March 12, 2013 at 10:39 am
It's hideous and I hate it but it does what you want.
if Object_id('tempdb..#TEMP') is not null
DROP TABLE #temp
set nocount on
set dateformat dmy
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551', '20/02/2013 17:41:45.000' --ok
insert into #temp select 19, '01551', '20/02/2013 17:41:45.400'
insert into #temp select 20, '01551', '20/02/2013 17:41:45.700'
insert into #temp select 8, '01551', '20/02/2013 17:41:45.800'
insert into #temp select 2, '01551', '20/02/2013 17:41:45.900'
insert into #temp select 9, '01551', '20/02/2013 17:41:45.950'
insert into #temp select 21, '01551', '20/02/2013 17:41:46.100'
insert into #temp select 3, '01551', '20/02/2013 17:41:46.150'
insert into #temp select 22, '01551', '20/02/2013 17:41:46.990'
insert into #temp select 31, '01551', '20/02/2013 17:41:47.140'--ok
insert into #temp select 32, '01551', '20/02/2013 17:41:47.940'
insert into #temp select 33, '01551', '20/02/2013 17:41:48.120'
insert into #temp select 34, '01551', '20/02/2013 17:41:48.720'
insert into #temp select 10, '01551', '20/02/2013 20:41:45.600'--ok
insert into #temp select 11, '01551', '20/02/2013 20:41:45.900'
insert into #temp select 4, '01551', '20/02/2013 20:41:47.000'
insert into #temp select 35, '01551', '20/02/2013 20:41:47.100'
insert into #temp select 36, '01551', '20/02/2013 20:41:47.600'--ok
insert into #temp select 37, '01551', '20/02/2013 20:41:47.900'
insert into #temp select 24, '01551', '20/02/2013 20:41:47.700'
insert into #temp select 25, '01551', '20/02/2013 20:41:48.990'
insert into #temp select 26, '01551', '20/02/2013 20:41:49.100'
insert into #temp select 5, '01552', '20/02/2013 17:42:45.000'--ok
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 6, '01552', '20/02/2013 17:42:46.000'
insert into #temp select 13, '01552', '20/02/2013 17:42:46.800'
insert into #temp select 12, '01552', '20/02/2013 17:42:45.500'
insert into #temp select 27, '01552', '20/02/2013 17:42:46.100'
insert into #temp select 28, '01552', '20/02/2013 17:42:46.600'
insert into #temp select 29, '01552', '20/02/2013 19:45:45.400'--ok
insert into #temp select 30, '01552', '20/02/2013 19:45:45.900'
insert into #temp select 15, '01552', '20/02/2013 19:45:46.200'
insert into #temp select 16, '01553', '20/02/2013 19:45:45.100'--ok
insert into #temp select 17, '01553', '20/02/2013 19:45:45.600'
insert into #temp select 18, '01553', '20/02/2013 23:45:45.000'--ok
insert into #temp select 19, '01553', '20/02/2013 23:45:45.200'
declare @validlogtime datetime
set @validlogtime = (select top 1 logtime from #temp order by userid, Logtime asc)
declare @userid varchar(10)
declare @test-2 varchar(10)
declare dangitusers cursor for select distinct userid from #TEMP order by UserID
select top 1 * into #resultset from #TEMP where 1=0
open dangitusers
fetch next from dangitusers into @userid
while @@FETCH_STATUS = 0
begin
insert into #resultset select top 1 *
from #temp where userid = @userid order by userid, LogTime
declare dangit cursor for select Logtime from #temp where userid = @userid order by userid asc , Logtime asc
open dangit
while @@FETCH_STATUS = 0
begin
select @userid, @validlogtime
if DATEDIFF(ms,(select max(logtime) from #resultset where UserID = @userid),@validlogtime)>=2000
begin
insert into #resultset select * from #temp where Logtime = @validlogtime
set @validlogtime = (select max(logtime) from #resultset where UserID = @userid )
end
fetch next from dangit into @validlogtime
end
close dangit
deallocate dangit
fetch next from dangitusers into @userid
end
select * from #resultset order by userid, LogTime
set nocount off
close dangitusers
deallocate dangitusers
drop table #resultset
drop table #TEMP
March 12, 2013 at 10:44 am
You ran into the same issue I did, Eugene. User ms>2000 rather than seconds to meet his criteria but many thanks. I just could NOT figure out hot to set-base this.
Great work!
March 12, 2013 at 10:54 am
fixed in my post...
just replace
DATEDIFF(second,@LastFlaggedLogTime, Logtime) >= 2
with
DATEDIFF(millisecond,@LastFlaggedLogTime, Logtime) >= 2000
March 12, 2013 at 12:23 pm
Perhaps this:
CREATE TABLE #temp (LogIndex int, UserID nvarchar (10), LogTime datetime)
set dateformat dmy;
insert into #temp select 1, '01551 ', '20/02/2013 17:41:45.000'
insert into #temp select 2, '01551 ', '20/02/2013 17:41:45.900'
insert into #temp select 3, '01551 ', '20/02/2013 17:41:46.150'
insert into #temp select 4, '01551 ', '20/02/2013 17:41:47.000'
insert into #temp select 5, '01551 ', '20/02/2013 17:41:48.000'
insert into #temp select 6, '01551 ', '20/02/2013 17:41:49.000'
insert into #temp select 7, '01551 ', '20/02/2013 20:41:47.000'
insert into #temp select 8, '01552 ', '20/02/2013 17:42:46.000'
insert into #temp select 9, '01552 ', '20/02/2013 17:42:47.000'
insert into #temp select 10, '01552 ', '20/02/2013 17:42:48.000'
insert into #temp select 11, '01552 ', '20/02/2013 17:42:49.000'
insert into #temp select 12, '01552 ', '20/02/2013 19:45:45.000'
set dateformat mdy;
declare @IntervalSeconds int = 2;
with MinDate as (
select cast(cast(min(LogTime) as date) as datetime) BaseDateTime from #temp
), BaseData as (
select
LogIndex,
UserID,
LogTime,
BaseDateTime,
BaseSeconds = datediff(second, BaseDateTime, LogTime),
rn1 = row_number() over (partition by UserID, datediff(second, BaseDateTime, LogTime) order by LogTime),
rn2 = dense_rank() over (partition by UserID order by datediff(second, BaseDateTime, LogTime))
from
#temp
cross join MinDate
), SelectData as (
select
LogIndex,
UserID,
LogTime,
rn = (row_number() over (partition by UserID, BaseSeconds - rn2 order by LogTime) - 1) % @IntervalSeconds
from
BaseData
where
rn1 = 1
)
select
LogIndex,
UserID,
LogTime
from
SelectData
where
rn = 0;
go
DROP TABLE #temp
go
March 12, 2013 at 1:07 pm
Never mind on mine at the moment, needs more work. Have to look at it tonight.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply