Average Duration Pulled from History Log... 1

[tqeonline]

Hello All - so I have a History Log that logs when something is Checked In and Checked Out. I need to create a query that will tell me the typical duration something is checked out.

So step 1 is to some how pair together a Checked OUT status with a Checked IN status. If there is no Checked IN status the I will replace it with GetDate().

The date looks like so:

ID	USER_ID	DEVICE_ID	ACTION	DATE_TIME
92	U004	D0001	OUT	2011-07-11 10:50:17.903
93	U004	D0002	IN	2011-07-11 10:50:21.607
94	U004	D0003	OUT	2011-07-11 10:50:25.140
95	U004	D0003	IN	2011-07-11 10:50:30.780

What i was thinking was something along these lines:

- Get ALL the "OUT"'s then look for the closest "IN" with the same DEVICE_ID that is Greater than the OUT I just grabbed.

ex. Using the Example above we would grab D0003 with the time of 2011-07-11 10:50:25.140 and then look for the MIN "IN" for D0003 that is Greater than "2011-07-11 10:50:25.140" which would be "2011-07-11 10:50:30.780" and it would return a duration of 5 seconds.

So in the case of D0001 there is no "IN" so I would just replace it with GETDATE() so the duration would grow every time you run the query.

- Sounds simple enough... but obviously not. I think I have the logic there just don't know where to begin with the SQL.

Any ideas? OR better logic to start with?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

Declare @_tbl as table(
ID int identity(1,1),
User_ID varchar(4),
Device_ID varchar(5),
Action varchar(3),
Date_Time datetime)
INSERT INTO @_tbl(USER_ID, DEVICE_ID, ACTION, DATE_TIME)
SELECT 'U004', 'D0001', 'OUT', '2011-07-11 10:50:17.903' UNION ALL
SELECT 'U004', 'D0002', 'IN', '2011-07-11 10:55:21.903'  UNION ALL
SELECT 'U004', 'D0003', 'OUT', '2011-07-11 10:57:23.903' UNION ALL
SELECT 'U004', 'D0003', 'IN', '2011-07-11 10:59:17.903'  UNION ALL
SELECT 'U004', 'D0003', 'OUT', '2011-07-11 11:05:23.903' UNION ALL
SELECT 'U004', 'D0003', 'IN', '2011-07-11 11:09:17.903'

SELECT i_out.ID, i_out.User_ID, i_out.Device_ID, i_out.Date_time Time_OUT, isNull(MIN(i_in.Date_time), GetDate()) Time_IN 
FROM 
	(select * from @_tbl WHERE Action = 'OUT') as i_out
LEFT JOIN 
	(SELECT * FROM @_tbl WHERE Action = 'IN') as i_in ON 
	i_out.User_ID = i_in.User_ID
	AND i_out.Device_ID = i_in.Device_ID
	AND i_out.Date_Time < i_in.Date_Time
GROUP BY i_out.ID, i_out.User_ID, i_out.Device_ID, i_out.Date_time

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[tqeonline]

how do you do that... you are rediculous...

SELECT i_out.ID, 
	i_out.User_ID, 
	i_out.Device_ID, 
	i_out.Date_time Time_OUT, 
	isNull(MIN(i_in.Date_time),GetDate()) Time_IN,
	DATEDIFF(ss,i_out.Date_time,isNull(MIN(i_in.Date_time),GetDate()))
FROM     
	(select * from [AMA_Device_History] 
	WHERE Action = 'OUT') as i_out
	
LEFT JOIN     
	(SELECT * FROM [AMA_Device_History]  WHERE Action = 'IN') as i_in 
	ON i_out.User_ID = i_in.User_ID    
	AND i_out.Device_ID = i_in.Device_ID    
	AND i_out.Date_Time < i_in.Date_Time
GROUP BY i_out.ID, i_out.User_ID, i_out.Device_ID, i_out.Date_time

This has duration. You are awesome my friend.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[markros]

I think you should pick either ridiculous or awesome. I'm wondering how a person can be both.

PluralSight Learning Library