I am developing an SLA Report in which i have to find the elapse time between successive ‘status’ changes for each subrequet within a request.
See the example below:
In the above example, i need to calculate the ‘Elapse Time’ which is the difference between the two successive Timestamps when a ‘Status’ change occurs i.e the time taken for a subrequest to change from one Status to another.
Area of interest here is ‘How to catch the values of two successive timestamps, so that one can be subtracted from the other’
I used Previous() function in finding the difference between two succssive values in a column.
Find the calculation that i used, below,
=If (<Status(NewUser)>=“Just Created”) Then 0 Else ((((ToNumber(SubStr(FormatDate(<Min TimeStamp within Status(NewUser)> ,“HH:mm:ss”) , 1 ,2))-ToNumber(SubStr(FormatDate(Previous(<Min TimeStamp within Status(NewUser)>) ,“HH:mm:ss”) , 1 ,2)))
*60)+(ToNumber(SubStr(FormatDate(<Min TimeStamp within Status(NewUser)> ,“HH:mm:ss”) , 4 ,2))-ToNumber(SubStr(FormatDate(Previous(<Min TimeStamp within Status(NewUser)>) ,“HH:mm:ss”) , 4 ,2))))*60)+(ToNumber(SubStr(FormatDate(<Min TimeStamp within Status(NewUser)> ,“HH:mm:ss”) , 7 ,2))-ToNumber(SubStr(FormatDate(Previous(<Min TimeStamp within Status(NewUser)>) ,“HH:mm:ss”) , 7 ,2)))
This is to find the difference between two successive dates with timestamps, in a column.
‘Break’ is applied on ‘Request number’ and ‘subrequest id’ .
Note: But this didnot solve my requirement fully. I am facing a strange problem with the Previous() function. When i use this function for the first time on a column then the values are coming fine, but if i do any sorting on that column or change the sequence the values are not shown proplerly i.e the difference between the column value and its previous value is not showing correctly. I am not able to get what the problem is.
Has anyone come accross this, please guide me
Instead of using previous() at reporter level use Lag function to create universe object and then use this in the query panel. Syntax for lag function.
m,n lag(m,1) order by(n) …depends on your logic you can also use “partition” before order by clause (partition acts as group by in lag function) if columns are from different tables. Hope this helps
Hi,
We are using MS SQL Server2000 for database.I didn’t use LAG function earlier and know nothing about it.I could not find the LAG function under the list of functions available in Designer while creating an object.
So could you please guide me about the parameters of that function and how to use that.
LAG function is an analytic function and it gives value from the previous row. You dont have it in designer since its an analytic function. I used this with oracle(8i) backend. I am not quite sure how it works with SQl server. But in time difference reports we usually use this LAG function.
(BOB member since 2002-08-15)