By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mydata
(date datetime,
thething varchar(4));
INSERT INTO mydata
(date,
thething)
VALUES ('2016-03-09 08:17:29.867',
'Frob'),
('2016-03-09 08:18:33.327',
'Frob'),
('2016-03-09 14:32:01.240',
NULL),
('2016-10-21 19:53:49.983',
NULL),
('2016-11-12 03:25:21.753',
'Frob'),
('2016-11-24 07:43:24.483',
NULL),
('2016-11-28 16:06:23.090',
'Frob'),
('2016-11-28 16:09:07.200',
'Frob'),
('2016-12-10 11:21:55.807',
'Frob');
9 rows affected
SELECT date,
thething,
rank() OVER (ORDER BY date)
-
sum(CASE
WHEN thething IS NULL THEN
1
ELSE
0
END) OVER (ORDER BY date) desiredtotal
FROM mydata;
| date | thething | desiredtotal |
|---|---|---|
| 09/03/2016 08:17:29 | Frob | 1 |
| 09/03/2016 08:18:33 | Frob | 2 |
| 09/03/2016 14:32:01 | null | 2 |
| 21/10/2016 19:53:49 | null | 2 |
| 12/11/2016 03:25:21 | Frob | 3 |
| 24/11/2016 07:43:24 | null | 3 |
| 28/11/2016 16:06:23 | Frob | 4 |
| 28/11/2016 16:09:07 | Frob | 5 |
| 10/12/2016 11:21:55 | Frob | 6 |