title: Find records updated within hour timeframe from creation of new record
body: <p>Let's say I have records that are created for every entry a user creates. We will call these documents. They have a <strong>CreatedWhen</strong> column.</p>
  
  <p>Let's also state that documents can be updated changing their timestamp. A column known as <strong>UpdatedOn</strong>.</p>
  
  <p>The <strong>UpdatedOn</strong> column has an initial timestamp that is the same as CreatedOn for new document creation, unless they saved it before exiting.</p>
  
  <p>Now, someone wants to know what documents were updated 2 hours prior to the creation of a new document.</p>
  
  <p>Here was my attempt, and I can't figure out why it's not working.</p>
  
  <p>EDIT: The times I get back are not correct.</p>
  
  <pre><code>SELECT
      CASE WHEN (d2.UpdatedOn IS NOT NULL)
           THEN ROW_NUMBER() OVER (partition by d2.DocumentID, d2.DocumentName, d2.CreatedWhen --sequence grouping
                                       order by d2.UpdatedOn desc) --get earliest dates
           ELSE 0
      END as SeqNum,
      d.DocumentName,
      d.CreatedWhen
      d2.DocumentName as [DocUpdatedWithin2HR],
      d2.UpdatedOn
  Into #temp
  FROM Documents d
  JOIN Documents d2 ON d.DocumentID = d2.DocumentID
  WHERE d2.UpdatedOn BETWEEN DATEADD(Hour,-2,d.CreatedWhen) --2 hours prior, 1 hour window
                        AND DATEADD(Second,-1,(DATEADD(Hour,-1,d.CreatedWhen))) --1 second shy of an hour
  
  select distinct
      DocumentName,
      CreatedWhen,
      DocUpdatedWithin2HR, --xml path this column later
      UpdatedOn
  from #temp where rn = 1 order by 1,3
  </code></pre>