sql - TSQL - Run date comparison for "duplicates"/false positives on initial query? -
i'm pretty new sql , working on pulling data several large tables analysis. data triggered events assets on system. events have created_date (datetime) field care about.
i able put query below data need (yay):
select event.efkey ,event.e_id ,event.e_key ,l.l_name ,event.created_date ,asset.a_id ,asset.asset_name event left join asset on event.a_key = asset.a_key left join l on event.l_key = l.l_key event.e_key in (350, 352, 378) order asset.a_id, event.created_date however, while gives me data specific events want, still have problem. assets can trigger these events repeatedly, can result in large numbers of "false positives" i'm looking at.
what need go through result set of query above , remove events asset occur closer n minutes (say 30 minutes example). if asset_id same , event.created_date within 30 minutes of event asset in set want removed. example:
for following records
a_id 1124 created 2016-02-01 12:30:30 a_id 1124 created 2016-02-01 12:35:31 a_id 1124 created 2016-02-01 12:40:33 a_id 1124 created 2016-02-01 12:45:42 a_id 1124 created 2016-02-02 12:30:30 a_id 1124 created 2016-02-02 13:00:30 a_id 1115 created 2016-02-01-12:30:30 i'd want return only:
a_id 1124 created 2016-02-01 12:30:30 a_id 1124 created 2016-02-02 12:30:30 a_id 1124 created 2016-02-02 13:00:30 a_id 1115 created 2016-02-01-12:30:30 i tried referencing this , this can't make concepts there work me. know need select * (my existing query) can't seem without ending tons of "multi-part identifier can't bound" errors (and have no experience creating temp tables, attempts @ have failed far). not sure how use datediff date filtering function.
any appreciated! if dumb down novice (or link explanations) helpful!
this trickier problem appears. hard part capturing previous row , removing next bad rows not allowing bad rows influence whether or not next row good. here came with. i've tried explain going on comments in code.
--sample data since don't have table structure , original query won't work me declare @events table ( id int, timestamp datetime ) --note changed of sample data test different scenarios insert @events values( 1124, '2016-02-01 12:30:30') insert @events values( 1124, '2016-02-01 12:35:31') insert @events values( 1124, '2016-02-01 12:40:33') insert @events values( 1124, '2016-02-01 13:05:42') insert @events values( 1124, '2016-02-02 12:30:30') insert @events values( 1124, '2016-02-02 13:00:30') insert @events values( 1115, '2016-02-01 12:30:30') --using cte here split result set of query groups --by id (you want partition whatever criteria use --to determine rows talking same event) --the row_number function gets row number each row within --id partition --the on clause specifies how break result set groups --(partitions) , order put rows in within group --that numbering stays consistant ;with orderedevents ( select id, timestamp, row_number() on (partition id order timestamp) rn @events --you replace @events here query ) --using second recursive cte here determine rows "good" --and ones not. , previousgoodtimestamps ( --this "seeding" part of recursive cte pick --first rows of each group being desired result. since --are first in each group, know good. assign --their timestamp previous timestamp since know --this row good. select id, timestamp, rn, timestamp prev_good_timestamp, 1 is_good orderedevents rn = 1 union --this recursive part of cte. takes rows have --already added result set , joins "next" rows --(as defined our ordering in first cte). output --those rows , calculations determine if row --"good" or not. if "good" set it's timestamp --previous row timestamp rows come after 1 --can use determine if or not. if row "bad" --we forward along last known timestamp next row. -- --we determine if row checking if last row --timestamp plus 30 minutes less or equal current row's --timestamp. if row good. select e2.id , e2.timestamp , e2.rn , last_good_timestamp.timestamp , case when dateadd(mi, 30, last_good_timestamp.timestamp) <= e2.timestamp 1 else 0 end previousgoodtimestamps e1 inner join orderedevents e2 on e2.id = e1.id , e2.rn = e1.rn + 1 --i used cross apply here calculate last row timestamp --once. have used 2 identical subqueries above in select --and case statements, rather not duplicate code. cross apply ( select case when e1.is_good = 1 e1.timestamp --if last row good, use it's timestamp else e1.prev_good_timestamp --the last row bad, forward on had last timestamp end timestamp ) last_good_timestamp ) select * previousgoodtimestamps is_good = 1 --only take "good" rows links msdn of more complicated things here:
Comments
Post a Comment