SQLTeam.com | Weblogs | Forums

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

tsql
sql2012

#1

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


#2

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