I was asked to put together a query that would allow management to know how many users used one of our internal application per week. Since we deploy our apps via ClickOnce my first stop was to view our WebTrends reports. While it contained lots of useful and interesting data it showed the number of hits and visits but the numbers seemed to be too high and I made a mental note to research their definition of a visit.
The application has a SQL Server 2005 LoginHistory table that gets populated each time the user launches the application. It records their LoginId char(7) and LoginDate (datetime). There were thousands of rows in this table and with a couple of quick queries I was able to see that the users were launching the application several times a day practically every day.
I need to determine how many users used the application per week not the number of connections per day. So the first thing to do was to figure how to determine the first day of the week for the given LoginDate:
LoginDate - (DATEPART(DW, LoginDate) - 1)
LoginDate is a datetime and all I was interested in was the date part so I converted the date into a string in the "mm/dd/yyyy" format:
CONVERT( varchar,LoginDate - (DATEPART(DW, LoginDate) - 1 ), 101) as 'FirstOfWeek'
Next I needed to return one row for each user along with a weekly login count:
SELECT DISTINCT
CONVERT(varchar,LoginDate - (DATEPART(DW, LoginDate) - 1),101) as 'FirstOfWeek'
, LoginId
, 1 as WeeklyLoginAttempt
FROM LoginHistory
Lastly, I needed to sum up the attempts so I made my SELECT a sub-query, added a sum, group by and order by statements.
SELECT
P1.FirstOfWeek,
SUM(P1.WeeklyLoginAttempt) as WeeklyLogins
FROM (
SELECT DISTINCT
CONVERT(varchar,LoginDate - (DATEPART(DW, LoginDate) - 1),101) as 'FirstOfWeek'
, LoginId
, 1 as WeeklyLoginAttempt
FROM LoginHistory
) as P1
GROUP BY P1.FirstOfWeek
ORDER BY CONVERT(datetime,FirstOfWeek)
This resulted in a nice result set that took the raw log of login attempts and converted into a user login by week report that made the boss happy which is always a good thing!