Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: can somebody please help me with my query?

    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) .

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    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
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by r937
    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.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •