SQL Server : Multiply Row Values for the last month in a quarter

Question

Hello , I have a table with two columns Date and Monthly Return , I need to calculate a new column with a calculation, the formula is , for the last month in the quarter , the value should be the multiple of previous 2 months and the current month, for example : for march, the value should be (jan * feb * march ) and again for june the value should be (april * may * june), for the other months in the quarter the value should be as is.

Answer 1

Check a query:

select *, 
    case when m % 3 = 0 then 
        isnull((select ReturnValue from ATable where year(EffectiveDate) = y and month(EffectiveDate) = m - 2), 1) *
        isnull((select ReturnValue from ATable where year(EffectiveDate) = y and month(EffectiveDate) = m - 1), 1) *
        ReturnValue
    else ReturnValue end as NewColumn
from ATable
cross apply (values (year(EffectiveDate), month(EffectiveDate))) M(y, m)