Get usernames logged in on day by day basis from database

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 ti开发者_如何学Cmespan 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...

---

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.

---

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)