Nee help to write a formula that will show previous status prior to the current status

1

Can someone please help me to work on a formula that will show the previous variable for a specific column?

My goal is to see the status of the candidate prior to being rejected or candidate declined. At first I was thinking that it should be like Current Status minus 1 but I read in the other discussions that there is no concept of previous, current and next. So what I did is to create a sequence to each statuses. But my problem is I am not sure how to write the formula showing the previous status.

I am a newbie in SQL and so I would really need your help in order for me to do this.

I hope someone will help.

Thank you!

2

Do you want to see an Audit / History record (of how the data was before it was changed)?

Or do you just want to see the previous-value-in-sequence-order?

if you want the highest-value-less-than-current-value (i.e. the "previous" value) you could do something like:

SELECT TOP 1 StatusDescription AS [PreviousStatusDescription]
FROM MyStatusTable
WHERE SequenceNumber < @MyCurrentSequenceNumber
ORDER BY SequenceNumber DESC
1 Like
3

CREATE TABLE OrderStatus
(
ID int IDENTITY PRIMARY KEY
, OrderID int REFERENCES Order(ID) NOT NULL
, StatusId int REFERECES Status(ID) NOT NULL
, StatusDate datetime NOT NULL DEFAULT GetDate()
);
INSERT INTO OrderStautus Select ***
GO
;WITH SecondToLastStatus(Id, OrderId, StatusId, StatusDate, RowNum)
AS
(
SELECT
ID
, OrderID
, StatusId
, StatusDate
, RowNumber() Over (PARTITION BY OrderId ORDER BY StatusDate Desc)
FROM OrderStatus
)
SELECT
Id
, OrderId
, StatusId
, StatusDate
FROM
SecondToLastStatus
WHERE
RowNum = 2;

1 Like
4

Hi Kristen,

Thank you for your response. Appreciate it.

I need to see the status before the person gets rejected. Not the whole history but only that one status. Say for example I have these statuses.

  1. New
  2. To be evaluated
  3. Review
  4. 1st interview
  5. 2nd interview
  6. final interview
  7. passed
  8. rejected
  9. candidate withdraw

and let us say that the person was able to move the ladder until 2nd interview, then gets rejected. I only need to see the 2nd interview and not all the process he had been through.

I read in other discussions that there are no concept of previous, current and next that is why I created the sequence. is it true?

Thank you!

5

Once the record is updated and the status changed to 8=Rejected then the previous value for that column is no longer available. You will have to store it somewhere.

I would create an additional column of either PreviousStatus or StatusBeforeRejection.

PreviousStatus would store the Status whenever it changes. Thus if the status then changes from Rejected to Candidate Withdrawn you won't be able to show the earlier status any more.

StatusBeforeRejection would only store the status when a new status of "Rejected" is applied. The "problem" with this is if the status is changed from Rejected to "something earlier" - for example, someone changed the status to Rejected by mistake (e.g. on the wrong record) and then back to the correct status. What now for the
StatusBeforeRejection column? Will it store the Status next time the Status is changed, again, to Rejected? (which will lose the value it had before) and in the meantime StatusBeforeRejection will be showing a value even though the main Status is showing a value earlier than Rejected ...

It might be that an error is made and to correct it the status changes from Candidate Withdrawn to back to Rejected - that would then store "Candidate Withdrawn" in the StatusBeforeRejection? which is clearly daft! (Of course you could program around that, but I am sure you get my point - a single StatusBeforeRejected column may not contain enough "granularity" to accurately provide information to the user)

An alternative is that you create a "History" table and store every previous change to Status (along with Date, Time [of the change] and UserID if you have one, and the ID of the record being changed). You can then report on "Most recent previous status" but also "All previous status changes" if you need that. (We do this for all columns, in almost every table we have; there are always questions about "How did that value get there?" which a History table answers, It enables the Goofy data to be fixed, based on how it used to be, and top-up-training to be provided for anyone who is consistently, but wrongly, updating records. But you do have to, also, create a housekeeping routine to purge history records after a suitable length of time.

Personally I would use a trigger to handle this so that the StatusBeforeRejection is always maintained, whether the Status is updated via an APP (i..e the "normal" route) or some adhoc SQL statement, or some "new process" in the future.

CREATE TRIGGER dbo.MyTriggerName
	ON dbo.MyTableName
	AFTER UPDATE
AS
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

	UPDATE	U
	SET	StatusBeforeRejection	= D.Status
	FROM	inserted AS I		-- New version of row(s)
		JOIN deleted AS D	-- Old version of row(s)
			 ON D.MyPKey = I.MyPKey
		JOIN dbo.MyTableName AS U
			 ON U.MyPKey = I.MyPKey
	WHERE	    I.Status = 8	-- Changed to Deleted
		AND D.Status < 8	-- Originally an earlier status
		AND (U.Status <> D.Status OR U.Status IS NULL)	-- Only update if not already set to correct value
6

Hi Kristen,

Thank you so much for the insights. I am using an Oracle Business Intelligence so the formatting is a little bit different I than the usual format you use.

I found an available column in the system which stores the history of candidates status that he had been process through.

Now I have 5 column namely: Job Code, Candidate Name, Current Status, History Status.

I filtered the current status to pull out 'Rejected' and 'Candidate Decline' status only.

I am able to see the current status alongside history Status of the candidate; however I don't know the formula to pull out only the status before the the rejected. I can see ALL the status from earlier to the latest (Rejected) which is not my goal.

I tried the first script you sent but changed some format. because OBI is using a different format in script writing.

Select Top 1 'rejected' as "Application History Status - All Events"."Application History Status"
From MyStatusTable
Where 8 < "Application Current Status Folder"."Application Current Status"
Order by 8 DESC end

But got syntax invalid. I also tried the exact format you used but still invalid syntax. :sob:

with regards to the latest script you gave, I am not sure which name should I replace.

What is dbo.MyTriggerName?
StatusBeforeRejection?
D.Status?
D.MyPKey?
I.MyPKey?

I am not sure which are temporary name(unique name in our system) and constant name.

Apologies for too many questions.

Thank you so much for helping me.

7

This is a Microsoft SQL Server forum, so you may not find folk here with knowledge of Oracle - might be worth asking on an Oracle forum.

AFAIK The Trigger code I posted won't be any use to you on Oracle, I have no idea how Oracle handles such things, sorry.

Does the Status History have something else that defines the changes in chronological order? A "ChangedOnDateTime" or ID number column perhaps? If so then retrieving just the "most recent" (before the change to Rejected) from that should do the trick.