How to reset the month to date running total

Question

SELECT DISTINCT
    ACCOUNTDATE,
    PROPERTYNAME,
    rt.management
FROM 
    aaa t
CROSS APPLY
    (SELECT 
         SUM(MANAGEMENT) AS management
     FROM
         aaa
     WHERE
         PROPERTYNAME = t.PROPERTYNAME 
         AND ACCOUNTDATE <= t.ACCOUNTDATE) as rt
WHERE 
    AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY 
    AccountDate

This is my query, I want to calculate the running total for month to date, eg: The running total from 1/08/2016-31/08/2016 and again the total has to reset from the beginning for 1/09/2016-31/09/2016.

But from the above query, I'm not able to reset total for the next month.

Answer 1

Try

SELECT DISTINCT
    ACCOUNTDATE
    ,PROPERTYNAME
    ,rt.management
from aaa t
cross apply     
    (select SUM(MANAGEMENT) as management
     from aaa
     where 
     PROPERTYNAME = t.PROPERTYNAME and
     ACCOUNTDATE BETWEEN dateadd(MONTH, datediff(MONTH, 0,t.ACCOUNTDATE),0) -- start of month
AND t.ACCOUNTDATE 
    ) as rt
WHERE AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY AccountDate