Assign flag based on cumulative running total

Question

I have a table as shown above which is from the following query:

SELECT f_id, f_datetime, (f_player1_score+f_player2_score) AS legs,
            SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs,
            IF(DATE(f_datetime) = DATE(NOW()), 1, 0) AS f_status
            FROM results

I want the f_status query column I've created to have the following rules:

1 - If `DATE(f_datetime) = DATE(NOW())` THEN assign `f_status=1`, which is what I've done.
2 - take the maximum total legs where `f_status=1` (i.e. 4 and add 50), then assign `f_status=2` up to total_legs=54,
3) Anything after 2) assign as 3

Desired output below:

I know how to do this but it requires many table aliases and is really messy. Is there a simple way to go about it?

Thanks

Gordon Linoff · Accepted Answer · 2021-06-16T12:36:19.827

If I follow correctly, you can just add a CASE expression with the boolean logic you want:

SELECT f_id, f_datetime,
      (f_player1_score+f_player2_score) AS legs,
      SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs,
       (CASE WHEN DATE(f_datetime) = CURDATE())
             THEN 1
             WHEN SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) <= 54
             THEN 2
             ELSE 3
        END) AS f_status
FROM results ;

EDIT:

If the 54 is just 50 plus the current day, you can use:

SELECT f_id, f_datetime,
      (f_player1_score+f_player2_score) AS legs,
      SUM(f_player1_score + f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs,
       (CASE WHEN DATE(f_datetime) = CURDATE()
             THEN 1
             WHEN SUM(f_player1_score + f_player2_score) OVER (ORDER BY f_datetime) <=
                  50 + SUM(CASE WHEN DATE(f_datetime) = CURDATE() THEN f_player1_score + f_player2_score ELSE 0 END)
             THEN 2
             ELSE 3
        END) AS f_status
FROM results ;

Yes but the query needs to know dynamically what the '54' is? In other queries the maximum total_legs when f_status=1 could be 100, therefore would need to be 100+50 — arsenal88, Jun 16 '21 at 11:22
@arsenal88 . . . This answers the question that you asked here. If you have a different question, ask a *new* question with a clear explanation, sample data, and desired results. Actually, if I edited the answer with my understanding of your logic. — Gordon Linoff, Jun 16 '21 at 12:32

score 0 · Answer 2 · answered Jun 16 '21 at 11:33

Managed to do it thanks to a suggestion to use CASE expression...

SELECT X.*,
    (CASE WHEN DATE(f_datetime) = CURDATE() then 1 
                WHEN total_legs<= MAX(CASE WHEN DATE(f_datetime) = CURDATE() THEN total_legs + 50 END) OVER(ORDER BY f_datetime DESC) then 2 
                ELSE 3 END) AS f_status
        
 FROM
(
    SELECT f_id, f_datetime, (f_player1_score+f_player2_score) AS legs,
                SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs
                
                FROM results 
) X

Assign flag based on cumulative running total

2 Answers2