CODEWITHSUNDEEP
sqlsql-serverreporting-services
Zi0n1
Zi0n1

Reputation: 594

Condiitonal running total for SSRS - SQL Server

I currently have a dataset that looks similar to the below image:

enter image description here

Essentially there are cases that get opened and closed throughout the year. I was asked to make a report (really will be a bar char in the end) that summarizes the total count of cases (count of unique subscriberID's) by month and by case type.

The catch though that i am having trouble with is that they want it as a running total. So if the case was created in January of 2017 and is still open in March of 2017, they want it to still count in March of 2017. Only the month AFTER the case closes do they want it to no longer count towards a month's total.

So essentially if there were 10 open type 2 cases that happened in December of 2016 and they did not get closed in December, and no new open cases in january 2017, then January 2017 would show 10 open type 2 cases. If those same 10 cases did not get closed in January then they would show up in February section as well as adding any newly opened type 2 cases from February.

If this doesnt sound super confusing and someone has worked with something like this for, any help at all would be super appreciative. My group by knowledge is failing me pretty hard right now.

Upvotes: 0

Views: 45

Answers (2)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

DECLARE @Test TABLE (
    CaseOpenYearMonth   VARCHAR(255),
    CaseType            INT,
    CaseStatus          VARCHAR(255),
    CaseCloseDate       DATE
)
INSERT INTO @Test VALUES
('2017-02', 1, 'Open', NULL),
('2016-12', 1, 'Open', NULL),
('2013-05', 5, 'Closed', '2013-10-22'),
('2017-02', 1, 'Open', NULL),
('2017-04', 1, 'Open', NULL),
('2017-01', 1, 'Open', NULL),
('2013-05', 2, 'Closed', '2013-07-08'),
('2013-10', 0, 'Closed', '2013-10-16'),
('2013-10', 2, 'Closed', '2014-02-06'),
('2016-11', 2, 'Open', NULL)


;WITH Preprocessed AS(
    SELECT
        YearMonth   = CaseOpenYearMonth,
        CaseType    = CaseType, 
        Opened      = 1,
        Closed      = 0
    FROM @Test YT

    UNION ALL 

    SELECT
        YearMonth   = FORMAT(CaseCloseDate, 'yyyy-MM'),
        CaseType    = CaseType,  
        Opened      = 0,
        Closed      = 1
    FROM @Test YT
    WHERE CaseCloseDate IS NOT NULL
), GroupedData AS (
    SELECT 
        YearMonth   = YearMonth,
        CaseType    = CaseType,
        Opened      = SUM(Opened),
        Closed      = SUM(Closed)
    FROM Preprocessed
    GROUP BY YearMonth, CaseType
)
SELECT 
        YearMonth   = YearMonth,
        CaseType    = CaseType,
        Opened      = Opened,
        Closed      = Closed,
        Active      = SUM(Opened) OVER (PARTITION BY CaseType ORDER BY YearMonth ROWS UNBOUNDED PRECEDING) 
                        - ISNULL(SUM (Closed) OVER (PARTITION BY CaseType ORDER BY YearMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
FROM GroupedData
ORDER BY YearMonth

this will not include month if case count is not changed if you want to include it, you should add rows with zero (in Opened and Closed) in Preprocessed table

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

This isn't really a "running total". It is much simpler.

Just SUM a CASE expression that returns 1 for each row that opened before the current month and NOT closed BEFORE the current month. ELSE 0.

Upvotes: 0

Related Questions