0

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   

1 Answer 1

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;
2
  • 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.
    – Peter
    Aug 30, 2023 at 22:23
  • 1
    @Peter I have made an edit to address that point
    – Paul White
    Aug 31, 2023 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.