Hi I'm creating a macro to show how many times a user has logged into our database within a month. The output should look like this :

Name

Kit
Peter
Jeny
Katie
Patricia

Last Login date
Dec 28 2004 07:12AM
Dec 28 2004 09:30AM
Dec 27 2004 10:23AM
Dec 28 2004 10:38AM
Dec 27 2004 10:30AM

Login count
12/26/04
0
0
1
0
0

Login count
12/27/04
1
0
1
1
1

Login count
12/28/04
1
1
0
1
0

Right now my query reflects Name, last login date and the current day login count:

Select a.username, a.login_dt, CASE WHEN
a.isactive = 1 and a.login_dt = current_date() THEN 1
ELSE 0
END
from cms.dbo.usagelog a, cms.dbo.sys_user c
where a.userid = c.user_id and c.group2 IN ('DIS', 'MD', 'PC', 'SYS')
ORDER BY c.group2, a.login_dt, a.username

and this is the OUTPUT:
Name

Kit
Peter
Jeny
Katie
Patricia

Last LOGIN Date
Dec 28 2004 07:12AM
Dec 28 2004 09:30AM
Dec 27 2004 10:23AM
Dec 28 2004 10:38AM
Dec 27 2004 10:30AM

Login COUNT 12/28/04 (CURRENT DAY)
1
1
0
1
0


Can somebody please show me how i can do a loop or an iteration inside my query and get it to show the current day's data and all the data from the previous days (ex. 12/23, 12/24, 12/25, 12/26, 12/27) .

You could try using 'GROUP BY':

Code:

Select A.Username, A.Login_Dt, Count(*) As Login_Times 
  From Cms.Dbo.Usagelog A, Cms.Dbo.Sys_User C
 Where A.Userid = C.User_Id And C.Group2 In ('Dis', 'Md', 'Pc', 'Sys') 
 Group By A.Username, A.Login_Dt
 Order By C.Group2, A.Username, A.Login_Dt

LKBrwn_DBA, i don't think you can ORDER BY a column in a GROUP BY query if that column isn't in the SELECT list

True, I kind'a just copied over the ORDER BY ... should have looked more closely.