June 28, 2011 at 7:12 am
Hi all
I have a table with username and visit_date, I want to display users who visited multiple times with in a time period. Below is the table script and sample data.
--table script
create table visits (username varchar(50),visit_date datetime)
--sample data
username visit_date
aaa 6/1/2011
aaa 6/5/2011
bbb 6/10/2011
ccc 6/11/2011
ddd 6/5/2011
ddd 6/20/2011
ddd 6/25/2011
Now I want to find visited date difference for each user
usernamevisit_dateDate_Difference
aaa6/1/2011 0
aaa6/5/2011 4
bbb6/10/2011 0
ccc6/11/2011 0
ddd6/5/2011 0
ddd6/20/201115
ddd6/25/20115
thanks in advance..
Raghavendra
June 28, 2011 at 7:19 am
something like this? i leave it to you to decide teh datediff period of time....i chose hours, you can change to days
/*--Results
rw username visit_date visit_date HoursDiff
-------------------- -------- ----------------------- ----------------------- -----------
1 aaa 2011-06-01 00:00:00.000 2011-06-05 00:00:00.000 96
2 aaa 2011-06-05 00:00:00.000 NULL NULL
1 bbb 2011-06-10 00:00:00.000 NULL NULL
1 ccc 2011-06-11 00:00:00.000 NULL NULL
1 ddd 2011-06-05 00:00:00.000 2011-06-20 00:00:00.000 360
2 ddd 2011-06-20 00:00:00.000 2011-06-25 00:00:00.000 120
3 ddd 2011-06-25 00:00:00.000 NULL NULL*/
;with MySampleData(username,visit_date)
AS
(
SELECT 'aaa',convert(datetime,'6/1/2011') UNION ALL
SELECT 'aaa','6/5/2011' UNION ALL
SELECT 'bbb','6/10/2011' UNION ALL
SELECT 'ccc','6/11/2011' UNION ALL
SELECT 'ddd','6/5/2011' UNION ALL
SELECT 'ddd','6/20/2011' UNION ALL
SELECT 'ddd','6/25/2011'
)
, MyIntermediateData AS
(
SELECT row_number() OVER (PARTITION BY username ORDER BY username,visit_date) AS RW,
MySampleData.*
FROM MySampleData
)
SELECT x.rw, x.username,x.visit_date,y.visit_date,DATEDIFF(hh,x.visit_date,y.visit_date) As HoursDiff
FROM MyIntermediateData X
LEFT OUTER JOIN MyIntermediateData Y
ON x.username=y.username
AND x.RW + 1 = Y.RW
Lowell
June 28, 2011 at 7:32 am
declare @visits table(username varchar(50),visit_date datetime)
--sample data
insert into @visits
select
'aaa' ,'6/1/2011'
union
select
'aaa' ,'6/5/2011'
union
select
'bbb' ,'6/10/2011'
union
select
'ccc' ,'6/11/2011'
union
select
'ddd', '6/5/2011'
union
select
'ddd' ,'6/20/2011'
union
select
'ddd','6/25/2011'
;
with my_cte(username,visit_date,myRowNumber) as (
select *
,ROW_NUMBER() OVER(PARTITION BY username order by visit_date) as myRowNum
from
@visits v1
)
select v1.username,v1.visit_date
,DATEDIFF(DAY,v1.visit_date,v2.visit_date)
from my_cte v1
left join my_cte v2 on v1.myRowNumber = v2.myRowNumber - 1
and v1.username = v2.username
order by v1.username,v1.visit_date
Edit: damn, someone else got in there first 🙂
June 28, 2011 at 7:40 am
lol except for aliases, we got the same solution!
Lowell
June 28, 2011 at 7:47 am
Yes, this is what I wanted
thanks
June 28, 2011 at 8:35 am
Lowell (6/28/2011)
lol except for aliases, we got the same solution!
I wonder if there are other ways of doing this, or is this the accepted 'best' way?
June 28, 2011 at 9:00 am
i cannot think of another way to do it easily; row_number makes the join simpler, but you could join two copies of the tables together based on the name and date field;
other than that, you still have to join the table against itself, right?
SELECT
x.username,
x.visit_date,
y.visit_date,
DATEDIFF(dd,x.visit_date,y.visit_date) As HoursDiff
FROM MySampleData X
LEFT OUTER JOIN MySampleData Y
ON x.username=y.username
AND x.visit_date < y.visit_date
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply