TSQL: Consecutive Days Help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

TSQL: Consecutive Days Help

Author

Topic

angelie128
Starting Member

4 Posts

Posted - 2011-03-14 : 13:06:32

Hi! I have this table with user logins data. I basically have to find out the max consecutive login days of a user for a given month. Can someone please help? I am stumped! Here is the dataset to get it started. Thanks a bunch for your help!

set nocount on
declare @mytable TABLE(userid int, actdate datetime)
insert into @mytable
select 1, '1/1/11' UNION ALL
select 1, '1/3/11' UNION ALL
select 1, '1/4/11' UNION ALL
select 1, '2/1/11' UNION ALL
select 1, '2/2/11' UNION ALL
select 1, '2/3/11' UNION ALL
select 1, '2/4/11' UNION ALL
select 2, '2/4/11' UNION ALL
select 2, '2/5/11' UNION ALL
select 2, '2/6/11'

SELECT * FROM @MYTABLE

Sachin.Nand

2937 Posts

Posted - 2011-03-14 : 14:57:24

So what should be the o/p for the sample data posted ?

PBUH

angelie128
Starting Member

4 Posts

Posted - 2011-03-14 : 15:00:10

Thanks for the reply!

The output should be:

userID Date maxConsecutiveDays
1 1/2011 2
1 2/2011 4
2 2/2011 3

Thank you for your help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-14 : 15:02:17

Here is a way to do it assuming I am inferring your requirement correctly. There may be more efficient and simpler ways to do it, but nothing comes to mind right now.

with A as
(
   select
      1 as lvl,
      userId,
      actdate as date
   from 
      @MYTABLE
   union all
   select
      a.lvl+1,
      m.userId,
      m.actDate
   from
      @MYTABLE m
      inner join A on a.date + 1 = actDAte and a.userId = m.userId
)
select userId,max(lvl) MaxConsecutiveDays from A group by userId option (maxrecursion 0);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-14 : 15:04:50

I didn't see Sachin's question and your response to it before I posted the code. So don't use my code, it is not to your specs. Conceivably can be modified to do what you are asking it to do.

angelie128
Starting Member

4 Posts

Posted - 2011-03-14 : 15:30:28

How would you modify it, Sunita?

Sachin.Nand

2937 Posts

Posted - 2011-03-14 : 15:45:36

[code]
set nocount on
declare @mytable TABLE(userid int, actdate datetime)
insert into @mytable
select 1, '1/1/11' UNION ALL
select 1, '1/3/11' UNION ALL
select 1, '1/4/11' UNION ALL
select 1, '2/1/11' UNION ALL
select 1, '2/2/11' UNION ALL
select 1, '2/3/11' UNION ALL
select 1, '2/4/11' UNION ALL
select 2, '2/4/11' UNION ALL
select 2, '2/5/11' UNION ALL
select 2, '2/6/11'

select userid,datepart(m,dt)month,count(datediff(dd,dt,actdate))+1 count from @mytable t1
cross apply(select top 1 actdate dt from @mytable t2 where
t1.userid=t2.userid and t1.actdate=t2.actdate+1 and datepart(M,t1.actdate)=datepart(M,t2.actdate) order by actdate)T
group by datepart(m,dt),userid
[/code]

PBUH

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-14 : 16:22:45

[code];with A as
(
select
1 as lvl,
userId,
month(actdate) as Mnth,
year(actdate) as Yr,
actdate as date
from
@MYTABLE
union all
select
a.lvl+1,
a.userId,
a.Mnth,
a.Yr,
m.actDate
from
@MYTABLE m
inner join A on a.date + 1 = actDAte and a.userId = m.userId
and a.Yr = year(actDate) and a.Mnth = month(actdate)
)
select userId,Mnth,Yr,max(lvl) MaxConsecutiveDays from A group by userId,Mnth,Yr[/code]

But, I like Sachin's code a little better, mainly because I have an irrational fear when it comes to recursion. In Sachin's code, I would add a year component also - if there is a possibility that you may need to use this for multiple years.

angelie128
Starting Member

4 Posts

Posted - 2011-03-14 : 19:27:10

Sunita and Sachin, thanks so much for both of your help!
I tried both solution and while Sachin runs faster, some of the max consecutive dates on certain user id's always end up being more than what it really is. On the other hand, Sunita's would run slower but yields the correct max days on each one of the user ID's.

Sunita, I actually followed your advice, even before seeing your code, of adding the Year component to Sachin's solution. Below, is the modified code I have on Sachin's and I can't exactly pinpoint which part of the code is making certain user ID's have the incorrect calculate max dates, but I could only guess that it may be caused by the recursion part? In either case, I would love to know!

THank you both for you help!


-- Sachin's Code, with a little modification from me (adding year 
-- datepart, but still doesn't work
select
	user_id
	,DATEPART(MM,LOGIN_DATE) AS MONTH
	,DATEPART(YY,LOGIN_DATE) AS LOGIN
	,count(datediff(dd,dt,login_date))+1 count 
from 
	KlickNation.dbo.daily_logins t1
	cross apply
	(	select	top 1 login_date dt 
		from	KlickNation.dbo.daily_logins t2 
		where
			t1.user_id = t2.user_id 
			and t1.login_date = t2.login_date+1 
			and datepart(MM,t1.login_date) = datepart(M,t2.login_date) 
			AND DATEPART(YY,T1.LOGIN_DATE) = DATEPART(YY,T2.login_date)
		order by login_date
	)T
group by 
	DATEPART(MM,LOGIN_DATE)
	,DATEPART(YY,LOGIN_DATE)
	,user_id
ORDER BY 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-14 : 20:05:02

Do you have more than one entry for one user on a single day? If so, that could cause this. You can check using this query

select userid, login_date,count(*) 
from KlickNation.dbo.daily_logins 
group by userid, login_date 
having count(*) > 1

Sachin.Nand

2937 Posts

Posted - 2011-03-15 : 01:23:25

quote:
Originally posted by angelie128

I tried both solution and while Sachin runs faster, some of the max consecutive dates on certain user id's always end up being more than what it really is.On the other hand, Sunita's would run slower but yields the correct max days on each one of the user ID's.

Is it possible that you post the dates which you think are showing wrong o/p ?

PBUH

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 07:14:07

;with cte_days as
(select userid,actdate,datediff(day,0,actdate) - row_number()over(partition by userid order by actdate) as diff
from @myTable
)

SELECT userid,min(actdate) as Start,count( diff) as ConsecutiveDays
FROM cte_days
GROUP BY userid,diff
HAVING count( diff) > 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources