Cumulative Column that starts at Zero every Year

Viewed 51

I have data from January 1st 2008 to today ordered by date in the first column of a table Ratio.

I have values in the second column. I was able to do a cumulative third column with the following code but I don't know how to make it re-start every January 1st to have cumulative per year.

SELECT 
    t3.Date, 
    SUM(cumul) AS cumul 
FROM (
    SELECT 
        t1.Date, 
        t1.nb, 
        SUM(t2.nb) AS cumul   
    FROM (
        SELECT 
            Ratio.Date, 
            SUM(DailyValue) AS Nb
        FROM Ratio
        GROUP BY Ratio.Date
    )t1
    INNER JOIN (
        SELECT 
            Ratio.Date, 
            SUM(DailyValue) AS nb
        FROM Ratio
        GROUP BY Ratio.Date
    ) t2 
        ON t1.Date >= t2.Date
    GROUP BY t1.Date, t1.nb
)t3
GROUP BY PnLDate,nb
ORDER BY pnldate
1 Answers
Related