February 17, 2009 at 4:24 am
I have a table which is structured as follows;
ObjectID, ObjectStateID, DateTime
A new record will be inserted each time a ObjectStateID changes. The ObjectStateID can be one of the following;
1 = Waiting
2 = Running
3 = Successful
4 = Error
The same ObjectID will be in the table multiple times. I need to write a select statement which calculates the difference between when the object was set to running to the subsequent change to either Successful or Error. Note: It will always change to one of these 2 statuses following a running state.
Thanks
February 17, 2009 at 5:24 am
How about this one?
DECLARE @tblObjects TABLE( ObjectID INT NOT NULL, ObjectStateID TINYINT NOT NULL, ObjectStateDateTime DATETIME NOT NULL )
INSERT@tblObjects( ObjectID, ObjectStateID, ObjectStateDateTime )
SELECT1, 1, '2009-02-17 17:38:51.393'
UNION ALL
SELECT1, 2, '2009-02-17 17:39:51.393'
UNION ALL
SELECT1, 3, '2009-02-17 17:39:54.714'
UNION ALL
SELECT2, 1, '2009-02-17 14:47:18.87'
UNION ALL
SELECT2, 2, '2009-02-17 14:47:58.10'
UNION ALL
SELECT2, 4, '2009-02-17 14:48:18.475'
--SELECT * FROM @tblObjects
;WITH ObjectStates
AS
(
SELECTROW_NUMBER() OVER( PARTITION BY ObjectID ORDER BY ObjectStateID ) AS RowNumber,
ObjectID, ObjectStateID, ObjectStateDateTime
FROM@tblObjects
WHEREObjectStateID > 1
)
SELECTObjectID, RunningState, SucessOrErrorState,
DATEDIFF( ms, RunningState, SucessOrErrorState ) AS TimeTakenInMS
FROM(
SELECTObjectID,
MAX( CASE WHEN RowNumber = 1 THEN ObjectStateDateTime ELSE NULL END ) AS RunningState,
MAX( CASE WHEN RowNumber = 2 THEN ObjectStateDateTime ELSE NULL END ) AS SucessOrErrorState
FROMObjectStates
GROUP BY ObjectID
) O
--Ramesh
February 17, 2009 at 5:54 am
This is good. Closer to what I require.
The point that I need to make clear is that this table will contain object information for each day, (sometimes more than once) for the same ObjectIds.
For example
on 18/02/09 02:00:00.000
ObjectId 4 could go from Running to Successful
on 19/02/09 02:00:00.000
ObjectId 4 could go from Running to Successful
on 19/02/09 13:52:18.475
ObjectId 4 could go from Running to Successful
on 20/02/09 02:00:00.000
ObjectId 4 could go from Running to Error
For each time I would need to measure the time from the Running state to the Successful or Error state. I hope I have explained this a little better!!
Thanks
February 17, 2009 at 6:40 am
Here's the old-fashioned way:
DROP TABLE #Table
CREATE TABLE #Table (ObjectID INT, ObjectStateID INT, [DateTime] DATETIME)
INSERT INTO #Table (ObjectID, ObjectStateID, [DateTime])
SELECT 1, 1, '2009-02-17 12:10:47.000' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17 12:10:47.180' UNION ALL -- running
SELECT 1, 3, '2009-02-17 12:11:06.490' UNION ALL -- successful
SELECT 1, 1, '2009-02-17 12:11:06.490' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17 12:11:55.460' UNION ALL -- running
SELECT 1, 4, '2009-02-17 12:11:56.490' UNION ALL -- error
SELECT 1, 1, '2009-02-17 12:11:56.490' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17 12:12:35.227' UNION ALL -- running
SELECT 1, 3, '2009-02-17 12:12:44.273' -- successful
SELECT ObjectID, ObjectStateID, StartDateTime = [DateTime],
StopDateTime = (SELECT MIN([DateTime])
FROM #Table b
WHERE b.ObjectID = a.ObjectID
AND b.[DateTime] > a.[DateTime] AND ObjectStateID IN (3,4))
FROM #Table a
WHERE ObjectStateID = 2
ORDER BY ObjectID, ObjectStateID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 17, 2009 at 7:21 am
If you can guarantee that each 'Running' state has a matching 'Success' or 'Error' (i.e. you never get consecutive 'Running' state) you could tryDROP TABLE #Table
CREATE TABLE #Table (ObjectID INT, ObjectStateID INT, [DateTime] DATETIME)
INSERT INTO #Table (ObjectID, ObjectStateID, [DateTime])
SELECT 1, 1, '2009-02-17 12:10:47.000' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17 12:10:47.180' UNION ALL -- running
SELECT 1, 3, '2009-02-17 12:11:06.490' UNION ALL -- successful
SELECT 1, 1, '2009-02-17 12:11:06.490' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17 12:11:55.460' UNION ALL -- running
SELECT 1, 4, '2009-02-17 12:11:56.490' UNION ALL -- error
SELECT 1, 1, '2009-02-17 12:11:56.490' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17 12:12:35.227' UNION ALL -- running
SELECT 1, 3, '2009-02-17 12:12:44.273' -- successful
UNION ALL SELECT 2, 1, '2009-02-17 14:47:18.87'
UNION ALL SELECT 2, 2, '2009-02-17 14:47:58.10'
UNION ALL SELECT 2, 4, '2009-02-17 14:48:18.475'
select
a.objectid, a.[datetime] as 'start', b.[datetime] as 'finish'
from (
select
row_number() over(partition by objectid order by [datetime]) as 'row',
objectid, datetime
from #table
where objectstateid=2
) a join (
select
row_number() over(partition by objectid order by [datetime]) as 'row',
objectid, datetime
from #table
where objectstateid in (3,4)
) b
on a.row=b.row
and a.objectid=b.objectid
Derek
February 17, 2009 at 7:45 am
aaa (2/17/2009)
This is good. Closer to what I require.The point that I need to make clear is that this table will contain object information for each day, (sometimes more than once) for the same ObjectIds.
this refinement of Ramesh's technique should work:
DECLARE @tblObjects TABLE( ObjectID INT NOT NULL,
ObjectStateID TINYINT NOT NULL,
ObjectStateDateTime DATETIME NOT NULL )
INSERT @tblObjects( ObjectID, ObjectStateID, ObjectStateDateTime )
SELECT 1, 1, '2009-02-17 17:38' UNION ALL
SELECT 1, 2, '2009-02-17 17:39' UNION ALL
SELECT 1, 3, '2009-02-17 17:41' union all
SELECT 1, 2, '2009-02-17 19:39' union all
SELECT 1, 4, '2009-02-17 19:49' UNION ALL
SELECT 2, 1, '2009-02-17 14:47' UNION ALL
SELECT 2, 2, '2009-02-17 14:48' UNION ALL
SELECT 2, 4, '2009-02-17 14:49' union all
SELECT 3, 2, '2009-02-17 14:50'
;WITH ObjectStates
AS ( SELECT ROW_NUMBER() OVER( PARTITION BY ObjectID
ORDER BY ObjectStateDateTime ) AS RowNumber,
ObjectID, ObjectStateID, ObjectStateDateTime
FROM @tblObjects
)--select * from ObjectStates
SELECT OS1.ObjectID, OS1.ObjectStateID, OS1.ObjectStateDateTime,
OS2.ObjectStateID, OS2.ObjectStateDateTime,
DATEDIFF( ms, OS1.ObjectStateDateTime, OS2.ObjectStateDateTime ) AS TimeTakenInMS
FROM ObjectStates OS1 left outer join ObjectStates OS2
on OS1.ObjectID = OS2.ObjectId and OS1.RowNumber + 1 = OS2.RowNumber
WHERE OS1.ObjectStateID = 2
[font="Courier New"]122009-02-17 17:3932009-02-17 17:41120000
122009-02-17 19:3942009-02-17 19:49600000
222009-02-17 14:4842009-02-17 14:4960000
322009-02-17 14:50NULLNULLNULL[/font]
February 17, 2009 at 8:12 am
Jeff Moden has written a couple of great articles on this topic:
e.g. http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 17, 2009 at 1:07 pm
Thanks for your help guys. At the moment I used Derek's old skool technique provided earlier. But I will be sure to review Jeff's article and see if I can utilise 2005 functionality.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply