CODEWITHSUNDEEP
sqlsql-serversql-server-2014-express
Ken John
Ken John

Reputation: 39

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

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

Upvotes: -3

Views: 71

Answers (2)

Kami
Kami

Reputation: 19407

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

Upvotes: 1

user23439169
user23439169

Reputation: 1

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

Upvotes: 0

Related Questions