I have a sql table with the following fields:
Action, Timestamp
Action can be In, Out, Denied.
ex
| 1/2/2008 6:10:12 AM |
ansys |
OUT |
struct |
| 1/2/2008 6:10:34 AM |
ansys |
IN |
struct |
| 1/2/2008 6:24:30 AM |
ansys |
OUT |
agppi |
| 1/2/2008 7:04:34 AM |
ansys |
IN |
struct |
| 1/2/2008 7:05:07 AM |
ansys |
OUT |
struct |
| 1/2/2008 7:13:53 AM |
ansys |
OUT |
struct |
| 1/2/2008 7:15:53 AM |
ansys |
IN |
struct |
| 1/2/2008 7:16:08 AM |
ansys |
DENIED |
struct |
How can I get data and subtract between out and denied to get the minute ?
Below is what I have, but looks like the data not line up correctly.
Thanks for any help
select RecDate oRecDate, Action oAction, [User] oUser, software oSoft, acct_dept_no oDeptinto #ustemp from ANSYS where Action = 'DENIED' AND (RecDate BETWEEN @StartDate AND @EndDate) order by RecDate ;
with user_cte
as(select top 100 percent oRecDate, oUser, oSoft, oDept, RecDate
from #ustemp, ANSYSwhere oUser = [user]
and oSoft = Softwareand oRecDate < RecDate
and Action = 'IN' AND (RecDate BETWEEN @StartDate AND @EndDate) order by RecDate)select oUser
, oDept
, DATEDIFF(mi, oRecDate, Min(RecDate)) Minutes
from user_cte
group by oUser, oSoft, oDept, orecDate