Deleting rows using count after reaching certain threshold MS Sql server 2012

Question

I'm trying to make a loop query for Sql agent which checks users data and if the criteria is met reward is sent, after that it would delete the records from the table restarting the loop. The issue is that I cannot get the loop working.

My table looks like this:

ID | login_id | price | date |
1 user1 -100 01/01/1999 

What I've tried:

DECLARE @retVal int
SELECT @retVal = COUNT(*)
select x.login_id, x.price
FROM Table as x inner join Table as b on x.login_id=b.login_id 
where x.price='-100'
order by x.login_id
IF (@retVal >= 3)
BEGIN
DELETE TOP (3) x 
FROM point_log_pitty as x 
inner join point_log_pitty as b on x.login_id=b.login_id 
where b.login_id=x.login_id and @retVal = 3 and x.price='-100';
End

First issue with this is that it counts all records for set conditions (multiple users) not per user. Example of what I've got(It doesn't limit it self to just 1 user):

ID | login_id | price | date |
1 user1 -100 01/01/1999 
2 user2 -100 01/01/1999 
3 user3 -100 01/01/1999 

I've tried this one too:

SELECT login_id, COUNT(login_id) AS username_count
FROM point_log_pitty
where price='-100'
GROUP BY login_id

Which counts properly the records, but I can't use the results from username_count to delete query.

Goal: Would look like something like this: Once you x user reaches 3'-100' price matches query would execute and select the records of that users history which match the criteria and delete those records restarting the loop.

This data being fetch and deleted.

ID | login_id | price | date |
1 user1 -100 01/01/1999 
2 user1 -100 01/01/1999 
3 user1 -100 01/01/1999 

Answer 1

You were close on your second attempt. You'd want to use the HAVING clause to only keep cases with 3 or more rows grouped up, and throw that into a temp table first, then rejoin to that temp table in the DELETE query like so:

IF OBJECT_ID(N'tempdb..#Rewards', 'U') IS NOT NULL
BEGIN
 DROP TABLE #Rewards;
END;
-- Step 1: Get the Rewards with 3 or more logins with Price = -100
SELECT login_id
INTO #Rewards
FROM point_log_pitty
WHERE price='-100'
GROUP BY login_id
HAVING COUNT(login_id) >= 3;
-- Step 2: Delete those Rewards
DELETE plp
FROM point_log_pitty AS plp
INNER JOIN #Rewards AS r
 ON plp.login_id = r.login_id;

• Peter: I'd assume that would work on more recent version but, as stated I'm using 2012 and it doesn't really like the first line of syntax not in its current form. "DROP TABLE IF EXISTS #Rewards;". Once that is settled I can accept your answer.
• Paul White: @Peter I have made an edit to address that point