How can you generate (in MS SQL) timeseries data where each timestamp's data value, if it does not exist in the table, will be based on the value before that timestamp? 

For example, given the following `data_table` -- actual values in the `date` column would be integers (unix timestamp) but showing here the human-readable date,

date (unix timestamp)|value|
----|-----|
01/01/2022 01:00|10
01/01/2022 04:00|12
01/01/2022 06:00|11

Requesting hourly data from 01/01/2022 00:00 to 08:00 should return

date (unix timestamp)|value|
----|-----|
01/01/2022 01:00|10
01/01/2022 02:00|10
01/01/2022 03:00|10
01/01/2022 04:00|12
01/01/2022 05:00|12
01/01/2022 06:00|11
01/01/2022 07:00|11

We're limited to MS SQL < 16 so we can't use [generate_series](https://docs.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16). Also, timeseries frequency would be hourly (3600 seconds), maximum. 

The best attempt (performance-wise) I did is to use a tally table (containing millions of rows with `N` column containing an incrementing value starting from 1) to generate timestamps. The SQL performs a `SELECT` query for each timestamp.

```sql
            DECLARE @DateStart int;
            DECLARE @DateEnd int;
            SET @DateStart = {start_date};
            SET @DateEnd = {end_date};
     
             SELECT @DateStart + tally.N * {frequency} as datetime, ISNULL((
                    -- Select the proper value for this timestamp 
                    SELECT TOP (1) [val]
                    FROM data_table
                    WHERE datetime <= @DateStart + tally.N * {frequency}
                    ORDER BY datetime DESC
                ), 0) as val, cast(dateadd(second, @DateStart + tally.N * {frequency}, '19700101') as DATETIME) as calendar_date
             FROM dbo.tally
             WHERE tally.N * {frequency} + @DateStart < @DateEnd
```

Here's a [fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=ded5dd00422a9a34294766eb9aa2c230).

What other SQL solution is there? Is there an  alternative to this SQL solution that would be able to support timestamps that are not in the proper frequency?

E.g., if my data are like so,

```
-- (1640998801, 10), -- JAN 01 2022 01:00:01 GMT
-- (1641009613, 12); -- JAN 01 2022 04:00:13 GMT
```

With the current query, it will incorrectly output, 

>   datetime | val | calendar_date          
> ---------: | --: | :----------------------
> 1640998800 |  10 | 2022-01-01 01:00:00.000
> 1641002400 |  10 | 2022-01-01 02:00:00.000
> 1641006000 |  10 | 2022-01-01 03:00:00.000
> 1641009600 |  10 | 2022-01-01 04:00:00.000
> 1641013200 |  12 | 2022-01-01 05:00:00.000

Since timestamp `2022-01-01 04:00:00.000` should have  the value of 12.