First of all, please read carefully solution #1 by
OriginalGriff[
^]. I completely agree with His notes.
To abe able to achieve that you should
UNPIVOT[
^] data first.
SELECT <SetOfColumns>, SUM(ValueToSum) AS SumOfSomething
FROM (
SELECT <SetOfColumns>, Jan_CY AS ValueToSum, SomeDateField AS Period
FROM YourTable
UNION ALL
SELECT <SetOfColumns>, Feb_CY AS ValueToSum, SomeDateField AS Period
FROM YourTable
UNION ALL
SELECT <SetOfColumns>, Mar_CY AS ValueToSum, SomeDateField AS Period
FROM YourTable
UNION ALL
SELECT <SetOfColumns>, APR_CY AS ValueToSum, SomeDateField AS Period
FROM YourTable
) AS T
WHERE (Month(T.Period) BETWEEN 1 AND Month(Date)-1) AND (Year(T.Period)=Year(Date))
GROUP BY ...