Trigger to calculate to time in specific status

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger to calculate to time in specific status

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-03 : 04:18:18

Hi

Ive been asked to create a trigger (I know what they are but never done one before) for a single table.

Essentially what we have now is

ID StartTime EndTime Status

and over teh course off a period it will change status 10/15 times

What I'd liek to do is calculate the time its in a specific status

so lets say it has three statuses ongoing, queried and complete

when its ongoing i want to calculate the number of days ebtween starttime and endtime its been ongoing and queried with the total equaling the difference between start and complete.

failing that anotehr way i thought was for each status change it creates a new row in anothe table so if its

ongoing its a row then when changed to queried its an additonal row, and then when its changed again eithe back to ongoing or complete its another row, then i can use sql to calculate times by summing.

anyone got any other suggestions on how to approach this? and maybe some base code i can ammend to my own purpose?

Thanks Nick

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 07:00:32

What is the final output you are trying to calculate? If you can post some representative sample data and the corresponding expected output, people on the forum would be able to offer concrete suggestions.

From reading your post, it seems to me that you have only one table and for a given ID, the status may be updated several times to one of these three. But you also said it may change 10 to 15 times. I didn't quite follow that logic - does it go from ongoing to queried to complete and then back to ongoing etc.?

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-03 : 07:24:29

Its more theory at moment but heres an example I think works

ID Status Date Started Date Completed
1 COMPLETED 2012-01-01 2012-05-01
2 ONGOING 2012-03-02 null
3 COMPLETED 2012-04-05 2012-05-06

Now with ID 1 and 3, I want to know what status it was prior to ebcome completed and when the start date and end date was, additionally it could eb that it was complete but then someone has raised a query and has gone back to ongoing so I want to see when ID 2 was complete in a historical table of sorts

I guess assume each one had 2 chanegs to it, id expect to see 9 records in an additional table

which would look maybe like this

ID Status Start_date EndDate CURRENT_FLAG
1 ONGOING 2012-01-01 2012-04-20 0
1 QUERIED 2012-01-01 2012-04-22 0
1 COMPLETE 2012-01-01 2012-05-06 1
2 ONGOING 2012-03-02 2012-4-20 0
2 COMPLETE 2012-03-02 2012-05-02 0
2 ONGOING 2012-03-02 NULL 1
3 QUERY 2012-04-05 2012-04-20 0
3 ONGOING 2012-04-05 2012-05-01 0
3 CPOMPLETED 2012-04-05 2012-05-06 1

I guess what Im trying to achieve is to create a table which I can query to calculate the amount of time the status isn’t complete and potentially going forward the time spent at each status as like you’ve said the table can update to complete have an end date, and then go abck to ongoing then change end date to null

The start date always remanings the same as that’s the startdate the query came in so id be using datediff

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-03 : 07:25:22

that looked so much better in word!! sorry :/

I think what I'd like is sample code , and if ne one else has had experience of calculating time between statuses when theres only a single row

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 07:53:50

You can use [code] and [/code] tags to use fixed space formatting when posting.

From what I understand, you have a physical process, and each time a change happens in the physical process, a row in the table is updated to reflect the new status.

If I had the opportunity to do it from scratch, I would do it slightly differently. I would have a table into which you simply dump the information related to the latest status. Then, either via a view, or even a trigger I would update other tables that hold the current information.

For example, I would have a table (with the appropriate PK's etc., of course), where you enter a row each time a change happens. Something like this:

CREATE TABLE Queries
(
	QueryID INT, StatusID INT , Date DATETIME 
)

If I assume that my statuses are these:

-- 1 = initiated
-- 2 = ongoing
-- 3 = queried
-- 4 = completed

Then, I may have some sample data like this:

INSERT INTO Queries VALUES (1,1,'20120101');
INSERT INTO Queries VALUES(1,2,'20120107');
INSERT INTO Queries VALUES(1,1,'20120108');
INSERT INTO Queries VALUES(1,3,'20120111');
INSERT INTO Queries VALUES(1,4,'20120114');
INSERT INTO Queries VALUES(1,3,'20120118');
INSERT INTO Queries VALUES(1,4,'20120122');

Now I can query the table to see how much time was spent in each status:

SELECT
	a.QueryID,
	a.StatusID,
	SUM(DATEDIFF(dd,b.PriorDate,a.Date)) AS TimeSpentInStatus
FROM
	Queries a
	CROSS APPLY
	(
		SELECT MAX(c.Date) AS PriorDate
		FROM Queries c
		WHERE c.QueryID = a.QueryID
		AND c.Date < a.Date
	) b
GROUP BY
	a.QueryID,
	a.StatusID

If I want to know the current status, I can query like this:

SELECT
	QueryID,StatusID,Date
FROM
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY QueryID ORDER BY Date DESC) AS RN
	FROM Queries
)s
WHERE RN = 1;

You might even create a view out of that query to make it simpler for someone to do a select.

You can certainly use the approach you mentioned - where you have one row that gets updated each time there is a change, but to my way of thinking (perhaps a little convoluted, some might suggest

), this seems simpler

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-03 : 09:03:56

Sunitabeck

Thank you for the time and effort to write this. Its very much appreciated and exactly what I'm looking for in terms of a base to work from with real data!

I made one mistake of posting on T-SQL 2008 :( as the version they use is 2005 annoyingly and didnt realise as I connect via 2008 so I'll need to try and modify query but thank you for the base!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 10:14:12

Glad to be of help!

All the code that I had posted in my previous post should work without any changes on SQL 2005 or SQL 2008. Now if you said you were using SQL 2000 (or if your database compatibility level was set at 80), then those would not work.

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-03 : 10:59:18

Haha second mistake then its 2000, its confusing as we have 2005 and 2008 instances but the actual data is stored on 2000 then - makes no sense but im not a dba, i just jazz up reports on ssrs usually.

I am going to try and get tehm to migrate it to 2008R2 as rownumber is soooo useful (never used cross apply before)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 12:12:48

I may be wrong in saying this, but if your server is SQL 2005 or later, you might still be able to use some of the windowing functions and row number function even if your database compatibility level is 80.

If you need to make the queries compatible with SQL 2000, you can rewrite them like shown below. But, I have not tested these, and the second one especially will not work as expected if you have multiple entries for one QueryId in a single day.

SELECT
	QueryId,
	StatusID,
	SUM(Days) AS Days
FROM
(
	SELECT
		QueryId,
		StatusID,
		DATEDIFF ( 
			dd, 
			(SELECT MAX(date) FROM Queries q2 WHERE q2.QueryId = q1.QueryId AND q2.Date < q1.Date),
			Date
		) Days
	FROM
		Queries q1
) q
GROUP BY
	QueryId,
	StatusID

------------------------------
SELECT
	a.*
FROM
	Queries a
	INNER JOIN 
	(
		SELECT 	QueryId,MAX(Date) AS CurrentDate FROM Queries c GROUP BY QueryId
	) b ON a.QueryId = b.QueryId AND a.Date = b.CurrentDate;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources