By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1 (
ClientID int NOT NULL,
Service varchar(25) NOT NULL,
Status varchar(25) NOT NULL,
CloseDate date,
);
insert into t1 values
(1,'A','Closed','7-1-2020'),
(1,'A','Open',null),
(1,'A','Open',null),
(2,'A','Open',null),
(3,'A','Closed','7-2-2020')
5 rows affected
SELECT ClientID,
Service,
Status,
CloseDate,
ISNULL((SELECT TOP 1 COUNT(*) OVER(PARTITION BY clientid ORDER BY clientid) FROM t1 WHERE t1.ClientID = t.ClientID and t1.CloseDate IS NULL),0)
FROM t1 t;
| ClientID | Service | Status | CloseDate | (No column name) |
|---|---|---|---|---|
| 1 | A | Closed | 2020-07-01 | 2 |
| 1 | A | Open | null | 2 |
| 1 | A | Open | null | 2 |
| 2 | A | Open | null | 1 |
| 3 | A | Closed | 2020-07-02 | 0 |