Swe - 1 year ago 86
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

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';
@fymonth - CASE WHEN month(@STARTOFMONTH_MAN) >= @fymonth THEN 1 ELSE 13 END,
``````

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