CODEWITHSUNDEEP
sqlt-sqlbusiness-intelligence
Swe
Swe

Reputation: 11

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

Upvotes: 0

Views: 794

Answers (2)

Swe
Swe

Reputation: 11

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

Upvotes: 0

Serg
Serg

Reputation: 22811

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

Upvotes: 0

Related Questions