question

danishamir-0686 avatar image
0 Votes"
danishamir-0686 asked danishamir-0686 commented

sql for finding value gaps

Hi,

I have the following table:

anomaly:

that looks like this:

exec_id account_id is_alerted

100 123 0
.
.
95 123 0
94 123 0
93 123 0
92 123 0
91 123 1
100 234 0
.
.
95 234 0
94 234 0
93 234 0
92 234 0
91 234 0



I need a query that returns the accounts that meet the following condition:


for each account - the gap between is_alerted = 0 and is alerted = 1 is exactly 12 exec_ids








sql-server-transact-sql
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What does gap mean? Is it the position between is_alerted = 0 and is alerted = 1?

0 Votes 0 ·

yes - the row position

0 Votes 0 ·

Which account from your sample data — “123” or “234” — meets the condition? Maybe show more relevant sample data.

0 Votes 0 ·

ithats what I want to find - which of the account meets the condition
there can be more accounts in the table

0 Votes 0 ·

In this data, there is first a slew of rows with account_id = 123 and then a batch with account_id = 234. But I assume that they can be interleaved? What happens in this case?

I would recommend that you analyse your problem a little more closely so that we can get the exact rules. I would also recommend that you post the CREATE TABLE statement for your table and the sample data as INSERT statements, to make it easier for us to work with it. We also need to see the expected results given the test data. Also, enlarge the set of test data, so that it covers all angles of the problem.

Right now, this is too much of a guessing game, and any attempts to serve you a query is likely to be frustrating exercise for everyone involved.

0 Votes 0 ·

why dont I make a project out of this - its a simple question
I will just solve it myself

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please check:

 CREATE TABLE #anomaly(exec_id INT,account_id INT,is_alerted INT)
 INSERT INTO #anomaly VALUES(100 ,123 ,0),(95 ,123 ,0),(94, 123, 0),(93 ,123, 0),
 (92 ,123 ,0),
 (91 ,123, 1),(100 ,234, 0),(95 ,234, 0),(94, 234 ,0),(93, 234 ,0),(92, 234 ,0)
 ,(91 ,234,0)
    
 SELECT * FROM  #anomaly 
    
 ;WITH cte
 as(SELECT *,ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY account_id) rr
 FROM #anomaly)
 ,cte2 as(SELECT account_id,MIN(rr) m1 
 FROM cte
 WHERE is_alerted=0
 GROUP BY account_id)
 ,cte3 as(
 SELECT account_id,MIN(rr) m2
 FROM cte
 WHERE is_alerted=1
 GROUP BY account_id)
    
 SELECT * FROM 
 cte2 c2
 JOIN cte3 c3
 ON c2.account_id=c3.account_id AND abs(m2-m1)=12

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.