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