Swe Swe - 3 months ago 15
SQL Question

year to date running total for fiscal year

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

Answer

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
Comments