How to create a stored procedure with given data in SQL Server

Question

I want to create a stored procedure in SQL Server with the given data below, but I don't know how to achieve this. My requirement is to change status column to overdue when rate column is not equal one hundred and end date is expired.

if (Rate == 100)
    Status = "Completed"
else if (Rate != 100 && EndDate < Today) //when end date expired and rate is not equal 100
    Status = "Overdue"
else
    Status = "Not started" //rate equal 0 and end date is intact

Expected output: Tasking table:

Task Id	Task Name	Start Date	End Date	Rate(%)	Status
1	Digging	2/16/2024	2/17/2024	25	Overdue
2	Cleaning	2/19/2024	2/20/2024	30	In progress
3	Planting	2/19/2024	2/19/2024	100	Completed
4	Planting	2/22/2024	2/22/2024	0	Not started

Answer 1

Have you checked the Case expression?

Try something like

SELECT TaskId, TaskName, StartDate, EndDate, Rate,
CASE
   WHEN Rate = 100 THEN 'Completed'
   WHEN Rate < 100 AND EndDate < GETUTCDATE() THEN 'Overdue'
   WHEN Rate < 100 AND EndDate >= GETUTCDATE() THEN 'In progress'
   ELSE 'Not started'
END as status

FROM Data

Answer 2

CREATE PROCEDURE UpdateStatus
    @id int,
    @rate int,
    @endDate date,
    @status varchar(20) 
AS
BEGIN
        if(@rate = 100) 
        set @status = 'Completed'
        if(@rate != 100 and @endDate < GETDATE()) 
        set @status = 'Overdue'
        else
        set @status = 'Not started'
        UPDATE tablename set columnname= @status where id = @id;
END