Help with SQL query

Hello EE,

I have a table wich saves the time and an action.

something like :

SIGN IN      06/06/2013 12:42:01.830
PRESSED     06/06/2013 12:45:11.293
PRESSED     06/06/2013 12:45:41.342
PRESSED     06/06/2013 12:45:11.293
PRESSED     06/06/2013 12:45:41.342
PAUSED      06/06/2013 12:46:21.552
RESUMED   06/06/2013 12:55:01.193
UNPRESSED ....
etc

only possible action are:  sign in, sign out, pressed, unpressed, pause, resume

I want to know how many time in minutes the guy took pause..
here it would be : 12:55:01.193  minus 12:46:21.552

the guy could take multiple pauses.. i need a query that it tells me the total ...

can you help ? thanks...
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
Yes, just replace the SELECT statement with:
SELECT @total=SUM(DATEDIFF(MILLISECOND,T2.DT, T1.DT))/60000.0

Open in new window

0
 
Om PrakashCommented:
Try something like:

Select 
FLOOR(Cast(Dummy as Float)) as Days
,DATEPART(Hour,dummy) as Hours
,DATEPART(MINUTE,dummy) as Minutes
,DATEPART(Second,dummy) as Seconds
from
(
Select 
DateAdd(second, SUM(DATEDIFF(SECOND,start,ende)),Cast(0 as Datetime)) as Dummy 
from table_name
) a

Open in new window

http://stackoverflow.com/questions/13697181/sql-query-for-finding-difference-of-two-time-and-sum
0
 
Easwaran ParamasivamCommented:
Order the dattime field.. Have an Id value as first column..

Whenever paused is found take the id, take the (id + 1)  that is resumed state and take the datetime difference. Below link could help you.

http://stackoverflow.com/questions/2116540/difference-of-two-date-time-in-sql-server
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
PhilippeRenaudAuthor Commented:
start, ended ?  you writing if I had 2 columns.. but I only have my column called: Action_Date
0
 
ThomasianCommented:
DECLARE @t TABLE (Act varchar(10), DT datetime)

INSERT @t
SELECT 'SIGN IN', '06/06/2013 12:42:01.830'
UNION ALL SELECT 'PAUSED', '06/06/2013 12:42:11.293'
UNION ALL SELECT 'PRESSED', '06/06/2013 12:43:31.142'
UNION ALL SELECT 'RESUMED', '06/06/2013 12:45:11.295'
UNION ALL SELECT 'PRESSED', '06/06/2013 12:45:41.342'
UNION ALL SELECT 'PAUSED', '06/06/2013 12:46:21.552'
UNION ALL SELECT 'RESUMED', '06/06/2013 12:55:01.193'

;WITH CTE AS (
	SELECT *, rn=ROW_NUMBER() OVER (ORDER BY DT)
	FROM @t
	WHERE Act IN ('PAUSED','RESUMED')
)
SELECT SUM(DATEDIFF(MILLISECOND,T2.DT, T1.DT))/60000.0
FROM CTE T1 INNER JOIN CTE T2 ON T1.rn = T2.rn+1
WHERE T2.Act = 'PAUSED' AND T1.Act = 'RESUMED'

Open in new window

0
 
PhilippeRenaudAuthor Commented:
Thomasian, thanks, is there a way to put the select result into a variable? im trying :  set @total = ( your final select here )  not working..
0
 
PhilippeRenaudAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.