请试着重写你的句子，它有点难理解。我从你的问题中写出了下面的疑问句

DECLARE @ACTIVE_TOTALS INTEGER
DECLARE @DISCHARGES INTEGER

SET @ACTIVE_TOTALS = 
(
SELECT COUNT(*)
WHERE STATUS = 'ACTIVE'
)

SET @DISCHARGES
(
SELECT COUNT(*)
WHERE STATUS = 'CLOSED'
)

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, admission_date), 0) AS [year_month],
    @ACTIVE_TOTALS,
    @DISCHARGES,
    TEAM
FROM TABLE

你的问题并不完全清楚，但我猜你需要的是条件聚合。这里是一个完整的示例，其中包含了你的样本数据，它会生成你想要的输出。我猜你需要根据实际查询调整一些内容。

declare @Something table
(
    client_id int
    , admission_date date
    , discharge_date date
    , status varchar(10)
    , Team varchar(10)
)
insert @Something values
(12, '1/01/2023', '1/25/2023', 'closed', 'Team1')
, (5,    '1/22/2023', null, 'active', 'Team1')
, (17, '1/02/2023', null, 'active', 'Team1')

select [year] = datepart(year, admission_date)
    , [month] = datepart(month, admission_date)
    , [Run total of actives] = sum(case when status = 'active' then 1 end)
    , [closed account] = sum(case when status = 'closed' then 1 end)
    , Team
from @Something s
group by datepart(year, admission_date)
    , datepart(month, admission_date)
    , Team