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

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 output 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 etc) and not a spesific 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 uses more than one criteria for the running total

Some reason you don't just put that, more complex, WHERE clause in your sub-select?

If you only want that for the running-total, not to control the rows that are included in the resultset, you could use a CASE statement in the SUM in your running total