TSQL problem to calculate differences between two dates inside one table
In MSSQL 2008 I have table and data which looks like this
create table #tempData
(user_id int,type varchar(10),ts datetime)
insert into #tempData
select 1,'ENTER','2011-01-30 15:00:00'
union all
select 1,'EXIT','2011-01-31 16:00:00'
union all
select 1,'ENTER','2011-02-1 18:00:00'
union all
select 1,'EXIT','2011-02-10 21:00:00'
union all
select 2,'ENTER','2011-01-10 21:00:00'
union all
select 2,'EXIT','2011-01-12 21:00:00'
union all
select 2,'ENTER','2011-01-13 01:00:00'
union all
select 2,'EXIT','2011-01-13 18:00:00'
--AND SO ON --
Now I trying to make query which is going to tell how long one user was inside according to last exit
something likeuser_id,exited,time_in_hours
1,'2011-01-31 16:00:00',25
1,'2011-02-10 21:00:00',219
2,'2011-01-12 21:00:00',48
2,'2011-01-13 18:00:00',17
Sor开发者_如何学Pythonry if this BAD or ROUGE question and if this is not proper way to ask a question like this. But I am stacked on this for while.
Assuming that
same-user sessions do not intersect,
there cannot be an EXIT record without the corresponding ENTER record,
you could try the following:
WITH ranked AS (
SELECT
user_id,
type,
ts,
rn = ROW_NUMBER() OVER (PARTITION BY user_id, type ORDER BY ts)
FROM #tempData
)
SELECT
tx.user_id,
exited = tx.ts,
hours = DATEDIFF(HOUR, tn.ts, tx.ts)
FROM ranked tn
INNER JOIN ranked tx ON tn.user_id = tx.user_id AND tn.rn = tx.rn
WHERE tn.type = 'ENTER'
AND tx.type = 'EXIT'
Using CTE's you can do
with
exits as ( select [user_id],ts from #tempData where type='EXIT'),
entries as ( select [user_id],ts from #tempData where type='ENTER'),
result as (
select
RANK() over (Partition by a.user_id,b.ts order by a.ts desc) as i,
a.[user_id],b.ts as 'Last Exited',DATEDIFF(hh,a.ts,b.ts) as [hours]
from
entries a inner join exits b on b.[User_id]=a.[User_id] and a.ts<b.ts
)
select user_id,[Last Exited],hours from result where i=1
This solution however assumes that there is always an exit record than that record always matches to the immediately preceeding entry record
Try
select a.usr,a.ts enter_date
,min(b.ts) exit_date, datediff(ss,a.ts,min(b.ts)) diff_in_sec
from tempData a
join tempdata b on a.usr = b.usr and a.ts < b.ts
where a.type='enter' and b.type='exit'
group by a.usr,a.ts
Try this:
SELECT user_id, exitTable.ts AS exited, DATEDIFF(hour, exitTable.ts, enterTable.ts) AS time_in_hours
FROM yourTable enterTable
INNER JOIN yourTable exitTable
ON enterTable.user_id = exitTable.user_id
and exitTable.ts > enterTable.ts
and exitTable.type = 'EXIT'
WHERE enterTable.type = 'ENTER'
and entertable.ts = (SELECT MAX(ts) FROM yourTable WHERE yourTable.user_id = enterTable.user_id AND type = 'ENTER')
A word of explanation...
The WHERE clause will limit the rows to the last ENTER for each user_id. The JOIN clause joins that row with the EXIT that has a time after the entrance.
精彩评论