By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT * INTO #data FROM (VALUES
(CAST('2021-08-15' AS DATE), 'Bulgaria', 1081636),
('2021-08-16', 'Bulgaria', 1088001),
('2021-08-17', 'Bulgaria', 1084693),
('2021-08-18', 'Bulgaria', NULL),
('2021-08-19', 'Bulgaria', NULL),
('2021-08-20', 'Bulgaria', 1189066)) x(dt, country, cnt);
6 rows affected
SELECT dt
, country
, cnt
, ISNULL(source.cnt, excludeNulls.LastCnt)
FROM #data source
OUTER APPLY ( SELECT TOP 1 cnt as LastCnt
FROM #data
WHERE dt < source.dt
AND cnt IS NOT NULL
ORDER BY dt desc) ExcludeNulls
ORDER BY dt
| dt | country | cnt | (No column name) |
|---|---|---|---|
| 2021-08-15 | Bulgaria | 1081636 | 1081636 |
| 2021-08-16 | Bulgaria | 1088001 | 1088001 |
| 2021-08-17 | Bulgaria | 1084693 | 1084693 |
| 2021-08-18 | Bulgaria | null | 1084693 |
| 2021-08-19 | Bulgaria | null | 1084693 |
| 2021-08-20 | Bulgaria | 1189066 | 1189066 |
CREATE TABLE AnimalEvent
(
Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Animal VARCHAR(10),
DateAdded DATE
)
INSERT INTO AnimalEvent (Animal, DateAdded)
VALUES
('Cat','1/1/2012'),
('Dog','1/2/2012'),
('Fish','1/3/2012'),
(NULL,'1/4/2012'),
('Goat','1/5/2012'),
(NULL,'1/6/2012'),
('Sheep','1/7/2012');
7 rows affected
SELECT A.Animal, A.DateAdded, ISNULL(A.Animal,B.Animal) V2
FROM AnimalEvent A
OUTER APPLY ( SELECT TOP 1 *
FROM AnimalEvent
WHERE DateAdded < A.DateAdded
AND Animal IS NOT NULL
ORDER BY DateAdded desc) B
order by DateAdded
| Animal | DateAdded | V2 |
|---|---|---|
| Cat | 2012-01-01 | Cat |
| Dog | 2012-01-02 | Dog |
| Fish | 2012-01-03 | Fish |
| null | 2012-01-04 | Fish |
| Goat | 2012-01-05 | Goat |
| null | 2012-01-06 | Goat |
| Sheep | 2012-01-07 | Sheep |