for loop - Running WHILE or CURSOR or both in SQL Server 2008 -

for loop - Running WHILE or CURSOR or both in SQL Server 2008 -

i trying run loop of sort in sql server 2008/tsql , unsure whether should while or cursor or both. end result trying loop through list of user logins, determine unique users, run loop determine how many visits took user on site 5 minutes , broken out channel.

table: loginhistory

userid channel datetime durationinseconds 1 website 1/1/2013 1:13pm 170 2 mobile 1/1/2013 2:10pm 60 3 website 1/1/2013 3:10pm 180 4 website 1/1/2013 3:20pm 280 5 website 1/1/2013 5:00pm 60 1 website 1/1/2013 5:05pm 500 3 website 1/1/2013 5:45pm 120 1 mobile 1/1/2013 6:00pm 30 2 mobile 1/1/2013 6:10pm 90 5 mobile 1/1/2013 7:30pm 400 3 website 1/1/2013 8:00pm 30 1 mobile 1/1/2013 9:30pm 200

sql fiddle schema

i can select unique users new table so:

select userid #users loginhistory grouping userid

now, functionality i'm trying develop loop on these unique userids, order logins datetime, count number of logins needed 300 seconds.

the result set hope this:

userid totallogins websitelogins mobilelogins loginsneededto5min 1 4 2 2 2 2 2 2 0 0 3 3 3 0 3 4 1 1 0 0 5 2 1 1 2

if performing in language, think this: (and apologies because not complete, think going)

for (i in #users): totallogins = count(*), websitelogins = count(*) channel = 'website', mobilelogins = count(*) channel = 'mobile', (i in loginhistory): if duration < 300: count(numlogins) + 1

** ok - i'm laughing @ myself way combined multiple different languages/syntaxes, how thinking solving **

thoughts on way accomplish this? preference utilize loop can go on write if/then logic code.

ok, 1 of times cursor outperform set based solution. sadly, i'm not cursors, can give set base of operations solution try:

;with cte ( select *, row_number() over(partition userid order [datetime]) rn userlogins ), cte2 ( select *, 1 recursionlevel cte rn = 1 union select b.userid, b.channel, b.[datetime], a.durationinseconds+b.durationinseconds, b.rn, recursionlevel+1 cte2 inner bring together cte b on a.userid = b.userid , a.rn = b.rn - 1 ) select a.userid, count(*) totallogins, sum(case when channel = 'website' 1 else 0 end) websitelogins, sum(case when channel = 'mobile' 1 else 0 end) mobilelogins, isnull(min(recursionlevel),0) loginsneedeto5min userlogins left bring together ( select userid, min(recursionlevel) recursionlevel cte2 durationinseconds > 300 grouping userid) b on a.userid = b.userid grouping a.userid

sql-server-2008 for-loop