asked on

Get Duration of last Status Update

I have a table where I have a Status field. My front end process will update this status up to 10 times with different statuses. I added an UpdateStatusDate field(DateTime, Default = GetDate()) and a Duration field(int, Default = 0). I essentially want to know how long of a duration that a Status has been in a certain status. Everytime a Status is updated the duration would start over. I thought I could just do a DateDiff(?) on the old UpdateStatusDate and GetDate() to get the Duration? But if a record has been in status = 3 then I wouldnt have the appropriate duration when Im pulling the record. I would have to run a daily job to update the Duration field to increment the days its been in that status, which I definitely dont wanna do.
So as Im writing this its becoming clearer that I should keep the UpdateDate(with GetDate() when status changes) and get rid of the Duration field and do a DateDiff from the UpdateStatusDate when I Select the data using the UpdateStatusDate and GetDate() to come up with the Duration of how long its been in this current status?

Does this make sense? If anyone has a better way of me doing this any suggestions would be great.
Thanks

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Hi,
Pls try..

create a new table called status_changed. Structure is to be used below-

CREATE TABLE status_changed
(
	 Id BIGINT IDENTITY(1,1) PRIMARY KEY    
	,Status VARCHAR(10) NOT NULL
	,StartDate DATETIME NOT NULL
	,EndDate DATETIME NULL
)
GO

Open in new window

ALTER your original table and add a new column called Status_changed_Id <Primary Key of above table>

Every time any one changes the status, Put GETDATE() in EndDate column and insert a new row with new status with enddate NULL.
You also need to update the Status_changed_Id in the original table with latest id of status_changed

<For making above changes you can use either trigger or simple insert/update statements>

So with this you have Start and End Date for each status.

Hope it helps!

EugeneZ

It is just main idea-- use if you like -- you may need to add extra filters to identify the "changing" record
"

--
when the time of the status change came:

in code identify and update the last record

e.g
;WITH CTE AS
(
select top 1 * from yourtable
--EZ <there can be added "Where youRecordSpecialIdentifire='something'">
order by UpdateStatusDate desc
)
UPDATE CTE SET duration=datediff(mi, [UpdateStatusDate] ,getdate()) -- mi <=> minutes

and the next step-- do your new record insert

more about datediff
https://msdn.microsoft.com/en-us/library/ms189794.aspx

jknj72

ASKER

I was hoping to not have to create another table but it seems like the best way to go so Thanks for the help