sql server - How to loop through multiple login entries and calculate the Login Count and time in SQL 2005? -

i beginner in sql , hope people can me through ordeal.

the below data should grouped client, date , user id (which have achieved).

client user id date action module moha mother 01/10/2010 12:35:36 pm login pp moha voodoo 02/10/2010 05:15:28 pm login pp moha panther 04/10/2010 04:36:42 pm login pp moha mother 01/10/2010 12:42:35 pm action pp moha mother 01/10/2010 12:55:14 pm action pp moha voodoo 02/10/2010 06:35:46 pm login pp moha panther 04/10/2010 04:53:24 pm action pp moha deuce 05/10/2010 09:13:42 pm login pp moha deuce 05/10/2010 09:19:42 pm action pp moha panther 06/10/2010 08:11:22 pm login pp moha deuce 05/10/2010 09:27:49 pm action pp moha panther 06/10/2010 08:15:18 pm action pp moha panther 06/10/2010 08:44:53 pm action pp moha deuce 05/10/2010 09:27:49 pm login pp moha rabbit 05/20/2010 09:27:49 pm login pp moha voodoo 02/10/2010 06:57:35 pm action pp moha deuce 06/10/2010 08:30:59 login pp moha rabbit 05/21/2010 09:27:49 pm login pp moha mother 03/10/2010 01:04:54 pm login pp moha mother 03/10/2010 01:23:55 pm action pp moha mother 03/10/2010 02:25:46 pm login pp moha mother 03/10/2010 02:45:54 pm action pp 

once done want know if below information achievable through sql.

client user id date login cnt login amt (mins) moha mother 01/10/2010 1 19.6 (00:06:59 + 00:12:39) - bracket info not req moha mother 03/10/2010 2 39.2 (00:19:01 + 00:20:08) moha vodoo … … … moha … … … …

i have written procedure dynamically accept optional parameters client, start date, end date , userid. procedure first convert date varchar format , extract date grouping purposes , calculate login count using case statement. but..!! how calculate time difference between 2 consecutive entries , login count under grouping rules? possible achieve same using cursors?

please feel ask questions , give suggestions on above mentioned topic 1 of critical requirements. thank beforehand.

thanks cs

there multiple solutions problem.

first of all, must cannot give complete solution problem. login count easy matter, login duration of each user require more information. based on present data, unclear of dates signifies logout time. assuming last occurrence of 'some action' before there new login, solution possible, don´t know if assumption correct or not. if user opens multiple sessions in parallel, assumption break, , if logout time not recorded (eg. when session times out after point , time not stored) there not can do.

anyway, there 3 points want make:

• general argument of set-based queries versus cursors
• suggested query login count
• hints on how proceed login duration

the first point: suggest use cursors , stored procedure determine login count. solution work, typically how proceed in normal programming language example c or pascal: define for-loop on data , calculation or routine each row. in sql, advisable think differently. use cursors , loops when have to, , otherwise use set-based solution. reason set-based queries easier optimize sql interpreter. argument made convincingly in several articles elsewhere, example here: why relational set-based queries better cursors?

so how proceed in "set-based" way? starting login count, like:

select user, count(*) yourtable action = 'login' group user 

this return number of logins per user. if want number of logins in time period, can add date criterium where field, , if want eg. number of logins per client or per day, need add client or cast(date) date field both select , group by clauses.

finally login duration, question how calculate time difference between 2 consecutive entries , indeed how should done. in mssql, row_number() (although there might better solutions around particular case):

with numberedtable (     select user, date, row_number() on (partition user order date) loginoccurrencesortedbydate     yourtable     action = 'login' ) select l.user, l.date, datediff(seconds, r.date, l.date) differencewithpreviouslogin  numberedtable l left join numberedtable r on r.loginoccurrencesortedbydate = l.loginoccurrencesortedbydate - 1 

you left join each entry previous occurrence , calculate time difference.