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