April 19, 2011 at 6:52 am
Hi,
I have one table with log data. What I know is that one action run till next record gets into the table column "Time Stamp Action".
Bellow is example of my table which I get from LINUX.
Time StampAction CodePerson ID
19.04.2011 09:03:391123
19.04.2011 09:03:412123
19.04.2011 09:03:413123
19.04.2011 09:04:151123
19.04.2011 09:04:152123
19.04.2011 09:04:243123
19.04.2011 09:05:574123
I want to know time duration between first and second row, second and third, third and fourth etc.
Table have approximately 800-1000 rows.
Can you help me please?
April 19, 2011 at 7:28 am
I have made a small change in the date field
Yours 19.04.2011and i have changed as 04/19/2011
declare @Temp table (Time_Stamp datetime ,Action_Code tinyint, Person_ID int)
Insert into @Temp
Select '04/19/2011 09:03:39', 1, 123
UNION ALL Select '04/19/2011 09:03:41', 2, 123
UNION ALL Select '04/19/2011 09:03:41', 3, 123
UNION ALL Select '04/19/2011 09:04:15', 1, 123
UNION ALL Select '04/19/2011 09:04:15', 2, 123
UNION ALL Select '04/19/2011 09:04:24', 3, 123
UNION ALL Select '04/19/2011 09:05:57', 4, 123
;WITH CTE AS
(
SELECT Time_Stamp, Action_Code,Person_ID,Rno = ROW_NUMBER() OVER (ORDER BY Time_Stamp)
FROM @Temp) , CTE2 AS (SELECT Time_Stamp,
--(Select MIN(CONVERT(time,Time_Stamp)) from CTE as a where a.Rno=c.Rno) as aa ,
--(Select MAX(CONVERT(time,Time_Stamp)) from CTE as b where b.Rno=c.Rno+1) as bb ,
Isnull(DATEDIFF(ss,(Select MIN(CONVERT(time,Time_Stamp)) from cte as a where a.Rno=c.Rno),
(Select MAX(CONVERT(time,Time_Stamp)) from CTE as b where b.Rno=c.Rno+1)),0)as Diff_in_Sec,
Action_Code,Person_ID
FROM CTE as c
GROUP BY Rno, Time_Stamp, Action_Code,Person_ID
)SELECT * FROM CTE2
And the difference has been shown in seconds
Thanks
Parthi
April 19, 2011 at 7:34 am
How's this?
-- specify dateformat for different areas:
SET DATEFORMAT DMY;
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @test-2 TABLE ([Time Stamp] DATETIME, [Action Code] INT, [Person ID] int);
INSERT INTO @test-2
SELECT '19.04.2011 09:03:39', 1, 123 UNION ALL
SELECT '19.04.2011 09:03:41', 2, 123 UNION ALL
SELECT '19.04.2011 09:03:41', 3, 123 UNION ALL
SELECT '19.04.2011 09:04:15', 1, 123 UNION ALL
SELECT '19.04.2011 09:04:15', 2, 123 UNION ALL
SELECT '19.04.2011 09:04:24', 3, 123 UNION ALL
SELECT '19.04.2011 09:05:57', 4, 123;
WITH cte AS
(
SELECT t.[Time Stamp],
t.[Action Code],
t.[Person ID],
RN = ROW_NUMBER() OVER (/*PARTITION BY [Person ID]?*/ ORDER BY [Time Stamp])
FROM @test-2 t
)
SELECT cte.*,
TimeDelta = DATEDIFF(SECOND, t2.[Time Stamp], cte.[Time Stamp])
FROM cte
LEFT JOIN cte t2
ON cte.RN = t2.RN+1
--AND cte.[Person ID] = t2.[Person ID]?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2011 at 11:54 pm
Hi,
I found new solution - not so IT, but it works...
First put IDENTITY column into table:
SELECT [0 TimeStampDtZt] AS TimeS, IDENTITY(int) AS ID
INTO my_Table
FROM ActLog
Than make two tables with splitting even and odd ID numbers:
SELECT IDENTITY(int) AS ID_End, TimeS, CONVERT(NUMERIC(5,0), ID) AS NumId
INTO my_Table_Even
FROM my_Table
WHERE (ID % 2) <> 0
SELECT IDENTITY(int) AS ID_Start, TimeE, CONVERT(NUMERIC(5,0), ID) AS NumId
INTO my_Table_Odd
FROM my_Table
WHERE (ID % 2) = 0
Than join that two tables:
SELECT my_Table_Even.NumId AS NumId_Start, my_Table_Even.TimeS AS TimeStart, my_Table_Odd.NumId AS NumId_End, my_Table_Odd.TimeE AS TimeEnd
into my_Table_Join
FROM my_Table_Even INNER JOIN
my_Table_Odd ON my_Table_Even.NumId = my_Table_Odd.NumId
Now I have table with time start and time end of every EVEN records of table ActLog. As I said it is not really IT solution, but I can use it for big table with let me say 1000 records.
At the end I can use DATEDIFF:
SELECT DATEDIFF(SECOND, (CONVERT(DATETIME, my_Table_Join.TimeStart, 104)), (CONVERT(DATETIME, my_Table_Join.TimeEnd, 104)) AS EvenDuration
FROM my_Table_Join
DONE
Now same procedure must be done for odd rows from table ActLog. Working quite good...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply