I have log data and I'm trying to back-fill the data as much as possible to help improve analytics.

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.

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.

For instance, if I had the following data:

```
--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

```

I would like to process the data as follows:

```
--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

```

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.

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:

```
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

```

This produces:

```
--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

```

This almost works but it doesn't take the date ranges into consideration.

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:

```
;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;

```

This produces:

```
--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

```

So close, but I still need the StartTime and to be honest I'm not 100% sure this will always do what I want.

The last query was in part created from the findings on
[SQL Query to group items by time, but only if near each other?](https://stackoverflow.com/questions/15818604/sql-query-to-group-items-by-time-but-only-if-near-each-other)

If anyone is willing to lend a hand here, I would be eternally grateful!

--Edit--

I've created some data to play with if anyone wants to give it a bash.

```
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

```
['https://Stackoverflow.com/questions/49191902', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1070816/']
Let's start with this:

> 
> But the problem is, the parent **div.wrapper** does **not expand** to the
>  **width** of its contents – its yellow background only extends as far as
>  the width of the viewport.
> 
> 
> 

By default a div is a block element and a block element takes up the **whole width** of it's parent container so your wrapper in this case has the width of the body which is the width of the screen. In addition to this we are facing an **overflow** as the child content width is bigger than the parent width and by default:

> 
> Content is not clipped and may be rendered outside the padding box[ref](https://developer.mozilla.org/en-US/docs/Web/CSS/overflow)
> 
> 
> 

This explain why the background doesn't cover the `h1` as this one is **rendred outside**.

To change this behavior we have two solutions:

1. We change the behavior of [overflow](https://developer.mozilla.org/en-US/docs/Web/CSS/overflow) by specifing a value different from `visible` (the default one). By doing this you will also notice some changes to margin because you are also facing a **margin collapsing** (margin of `h1` and `p` are collpasing with the margin of `div.wrapper`).

```css
body {
  margin: 0;
}

.wrapper {
  background: yellow;
  margin:10px 0;
}

h1 {
  width: 2000px;
  border: 2px solid red;
}
```

```html
<div class="wrapper" style="overflow: auto;">
  <h1>Hello</h1>
  <p>More content</p>
</div>
<div class="wrapper" style="overflow: hidden;">
  <h1>Hello</h1>
  <p>More content</p>
</div>
<div class="wrapper" style="overflow: scroll;">
  <h1>Hello</h1>
  <p>More content</p>
</div>
<p>More content outside wrapper</p>
```

2. We change the display property of the element to something else than block. We can for example use `inline-block` or `inline-flex` and in this case the wrapper will fit the content of its element and he will **overflow** the body

```css
body {
  margin: 0;
}

.wrapper {
  background: yellow;
  display: inline-block;
}

h1 {
  width: 2000px;
  border: 2px solid red;
}
```

```html
<div class="wrapper">
  <h1>Hello</h1>
  <p>More content</p>
</div>
<p>More content outside wrapper</p>
```

---

Concerning this:

> 
> Why does setting body { position: absolute } fix the problem?
> 
> 
> 

We all know what `position:absolute` means but the intresting part is this one:

> 
> Most of the time, absolutely positioned elements that have height and
>  width **set to auto** are sized so **as to fit their contents**. However,
>  non-replaced, absolutely positioned elements can be made to fill the
>  available vertical space by specifying both top and bottom and leaving
>  height unspecified (that is, auto). They can likewise be made to fill
>  the available horizontal space by specifying both left and right and
>  leaving width as auto. [ref](https://developer.mozilla.org/en-US/docs/Web/CSS/position)
> 
> 
>
You haven't told the div how to handle the overflow caused by the width of the inner element. Add `overflow: auto`.

```css
body { margin: 0; }

.wrapper { background: yellow; overflow: auto; }

h1 {
  width: 2000px;
  border: 2px solid red;
}
```

```html
<!doctype html>
<html>
  <body>
    <div class="wrapper">
      <h1>Hello</h1>

      <p>More content</p>
    </div>

    <p>More content outside wrapper</p>
  </body>
</html>
```

I do not believe that there is an obvious reason why adding `position: absolute` to the body *fixes* this. It does take body out of the document flow, but body is the container for all the content. So I would describe it as a quirk. 

We could describe body as being the initial constraint for the width of the content, the .wrapper. Being absolute removes this constraint. Actually, it likely remove the width constraint for any further elements on the page, so they will probably all expand to contain any inner content.