CODEWITHSUNDEEP
sql-server
kalmeida
kalmeida

Reputation: 41

Calculate time from 1 column

Ok, here's my situation. Our previous DB specialist (SQL Server 2008) set up a table to store times for performance purposes. The times are generated from a VB script and entered into the table.

The performance process (in table: from bottom to top):

e.g.

Msrmnt  | Time                    | Window     | Function | Position  
--------+-------------------------+------------+----------+-----------
109     | 2016-05-10 20:35:32.243 | WindowName | Select   |    2  
109     | 2016-05-10 20:35:29.230 | WindowName | Select   |    1  
109     | 2016-05-10 20:35:26.697 | WindowName | Open     |    2     
109     | 2016-05-10 20:35:23.297 | WindowName | Open     |    1

Now, I need to calculate the time (e.g.) between the 2 open values (1 & 2). I do have a lot of experience with DB's, but I am stuck on how I should best approach this to be sure I am 1.) getting accurate results, 2.) the calculations are efficient regarding time (as there are lot of entries within a weeks time)

Thanks for any suggestions

Karen

Upvotes: 3

Views: 64

Answers (3)

kalmeida
kalmeida

Reputation: 41

Ok, I solved it like this . . .

Select t.ZP1, t.Form1, t.ZP2, t.Form2, DATEDIFF(MILLISECOND,t.ZP1,t.ZP2) as ms  
From  
(Select p1.time as ZP1, p1.window as Form1, p2.time as ZP2, p2.window as Form2
from tpermonitor p1 LEFT JOIN   
tpermonitor p2 On 
p2.msrmnt = p1.msrmnt 
and p2.Funktion = 'open' 
and p2.Position = 2 and p2.System = 'mysys'  
and p2.time Between '05.04.2016' and '05.13.2016'   
and p2.window = p1.window and p2.msrmnt = p1.msrmnt   
where  p1.System like 'mysys'  
and p1.time Between '05.04.2016' and '05.13.2016'   
and p1.Position = 1  
and p1.Funktion like 'Open'  
and CAST(p2.time as DATE) = CAST(p1.time as DATE)) as t  
Order by t.ZP1

I'm not sure it's the prettiest solution, but it works. Will work on a more elegant solution in the future, but for the time being, I'll work with this.

If anyone wishes to comment, I'm open . . .

Thanks for all your feedback

Upvotes: 1

Dariusz Bielak
Dariusz Bielak

Reputation: 415

Base on the date order in SQL 2008. If the platform is SQL 2012 then this can be LEAD or LAG function.

;WITH X AS (

SELECT 109 AS Msrmnt, CAST('2016-05-10 20:35:32.243' AS DATETIME) As Date, 'WindowName' AS Window,  'Select' AS [Function],   2 AS Position UNION ALL   
SELECT 109,CAST('2016-05-10 20:35:29.230' AS DATETIME),'WindowName' , 'Select',1  UNION ALL
SELECT 109,CAST('2016-05-10 20:35:26.697' AS DATETIME),'WindowName','Open',2     UNION ALL 
SELECT 109,CAST('2016-05-10 20:35:23.297' AS DATETIME),'WindowName','Open',1 
)
SELECT 
 DATEDIFF(S,x1.Date, x2.Date)
 ,x1.Date, x2.Date
FROM x as x1
 INNER JOIN x as x2 
  ON x1.Msrmnt = x2.Msrmnt AND x1.Window = x2.Window AND x1.Date < x2.Date 
WHERE x1.[Function] = 'Open' AND x2.[Function] = 'Open'

Upvotes: 2

gofr1
gofr1

Reputation: 15977

The basic example how to get calculated time between two time values:

;WITH cte AS (
SELECT *
FROM (VALUES
(109, '2016-05-10 20:35:32.243', 'WindowName', 'Select', 2),
(109, '2016-05-10 20:35:29.230', 'WindowName', 'Select', 1),
(109, '2016-05-10 20:35:26.697', 'WindowName', 'Open', 2),   
(109, '2016-05-10 20:35:23.297', 'WindowName', 'Open', 1)
) as t (Msrmnt, [Time], Window, [Function], Position)
)

SELECT  c.Msrmnt,
        c.Window,
        DATEDIFF(MILLISECOND,c.[Time],c1.[Time]) as ms
FROM cte c
INNER JOIN cte c1 
    ON c.Msrmnt = c1.Msrmnt 
        AND c.Window = c1.Window 
        AND c.[Function] = c1.[Function] 
        AND c.Position + 1= c1.Position
WHERE c.[Function] = 'Open'

Output:

Msrmnt      Window     ms
----------- ---------- -----------
109         WindowName 3400

Upvotes: 2

Related Questions