title: Backfilling SQL data using a date range
body: <p>I have log data and I'm trying to back-fill the data as much as possible to help improve analytics.</p>
  
  <p>The log data contains a SessionId, which is the SessionId created by the browser, the Name of the logged in user (if they are logged in) and a LogTime.</p>
  
  <p>I'm trying to get all the related sessions, sessions that are within 24 hours of each other, and get the first date of that group of sessions, the last date of that group of sessions and populate the first not null and not empty name into all the other name spaces.</p>
  
  <p>For instance, if I had the following data:</p>
  
  <pre><code>--Id    SessionId   Name        LogTime
  --1     1                       2018-01-01 00:00
  --2     1           LargeOne    2018-01-01 12:00
  --3     2           Two         2018-01-01 13:00
  --4     3           NULL        2018-01-02 00:00
  --5     3                       2018-01-03 00:00
  --6     1           One         2018-01-03 00:00
  --7     2                       2018-01-03 00:00
  --8     2           LargeTwo    2018-01-04 00:00
  --9     1                       2018-01-04 00:00
  </code></pre>
  
  <p>I would like to process the data as follows:</p>
  
  <pre><code>--Id    SessionId   Name        LogTime             StartTime           EndTime
  --1     1           LargeOne    2018-01-01 00:00    2018-01-01 00:00    2018-01-01 12:00
  --2     1           LargeOne    2018-01-01 12:00    2018-01-01 00:00    2018-01-01 12:00
  
  --3     2           Two         2018-01-01 13:00    2018-01-01 13:00    2018-01-01 13:00
  
  --4     3           NULL        2018-01-02 00:00    2018-01-02 00:00    2018-01-03 00:00
  --5     3           NULL        2018-01-03 00:00    2018-01-02 00:00    2018-01-03 00:00
  
  --6     1           One         2018-01-03 00:00    2018-01-03 00:00    2018-01-04 00:00
  
  --7     2           LargeTwo    2018-01-03 00:00    2018-01-03 00:00    2018-01-04 00:00
  --8     2           LargeTwo    2018-01-04 00:00    2018-01-03 00:00    2018-01-04 00:00
  
  --9     1           One         2018-01-04 00:00    2018-01-03 00:00    2018-01-04 00:00
  </code></pre>
  
  <p>Ids 1 and 2 are in the same session and in range (24 hours) of each other so they make one set, notice that the Id 1 doesn't have a name column but Id 2 does and because it's part of the same set, it back fills the name. Ids 6 and 9 are also in session 1 but is not in the 24 hour range of the first set so it makes a new set, Ids 6 and 9 are both in session 1 and even though new sessions appear between them, they are still the same session within range so they make a new set.</p>
  
  <p>I think that covers explaining the problem, now for my attempts at finding a solution. To find and backfill the Name, I tried to use:</p>
  
  <pre><code>SELECT  Id,SessionId,
          FIRST_VALUE(Name) OVER (PARTITION BY SessionId ORDER BY CASE WHEN Name IS NULL or Name='' then 0 ELSE 1 END DESC,Id) Name,
          LogTime
  FROM #RawData
  ORDER BY Id
  </code></pre>
  
  <p>This produces:</p>
  
  <pre><code>--Id    SessionId   Name        LogTime
  --1     1           LargeOne    2018-01-01 00:00
  --2     1           LargeOne    2018-01-01 12:00
  --3     2           Two         2018-01-01 13:00
  --4     3           NULL        2018-01-02 00:00
  --5     3           NULL        2018-01-03 00:00
  --6     1           LargeOne    2018-01-03 00:00
  --7     2           Two         2018-01-03 00:00
  --8     2           Two         2018-01-04 00:00
  --9     1           LargeOne    2018-01-04 00:00
  </code></pre>
  
  <p>This almost works but it doesn't take the date ranges into consideration.</p>
  
  <p>So I did a lot of digging as to how to get the groups based on the SessionId and date ranges and I came up with this:</p>
  
  <pre><code>;WITH ProcessTable1 AS
  (
    SELECT Id,SessionId,Name,LogTime,
      PreviousLogTimeInRange = CASE WHEN LAG(LogTime, 1) OVER (partition by SessionId ORDER BY LogTime) between  DATEADD(day, -1, LogTime) and LogTime
          THEN 0 ELSE 1 END,
      NextLogTimeInRange = CASE WHEN Lead(LogTime,1) OVER (partition by SessionId ORDER BY LogTime) between  LogTime and DATEADD(day, 1, LogTime)
          THEN 0 ELSE 1 END
    FROM #RawData
  ),
  ProcessTable2 AS 
  (
    SELECT Id, Name, SessionId, LogTime, PreviousLogTimeInRange, 
    NextLogTime = case when NextLogTimeInRange = 0 then LEAD(LogTime, 1) OVER (partition by SessionId ORDER BY LogTime) else LogTime end
    FROM ProcessTable1 WHERE 1 IN (PreviousLogTimeInRange, NextLogTimeInRange)
  )
  SELECT Id,SessionId,
  FIRST_VALUE(Name) OVER (PARTITION BY SessionId ORDER BY CASE WHEN Name IS NULL or Name = '' then 0 ELSE 1 END DESC, Id) Name,
  LogTime, NextLogTime
  
  FROM ProcessTable2 
  --WHERE PreviousLogTimeInRange = 1
  ORDER BY id;
  </code></pre>
  
  <p>This produces:</p>
  
  <pre><code>--Id    SessionId   Name        LogTime             NextLogTime
  --1     1           LargeOne    2018-01-01 00:00    2018-01-01 12:00
  --2     1           LargeOne    2018-01-01 12:00    2018-01-01 12:00
  --3     2           Two         2018-01-01 13:00    2018-01-01 13:00
  --4     3           NULL        2018-01-02 00:00    2018-01-03 00:00
  --5     3           NULL        2018-01-03 00:00    2018-01-03 00:00
  --6     1           LargeOne    2018-01-03 00:00    2018-01-04 00:00
  --7     2           Two         2018-01-03 00:00    2018-01-04 00:00
  --8     2           Two         2018-01-04 00:00    2018-01-04 00:00
  --9     1           LargeOne    2018-01-04 00:00    2018-01-04 00:00
  </code></pre>
  
  <p>So close, but I still need the StartTime and to be honest I'm not 100% sure this will always do what I want.</p>
  
  <p>The last query was in part created from the findings on
  <a href="https://stackoverflow.com/questions/15818604/sql-query-to-group-items-by-time-but-only-if-near-each-other">SQL Query to group items by time, but only if near each other?</a></p>
  
  <p>If anyone is willing to lend a hand here, I would be eternally grateful!</p>
  
  <p>--Edit--</p>
  
  <p>I've created some data to play with if anyone wants to give it a bash.</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#RawData') IS NOT NULL DROP TABLE #RawData
  GO
  
  Create Table #RawData
  (
  Id INT IDENTITY,
  SessionId INT NOT NULL,
  Name NVARCHAR(50) NULL,
  LogTime DATETIME NOT NULL
  )
  
  INSERT INTO #RawData(SessionId,Name,LogTime)
  VALUES
  (1, '',         '2018-01-01 00:00'),
  (1, 'LargeOne', '2018-01-01 12:00'),
  
  (2, 'Two',      '2018-01-01 13:00'),
  
  (3, NULL,       '2018-01-02 00:00'),
  (3, '',         '2018-01-03 00:00'),
  
  (1, 'One',      '2018-01-03 00:00'),
  
  (2, '',         '2018-01-03 00:00'),
  (2, 'LargeTwo', '2018-01-04 00:00'),
  
  (1, '',         '2018-01-04 00:00')
  
  SELECT * FROM #RawData
  </code></pre>