Get Last date column Name

[AtulJadhav]

HI Expert

I have SQL Table and the column are as follow.
CustomerCode, Step1, Stap2, Step3, Step4,Final Status

Now in customerCode column having 6 digit code and Step1, Step2, Step3, and Step4 column having Date formate now I want final status in Final Status column which as below

if Step4 having Date then give the result “case closed”, if Step4 doesn’t have the date then search in Step3 and if Date is there then give me status as “Step4 Pending”, if Step3 doesn’t have the date then search in Step2 Column and if the date is there then give me status as “Step3 Pending” if Step2 doesn’t have the date then go to Step1 column and give me result as “Step2 Pending” and non of the column having date then give me default status as “Cast Not Started”.
the program need to search backward and if any date found the status show next step pending. also if I remove the date from any column the status will change as per last date available in table.

also if we can do this in SQL table in calculated column that also need

[skawid]

You can do this using generated columns, which are available since MySQL 5.7:

ALTER TABLE customerStatus 
ADD COLUMN FinalStatus VARCHAR(64) AS (
    CASE
        WHEN Step1 IS NULL THEN "Step1 pending"
        WHEN Step2 IS NULL THEN "Step2 pending"
        WHEN Step3 IS NULL THEN "Step3 pending"
        WHEN Step4 IS NULL THEN "Step4 pending"
        ELSE "Case closed" 
    END
) STORED;

Will add the column you want. The STORED at the end means the field will actually be stored on disk; this takes more space, but means the column can be indexed normally.

It’s worth noting that while you can do this, it might not be the safest approach. It relies on the Step fields being populated in the correct order for example, which is logic you will have to do in code. I’ve also personally never seen this MySQL syntax before; it might take your team mates by surprise as well.

[AtulJadhav]

Hi, i used below formual in computed column and got result what i want.
Thanks for sharing codes.

(case when [Step4] IS NOT NULL then ‘All Done’ when [Step3] IS NOT NULL then ‘Step4 pending’ when [Step2] IS NOT NULL then ‘Step3 pending’ when [Step1] IS NOT NULL then ‘Step2 pending’ else ‘Case Not Started’ end)