How do I write a query that shows year, month, running total for actives, distinct closed numbers, t-CodePudding

client_id	admission_date	discharge_date	status	Team
12	1/01/2023	1/25/2023	closed	Team1
5	1/22/2023	null	active	Team1
17	1/02/2023	null	active	Team1

The output I am looking for is:

year	month	Run total of actives	closed count	Team
2023	January	2	1	team 1

I've tried joining on dates, tried a union all no luck.

CodePudding user response：

Please try to rewrite your sentence, it's a tad hard to understand. I've written the following query from your given question

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

CodePudding user response：

Your question is not totally clear but I suspect what you need here is conditional aggregation. Here is a fully working example with your sample data that produces what you state you want as output. My guess is you will need to adjust some stuff here for your actual query.

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