By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
CaseID INTEGER NOT NULL
,UserID VARCHAR(2) NOT NULL
,OpenedOn DATE NOT NULL
,ClosedDate DATE
,Value INTEGER NOT NULL
);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (1,'U1','2020-01-04','2020-02-05',500);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (2,'U1','2020-01-08',NULL,120);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (3,'U3','2020-01-16',NULL,350);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (4,'U2','2020-01-21','2020-01-25',100);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (5,'U1','2020-01-25','2020-04-08',150);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (6,'U2','2020-02-05',NULL,790);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (7,'U4','2020-02-14','2020-03-18',190);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (8,'U4','2020-02-19',NULL,490);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (9,'U1','2020-02-21','2020-02-27',140);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (10,'U2','2020-03-07','2020-03-17',640);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (11,'U1','2020-03-17','2020-05-12',420);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (12,'U3','2020-03-22',NULL,810);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (13,'U2','2020-04-05','2020-05-19',320);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (14,'U1','2020-04-18',NULL,180);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (15,'U4','2020-04-16','2020-05-22',210);
INSERT INTO mytable(CaseID,UserID,OpenedOn,ClosedDate,Value) VALUES (16,'U3','2020-05-19',NULL,230);
16 rows affected
select
u.userID,
d.dt year_month,
sum(coalesce(sum(no_open - no_closed), 0))
over(partition by u.userID order by d.dt) no_cases_still_open,
sum(coalesce(sum(value_open - value_closed), 0))
over(partition by u.userID order by d.dt) value_open,
coalesce(sum(no_closed), 0) no_closed,
coalesce(sum(value_closed), 0) value_closed
from
(select distinct datefromparts(year(openedOn), month(openedOn), 1) dt from mytable) d
cross join (select distinct userID from mytable) u
left join (
select userID, openedOn dt, 1 no_open, value value_open, 0 no_closed, 0 value_closed from mytable
union all
select userID, closedDate dt, 0, 0, 1, value from mytable
) t
on t.userID = u.userID
and t.dt >= d.dt and t.dt < dateadd(month, 1, d.dt)
group by u.userID, d.dt
| userID | year_month | no_cases_still_open | value_open | no_closed | value_closed |
|---|---|---|---|---|---|
| U1 | 2020-01-01 | 3 | 770 | 0 | 0 |
| U1 | 2020-02-01 | 2 | 270 | 2 | 640 |
| U1 | 2020-03-01 | 3 | 690 | 0 | 0 |
| U1 | 2020-04-01 | 3 | 720 | 1 | 150 |
| U1 | 2020-05-01 | 2 | 300 | 1 | 420 |
| U2 | 2020-01-01 | 0 | 0 | 1 | 100 |
| U2 | 2020-02-01 | 1 | 790 | 0 | 0 |
| U2 | 2020-03-01 | 1 | 790 | 1 | 640 |
| U2 | 2020-04-01 | 2 | 1110 | 0 | 0 |
| U2 | 2020-05-01 | 1 | 790 | 1 | 320 |
| U3 | 2020-01-01 | 1 | 350 | 0 | 0 |
| U3 | 2020-02-01 | 1 | 350 | 0 | 0 |
| U3 | 2020-03-01 | 2 | 1160 | 0 | 0 |
| U3 | 2020-04-01 | 2 | 1160 | 0 | 0 |
| U3 | 2020-05-01 | 3 | 1390 | 0 | 0 |
| U4 | 2020-01-01 | 0 | 0 | 0 | 0 |
| U4 | 2020-02-01 | 2 | 680 | 0 | 0 |
| U4 | 2020-03-01 | 1 | 490 | 1 | 190 |
| U4 | 2020-04-01 | 2 | 700 | 0 | 0 |
| U4 | 2020-05-01 | 1 | 490 | 1 | 210 |
Warning: Null value is eliminated by an aggregate or other SET operation.