0

I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.

Table has 3 columns - Employee, Month, Salary. Need a MySQL Select statement that also creates a new calculated column Bonus (per employee per month) as below (Logic for the calculated column is in Logic column for reference) :

Sample

Improve this question
2
  • 1
    Add your sample data as formatted CREATE TABLE + INSERT INTO code (of course w/o Logic column).
    – Akina
    Commented Mar 15, 2023 at 4:57
  • See LAG() and other windowing functions.
    – Rick James
    Commented Mar 31, 2023 at 20:52

1 Answer 1

Reset to default
0 
SELECT *,
       CASE month WHEN 1 THEN salary
                  WHEN 2 THEN LAG(salary) OVER (PARTITION BY employee ORDER BY month) + 10
                  ELSE        LAG(salary) OVER (PARTITION BY employee ORDER BY month) + 15
                  END bonus
FROM datatable
Improve this answer
2
  • Thanks for the response. But the logic for Month>2 should be previous value of the Bonus column, and not Salary column.
    – Vineeth
    Commented Mar 14, 2023 at 20:56
  • @Vineeth Sorry, I miss this point. In this case you'd use recursive CTE.
    – Akina
    Commented Mar 15, 2023 at 4:27

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.