How to calculate running total column which has more than one date where criteria

Question

I'm using SQL Server 2012 and have a query I need to convert to a running total per month.

The current query expect a input parameter of date and outputs all the groups with the total per group for the input month date

DECLARE @InputDate DATETIME
SET MonthEndDate = '2016/07/31'

SELECT P.GroupID,Count(P.PersonID) as GroupTotal
from PersonData P
WHERE 
     P.StartedDate IS NOT NULL
AND P.StartedDate < @InputDate
AND (
     P.OutcomeDate > @InputDate
     OR P.OutcomeDate IS NULL 
)
GROUP BY  P.GroupID

Using the same query logic I now need to repopulate historic data per month so I need to use a Running Total for all months e.g. (2016/01/01,2016/02/01,2016/03/01 etc) and not a specific month

I can do it if there was one date criteria e.g. for StartedDate e.g.

SELECT P.*,
SUM(GroupTotal)  OVER (PARTITION BY GroupID ORDER BY StartedMonth) AS RunningTotal


FROM (
        SELECT P.GroupID,LEFT(CONVERT(VARCHAR, P.StartedDate, 112), 6) as StartedMonth,Count(P.PersonID) as GroupTotal
        from PersonData P
        WHERE 
             P.StartedDate IS NOT NULL

        GROUP BY  P.GroupID,LEFT(CONVERT(VARCHAR, P.StartedDate, 112), 6)
    ) P
    ORDER BY GroupID,StartedMonth

but my original query has two date criteria not just one

1. P.StartedDate < @InputDate
2. P.OutcomeDate > @InputDate or P.OutcomeDate IS NULL

Is it possible to write a query which has more than one date criteria for the running total

Edit:

Here is example of input PersonData table

PersonID,GroupID,StartedDate,OutcomeDate
1,1001,'2016/05/08',null
2,1001,'2016/05/04','2016/08/03'
3,1001,'2016/06/04','2016/08/03'
4,1001,'2016/07/04','2016/07/07'
5,1001,'2016/07/04','2016/08/08'
6,1001,'2016/08/04','2016/09/03'
7,1001,'2016/08/04','2016/09/03'
8,1001,'2016/09/04','2016/09/08'

Expected Output

GroupId,EndMonthDate,MonthCount, RTMonthCount
1001,'2016/05/31', 2, 2
1001,'2016/06/30', 1, 3
1001,'2016/07/31', 1, 4
1001,'2016/08/31', 2, 6
1001,'2016/09/31', 0, 6

So in the above example you can see Person ID 4 & 8 is not counted as only criteria no 1 was matched but not criteria no 2.

please show sample data and expected result and explain along with it — TheGameiswar, Aug 23 '16 at 17:57
But looking at your criteria PersonIDs 6 and 7 would be excluded as well, because they dont fit into date criteria, right? — Evaldas Buinauskas, Aug 25 '16 at 19:45

score 0 · Answer 1 · edited May 23 '17 at 10:29

How about something like below, you need the PARTITION BY (GroupID, StartedMonth) to calculate the total for each month, and for the running total, do the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT P.*,
SUM(GroupTotal) OVER (PARTITION BY GroupID, StartedMonth) AS MonthTotal,
SUM(GroupTotal)  OVER (PARTITION BY GroupID ORDER BY StartedMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM (
        SELECT P.GroupID,LEFT(CONVERT(VARCHAR, P.StartedDate, 112), 6) as StartedMonth,Count(P.PersonID) as GroupTotal
        from PersonData P
        WHERE 
             P.StartedDate IS NOT NULL

        GROUP BY  P.GroupID,LEFT(CONVERT(VARCHAR, P.StartedDate, 112), 6)
    ) P
    ORDER BY GroupID,StartedMonth

Check out this link also: Calculate a Running Total in SQL Server

** Second version, added a condition to the subquery

SELECT P.*,
SUM(GroupTotal) OVER (PARTITION BY GroupID, StartedMonth) AS MonthTotal,
SUM(GroupTotal)  OVER (PARTITION BY GroupID ORDER BY StartedMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM (
        SELECT P.GroupID,LEFT(CONVERT(VARCHAR, P.StartedDate, 112), 6) as StartedMonth,Count(P.PersonID) as GroupTotal
        from PersonData P
        WHERE 
             P.StartedDate IS NOT NULL
             AND (P.OutcomeDate IS NULL OR (P.OutcomeDate> EOMONTH(P.StartedDate)))    
        GROUP BY  P.GroupID,LEFT(CONVERT(VARCHAR, P.StartedDate, 112), 6)
    ) P
    ORDER BY GroupID,StartedMonth

You didn't read my question. I can do this. What I am not sure it to use two date criteria. OutcomeDate is not in your answer. — dfmetro, Aug 23 '16 at 20:31
So your StartedMonth would play the role of @InputDate in your question? Am I understand this right? — DVT, Aug 23 '16 at 20:41
If I understand it correct this time, then it just about limiting the rows you want in the inner query, which you can do using and extra condition "AND (P.OutcomeDate IS NULL OR (P.OutcomeDate> EOMONTH(P.StartedDate)))" — DVT, Aug 23 '16 at 20:54

How to calculate running total column which has more than one date where criteria

1 Answers1