SQL Query

Question

I have a query that i created which searches all logs to see what users did on the website for the COMPUTER account.

SELECT u.ID, u.LastName, u.FirstName, u.Company, ua.AccountID, wsl.SessionBegan, wsl.ServerName, wal.Activity
FROM [User] u INNER JOIN
UserAccount ua ON u.ID = ua.UserID INNER JOIN
wwwSessionLog wsl ON u.ID = wsl.UserName INNER JOIN
wwwActivityLog wal ON wsl.SessionID = wal.SessionID
WHERE (ua.AccountID = 'COMPUTER') AND (wsl.ServerName = 'www.test.com') AND (wsl.SessionBegan > DATEADD(Month, - 6, GETDATE()))
ORDER BY ua.AccountID

What i would like to do is the following reformatting:

LAST Name | First Name | ACCOUNTID | Aug 06 | Sep 06| Oct 06| .... | Jan 06| Feb 06


The activity should be ordered by time, and there should be totals for category, so under computer there should be SOFTWARE, HARDWARE...and cound for each month how many accesses were requested for these categories, and a grand total for the entire 6 months.

Thank you in advance for your help

Answer 1

UT:

Are you using SQL Server 2005?  Also, what is the column name that will contain the categories "SOFTWARE, HARDWARE, etc"?  Is taht the "wal.Activity" column?

Answer 2

Yes, that is correct, wal.Activity contains details like "SOFTWARE, HARDWARE"

Answer 3

UT:

I have a column for "Activity" so that I can display activity variations within "AccountID" columns that contain the value "Computer".  I don't think I have this right, but with feedback maybe we can get it straightened out.

select LastName,
       FirstName,
       Activity,
       [608] as [Aug 06],
       [609] as [Sep 06],
       [610] as [Oct 06],
       [611] as [Nov 06],
       [612] as [Dec 06],
       [701] as [Jan 07],
       [702] as [Feb 07]
from ( SELECT u.LastName,
              u.FirstName,
              wal.Activity,
              100 * (datepart (yy, wsl.SessionBegan) % 100)
                + datepart (mm, wsl.SessionBegan)
              as period,
              count(*) as activityCount
         FROM [User] u
        INNER JOIN UserAccount ua
           ON u.ID = ua.UserID
        INNER JOIN wwwSessionLog wsl
           ON u.ID = wsl.UserName
        INNER JOIN wwwActivityLog wal
           ON wsl.SessionID = wal.SessionID
       WHERE (ua.AccountID = 'COMPUTER')
         AND (wsl.ServerName = 'www.test.com')
         AND (wsl.SessionBegan > DATEADD(Month, - 6, GETDATE()))
       group by u.lastName,
                u.firstName,
                100 * (datepart (yy, wsl.SessionBegan) % 100)
                   + datepart (mm, wsl.SessionBegan),
                wal.Activity
     ) a
pivot( sum(activityCount) for period
       in ( [608],[609],[610],[611],[612],[701],[702] )
     ) x

--   LastName   FirstName  Activity   Aug 06 Sep 06 Oct 06 Nov 06 Dec 06 Jan 07 Feb 07
--   ---------- ---------- ---------- ------ ------ ------ ------ ------ ------ -----------
--   Addams     Gomez      Hardware   1      NULL   1      1      NULL   1      1
--   Addams     Gomez      Software   NULL   NULL   NULL   1      2      NULL   1
--   Addams     Gomez      Training   NULL   2      1      NULL   NULL   1      NULL
--   Rubble     Barney     Hardware   NULL   NULL   NULL   1      NULL   NULL   NULL
--   Rubble     Barney     Training   NULL   NULL   NULL   1      NULL   NULL   NULL

 

Answer 4

Thank you, we are almost there :)