(SQL Server 2008) - SQL question regarding CTE and joins :-(

Question

How can I retrieve the following results?

user | date | login time | logoff time

I wish to print out every day in a given time period with matching login and logoff time which is written is table history. If there's a multiple login for same user and for same day then SQL should select minimal date for login and maximum date for logoff.

This is a user table:

CREATE TABLE [dbo].[User](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](25) NOT NULL,
[firstname] [nvarchar](50) NOT NULL,
[lastname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
[id] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This is a history table:

CREATE TABLE [dbo].[History](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[login_time] [datetime] NOT NULL,
[logoff_time] [datetime] NOT NULL,
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
( 
[id] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So far I can print out this, but for only one user:

date | login time | logoff time

This is a SQL (using CTE on SQL Server 2008):

with CTE(d) as 
(
select d = convert(datetime, '20090801')
union all 
select d = d + 1 from CTE where d < '20090831'
)

select 
d as date,
(
 select min(h.login_time)
 from history h
 where 
  h.user_id = 1 and
  h.login_time >= d and
  h.login_time < dateadd(d, 1, d)
) as login_time,
(
 select max(h.logoff_time)
 from history h
 where 
  h.user_id = 1 and
  h.logoff_time >= d and
  h.logoff_time < dateadd(d, 1, d)
) as logoff_time
from CTE
option (maxrecursion 370)

I should somehow include this, but I'm stuck :-(

select *
from [user] u
where 
exists (
 select *
 from history h
 where 
  h.user_id = u.id and
  h.login_time >= '20090801' and
  h.login_time < '20090901'
)

Here's the example of data in history table:

id  user_id login_time         logoff_time
1   1 2009-08-20 06:00:01.000 2009-08-20 22:07:58.230
2   1 2009-08-20 22:10:15.137 2009-08-20 23:15:15.000
3   2 2009-08-20 22:08:20.103 2009-08-20 22:08:20.103
4   2 2009-08-20 22:08:23.340 2009-08-20 22:08:23.340
5   2 2009-08-21 14:30:30.120 2009-08-21 19:20:30.000

Desired output would be:

user  date       login_time               logoff_time
john  2009-08-01 NULL                     NULL
john  2009-08-02 NULL                     NULL
...
john  2009-08-08 2009-08-20 06:00:01.000  2009-08-20 23:15:15.000
...
john  2009-08-31 NULL                     NULL
merry 2009-08-01 NULL                     NULL
merry 2009-08-02 NULL                     NULL
...
merry 2009-08-20 2009-08-20 22:08:20.103  2009-08-20 22:08:23.340
merry 2009-08-21 2009-08-21 14:30:30.120  2009-08-21 19:20:30.000
...
merry 2009-08-31 NULL                     NULL

Answer 1

I've solved the problem with the help of a friend. This is a solution:

 with dates(a_date) as (
select a_date = convert(datetime, '20090801')
union all 
select a_date = a_date + 1 from dates where a_date < '20090831'  
)

select
  u.username         as username,
  d.a_date           as a_date,
  min(h.login_time)  as login_time,
  max(h.logoff_time) as logoff_time
from
  dates d
  cross join dbo.[User] u
  left join dbo.History h ON 
u.id = h.user_id and
h.login_time >= d.a_date and
h.login_time < dateadd(d, 1, d.a_date)  
group by
  u.username,
  d.a_date
order by
  u.username

Also the second one, little bit longer:

with CTE(d) as 
(
select d = convert(datetime, '20090801')
union all 
select d = d + 1 from CTE where d < '20090831'
)

select 
u.username,
d as date,
(
 select min(h.login_time)
 from history h
 where 
  h.user_id = u.id and
  h.login_time >= d and
  h.login_time < dateadd(d, 1, d)
) as login_time,
(
 select max(h.logoff_time)
 from history h
 where 
  h.user_id = u.id and
  h.logoff_time >= d and
  h.logoff_time < dateadd(d, 1, d)
) as logoff_time
from CTE
cross join [User] u
order by username, date