Condiitonal running total for SSRS - SQL Server

Question

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

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.

What version of SQL Server are you using? Could you also please add a create table script of the tables you are using as well as some dummy data and your desired output to your answer to assist us in helping you. — iamdave

Mikhail Lobanov Mikhail Lobanov · Accepted Answer · 2017-05-31T14:59:21

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

Condiitonal running total for SSRS - SQL Server

2 Answers