year to date running total for fiscal year

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

This is the query to find month to date. How to find year to date for fiscal year from the same query? eg: running total from 01/04/2015-31/03/2016

Serg · Accepted Answer · 2016-08-11T10:35:33.883

Not sure from wich date you need the FY start. Suppose from @STARTOFMONTH_MAN. Then you can get FY start as

declare @fymonth int = 4; -- first month of FY.

declare @STARTOFMONTH_MAN date = '20160320';
select  fyStart = dateadd(MONTH,
                @fymonth - CASE WHEN month(@STARTOFMONTH_MAN) >= @fymonth THEN 1 ELSE 13 END,
                dateadd(YEAR, datediff(YEAR, 0, @STARTOFMONTH_MAN),0));

You may simplify those dates calculations by creating calendar table.

EDIT
Idea is to restrict data in WHERE with the larger interval and conditionally SUM data for the subinterval.

declare @fymonth int = 4; -- first month of FY.

SELECT DISTINCT
    ACCOUNTDATE
    ,PROPERTYNAME
    ,rt.FYManagement, rt.MonthManagement
FROM aaa t
CROSS APPLY     
    (SELECT 
        SUM(t2.MANAGEMENT) AS FYManagement
        ,SUM(CASE WHEN t2.ACCOUNTDATE BETWEEN 
                -- start of month for t.ACCOUNTDATE
            dateadd(MONTH, datediff(MONTH, 0, t3.ACCOUNTDATE), 0)
            AND t3.ACCOUNTDATE
         THEN t2.MANAGEMENT END) AS MonthManagement
     from aaa t2
     JOIN aaa t3 ON t3.primarykey = t.primarykey -- change as needed to get 1 to 1 JOIN
     where 
     t2.PROPERTYNAME = t.PROPERTYNAME and
     t2.ACCOUNTDATE BETWEEN 
                -- FY start for t.ACCOUNTDATE
                dateadd(MONTH,
                  @fymonth - CASE WHEN month(t.ACCOUNTDATE) >= @fymonth THEN 1 ELSE 13 END,
                  dateadd(YEAR, datediff(YEAR, 0, t.ACCOUNTDATE), 0));
                AND t.ACCOUNTDATE 
    ) as rt
WHERE AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY AccountDate

thanks for your help.. I need to start the FY from 1st April of every year and this will be dynamic. In this query eg: running total from 01/04/2015-31/03/2016 has to reset. — Swe, Aug 11 '16 at 09:23
I've got it. I mean your query depends on parameters which restrict the period. The FY start for the first parameter may be 01.04.2015 and for the second one 01.04.2016. Should this be taken into account in the subquery or not? — Serg, Aug 11 '16 at 09:35
this is not not working, error message is //// Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression. — Swe, Aug 11 '16 at 10:13
I updated the answer, see `JOIN aaa t3` workaround. You need to change ON clause using real aaa's primary key. — Serg, Aug 11 '16 at 10:35

score 0 · Answer 2 · answered Aug 11 '16 at 10:50

declare @fymonth int = 4; -- first month of FY.

SELECT DISTINCT ACCOUNTDATE ,PROPERTYNAME ,rt.FYManagement, rt.MonthManagement FROM aaa t CROSS APPLY
(SELECT SUM(t2.MANAGEMENT) AS FYManagement ,SUM(CASE WHEN t2.ACCOUNTDATE BETWEEN -- start of month for t.ACCOUNTDATE dateadd(MONTH, datediff(MONTH, 0, t3.ACCOUNTDATE), 0) AND t3.ACCOUNTDATE THEN t2.MANAGEMENT END) AS MonthManagement from aaa t2 JOIN aaa t3 ON t3.primarykey = t.primarykey -- change as needed to get 1 to 1 JOIN where t2.PROPERTYNAME = t.PROPERTYNAME and t2.ACCOUNTDATE BETWEEN -- FY start for t.ACCOUNTDATE dateadd(MONTH, @fymonth - CASE WHEN month(t.ACCOUNTDATE) >= @fymonth THEN 1 ELSE 13 END, dateadd(YEAR, datediff(YEAR, 0, t.ACCOUNTDATE), 0)); AND t.ACCOUNTDATE ) as rt WHERE AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN ORDER BY AccountDate

year to date running total for fiscal year

2 Answers2