Get usernames logged in on day by day basis from database

Question

I have this database structure

username   logged_in            logged_out
------------------------------------------
user1      2011-04-03 19:32:01  2011-04-05 03:41:34
user2      2011-04-01 10:33:42  2011-05-01 23:15:23

What I need is a list of all users that were logged in on a specific day, so e.g.

day           logged users
2011-04-01    user2
2011-04-02    user2
2011-04-03    user2
2011-04-03    user1
2011-04-04    user2
2011-04-04    user1
2011-04-05    user2
2011-04-05    user1
...
2011-05-01    user2

I'm currently trying to get this done with a single SQL query, but I don't really have a clue how to get the timespan of all days that are logged in the table and how to connect them to the users logged in.

My biggest problem is how to create the "virtual" table of all days spanned in the database...

Do you -really- want to create a single field concatenating the different logged in users? — MatBailie, Jun 22 '11 at 14:45
possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column). Also: http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query At least, it was until the OP edited the question after I supplied the duplicate info. — OMG Ponies, Jun 22 '11 at 14:47
It doesn't have to be concatenated. When every user has a row of its own and some days (like `2011-04-03`) are listed multiple times it's still better than nothing. @OMG Ponies: That's why I edited it. The problem isn't concatenating, but creating the table as such. Thanks for your hint though! — F.P, Jun 22 '11 at 14:47

MatBailie · Accepted Answer · 2011-06-22T16:44:19.767

3

DECLARE @from_date DATETIME, @to_date DATETIME

-- populate @from_date and @to_date based on reporting needs
-- possibly using MIN() and MAX() on your logged_in and logged_out fields

DECLARE
  @limit INT
SELECT
  @limit = DATEDIFF(DAY, @from_date, @to_date)
;
WITH
  calendar AS
(
  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @from_date), 0) AS date, 1 AS inc_a, 2 AS inc_b
UNION ALL
  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @from_date) + inc_a, 0), inc_a + inc_a + 1, inc_a + inc_a + 2 FROM calendar WHERE inc_a <= @limit
UNION ALL
  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @from_date) + inc_b, 0), inc_b + inc_b + 1, inc_b + inc_b + 2 FROM calendar WHERE inc_b <= @limit
)

SELECT
  calendar.date,
  your_table.username
FROM
  your_table
INNER JOIN
  calendar
    ON  calendar.date >= DATEADD(DAY, DATEDIFF(DAY, 0, your_table.logged_id), 0)
    AND calendar.date <  your_table.logged_out

EDIT

Binary growth in CTE instead of Linear. 2^100 dates should give a reasonable range.

edited Jun 22 '11 at 16:44

answered Jun 22 '11 at 15:06

MatBailie

83,401
18
103
137

It goes into an infinite loop at the second day, but I think you are in the right track – Adriano Carneiro Jun 22 '11 at 15:18
The second part of the union must be `SELECT DATEADD(DAY, 1, date) FROM calendar WHERE date < @to_date ` – Adriano Carneiro Jun 22 '11 at 15:20
Also, this solution cannot have as many days as you want, since there's a limit for recursion (it says 100 recursion for me here on SQL Server 2008). Thus, for me, it says I can only have 100 days. Maybe it's not that little, but it is a limit. – Adriano Carneiro Jun 22 '11 at 15:21
Solution now allows for (2^100 + 1) dates in the CTE derived calendar table. – MatBailie Jun 22 '11 at 16:45
+1 Sweet. Pure gold. Wish could upvote twice. Whoever is going to use this, please remember to order by date, because there's now way to guarantee dates in CTE to be ordered. On the contrary, since it's recursively called twice, it *should not* be ordered. – Adriano Carneiro Jun 22 '11 at 18:04
Oh, I'm sure 2^100 dates should give an unreasonable range just as well. (+1) – Andriy M Jun 22 '11 at 22:01
I get an error saying "Incorrect syntax near `WITH`", which I don't really understand. I checked the syntax and it seems correct to me oO? – F.P Jun 24 '11 at 08:17
You definitely have the `;` before the `WITH` statement? (If I set the @from and @to variables, then do `SELECT * FROM calendar ORDER BY date`, it all works fine for me in SQL 2005.) – MatBailie Jun 24 '11 at 14:32

Joel Martinez · Answer 2 · 2011-06-22T15:59:13.907

0

For any given day, you can easily figure out what users were logged in on that day

declare @thedate datetime
set @thedate = '2011-04-01'

select * from userlog where logged_in between @thedate and @thedate+1

Do you specifically need the report in that format?

edit: in response to updated question

select 
   username,
   DATEADD(DAY, DATEDIFF(DAY, 0, logged_in), 0)
from userlog
group by 
   username, 
   DATEADD(DAY, DATEDIFF(DAY, 0, logged_in), 0)

edited Jun 22 '11 at 15:59

answered Jun 22 '11 at 14:48

Joel Martinez

46,929
26
130
185

I edited the format - and yes, it would be the best. Thanks for the query, but I know this isn't the hard part - I just cant figure out how to iterate over all days that are present in the databse and create a table from that... – F.P Jun 22 '11 at 14:50
This does not answer OPs question. You should rework it to avoid downvotes. – Adriano Carneiro Jun 22 '11 at 14:55
updated answer with an updated select statement that will output as per the output he specified when he updated the question – Joel Martinez Jun 22 '11 at 15:59
If a login-logout spans several days, this still only gives one records, not one for each day. – MatBailie Jun 22 '11 at 16:32

Get usernames logged in on day by day basis from database

2 Answers2

Linked