CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
dfmetro
dfmetro

Reputation: 4582

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 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.

Upvotes: 2

Views: 67

Answers (1)

DVT
DVT

Reputation: 3127

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

Upvotes: 0

Related Questions