Average days between, 1st & 2nd date, 2nd & 3rd...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Average days between, 1st & 2nd date, 2nd & 3rd...

Author

Topic

adrianmuller
Starting Member

4 Posts

Posted - 2009-04-07 : 07:50:02

Hello

I need to know the average number of days between the first and second click, the second and third, third and fourth click and so on for different users that each have a row every time they click.

Excerpt from data:

Computer ID Click Date
111 03/10/2008
111 08/10/2008
111 18/10/2008
111 03/11/2008
111 03/12/2008
222 03/10/2008
222 08/10/2008

So for example user 111, I need to know difference between 1st & 2nd, 2nd & 3rd, 3rd & 4th and 4ht & 5ht, while for user 333 I only need the difference between 1st & 2nd.

Problems that I have:

-I can not use only the max and min functions, cause there are in between values I also need to estimate.
-Users have a different number of clicks, so they are in one or more rows.

I want to group by computer id, and be able to tell, the average number of days between 1st and 2nd click for all users is....? the average number of days between 2nd and 3rd click for all users is...?

Thanks in advanced

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:06:37

[code]DECLARE @Sample TABLE
(
ComputerID INT,
ClickDate DATETIME
)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 111, '03/10/2008' UNION ALL
SELECT 111, '08/10/2008' UNION ALL
SELECT 111, '18/10/2008' UNION ALL
SELECT 111, '03/11/2008' UNION ALL
SELECT 111, '03/12/2008' UNION ALL
SELECT 222, '03/10/2008' UNION ALL
SELECT 222, '08/10/2008'

;WITH Yak (ComputerID, ClickDate, recID)
AS (
SELECT ComputerID,
ClickDate,
ROW_NUMBER() OVER (PARTITION BY ComputerID ORDER BY ClickDate)
FROM @Sample
)

SELECT y1.ComputerID,
y1.ClickDate,
DATEDIFF(DAY, y2.ClickDate, y1.ClickDate) AS [Days]
FROM Yak AS y1
LEFT JOIN Yak AS y2 ON y2.ComputerID = y1.ComputerID
AND y2.recID + 1 = y1.recID[/code]

E 12°55'05.63"
N 56°04'39.26"

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 08:13:21

Assume that your table is like this
create table ComputerClicks
(
[Computer ID] int ,
[Click Date] datetime,
[UserId] int
)
then the below query will give your required output.

with cte as
(
select *,row_number() over (partition by [Computer Id],UserId order by [Click Date]) as rn from ComputerClicks
)
,
cte1 as
(
select c.[Computer Id],datediff(day,c.[Click Date],c1.[Click Date]) as Days,'Between ' + cast( c.RN as varchar) + ' and ' + cast( c1.RN as varchar) as CLickType from cte c
inner join cte c1 on c1.rn = c.rn + 1 and c.[Computer Id] = c1.[computer Id] and c.UserId = c1.UserId
)
select [Computer Id],ClickType,Avg(Days) from cte1
group by [Computer Id],ClickType

adrianmuller
Starting Member

4 Posts

Posted - 2009-04-07 : 09:44:03

I am going to try it out and will let you know. thanks to both

adrianmuller
Starting Member

4 Posts

Posted - 2009-04-07 : 10:18:29

Dear ddramireddy,

Why do I have to create a join, the info is already in just one table.

quote:
Originally posted by ddramireddy

Assume that your table is like this
create table ComputerClicks
(
[Computer ID] int ,
[Click Date] datetime,
[UserId] int
)
then the below query will give your required output.

with cte as
(
select *,row_number() over (partition by [Computer Id],UserId order by [Click Date]) as rn from ComputerClicks
)
,
cte1 as
(
select c.[Computer Id],datediff(day,c.[Click Date],c1.[Click Date]) as Days,'Between ' + cast( c.RN as varchar) + ' and ' + cast( c1.RN as varchar) as CLickType from cte c
inner join cte c1 on c1.rn = c.rn + 1 and c.[Computer Id] = c1.[computer Id] and c.UserId = c1.UserId
)
select [Computer Id],ClickType,Avg(Days) from cte1
group by [Computer Id],ClickType

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:14:14

[code]
SELECT t.ComputerID,t.ClickDate,DATEDIFF(dd,t1.ClickDate,t.ClickDate) AS Diff
FROM Table t
OUTER APPLY(SELECT TOP 1 ClickDate
FROM Table
WHERE ComputerID=t.ComputerID
AND ClickDate<t.ClickDate
ORDER BY ClickDate DESC)t1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources