title: (SQL Server 2008) - SQL question regarding CTE and joins :-(
body: <p>How can I retrieve the following results?</p>
  
  <pre><code>user | date | login time | logoff time
  </code></pre>
  
  <p>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. </p>
  
  <p>This is a user table:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>This is a history table:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>So far I can print out this, but for only one user:</p>
  
  <pre><code>date | login time | logoff time
  </code></pre>
  
  <p>This is a SQL (using CTE on SQL Server 2008):</p>
  
  <pre><code>with CTE(d) as 
  (
  select d = convert(datetime, '20090801')
  union all 
  select d = d + 1 from CTE where d &lt; '20090831'
  )
  
  select 
  d as date,
  (
      select min(h.login_time)
      from history h
      where 
          h.user_id = 1 and
          h.login_time &gt;= d and
          h.login_time &lt; dateadd(d, 1, d)
  ) as login_time,
  (
      select max(h.logoff_time)
      from history h
      where 
          h.user_id = 1 and
          h.logoff_time &gt;= d and
          h.logoff_time &lt; dateadd(d, 1, d)
  ) as logoff_time
  from CTE
  option (maxrecursion 370)
  </code></pre>
  
  <p>I should somehow include this, but I'm stuck :-(</p>
  
  <pre><code>select *
  from [user] u
  where 
  exists (
      select *
      from history h
      where 
          h.user_id = u.id and
          h.login_time &gt;= '20090801' and
          h.login_time &lt; '20090901'
  )
  </code></pre>
  
  <p>Here's the example of data in history table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Desired output would be:</p>
  
  <pre><code>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
  </code></pre>