By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values
(convert(date, '2020-01-10'), 1 ),
(convert(date, '2020-01-15'), 1 ),
(convert(date, '2020-01-17'), null ),
(convert(date, '2020-02-17'), 1 ),
(convert(date, '2020-02-21'), null ),
(convert(date, '2020-04-04'), null ),
(convert(date, '2020-04-18'), null ),
(convert(date, '2020-04-19'), 1 )
) v(dateinserted, isregistered)
8 rows affected
select year(dateinserted) as [Year],
datename(month, dateinserted) as [Month],
count(isregistered) as registered_in_month,
sum(count(isregistered)) over (order by min(dateinserted)) as registered_up_to_month,
sum(count(*)) over () - sum(count(isregistered)) over (order by min(dateinserted)) as not_yet_registered
from t
group by year(dateinserted), datename(month, dateinserted)
order by year(dateinserted), month(min(dateinserted));
| Year | Month | registered_in_month | registered_up_to_month | not_yet_registered |
|---|---|---|---|---|
| 2020 | January | 2 | 2 | 6 |
| 2020 | February | 1 | 3 | 5 |
| 2020 | April | 1 | 4 | 4 |
Warning: Null value is eliminated by an aggregate or other SET operation.