-- This query calculates the fiscal year and fiscal week number for each date based on a custom fiscal calendar that starts on the closest Sunday to January 1st.

SELECT DISTINCT
    d.[Date_Column],
    CASE 
        WHEN d.[Date_Column] >= start_next THEN GetYear(d.[Date_Column]) + 1
        WHEN d.[Date_Column] >= start_cur THEN GetYear(d.[Date_Column])
        ELSE GetYear(d.[Date_Column]) - 1
    END AS [Fiscal Year],
    CASE 
        WHEN d.[Date_Column] >= start_next THEN GetYear(d.[Date_Column]) + 1
        WHEN d.[Date_Column] >= start_cur THEN GetYear(d.[Date_Column])
        ELSE GetYear(d.[Date_Column]) - 1
    END AS [Fiscal Year to Compare],
    FLOOR(1 + DayDiff(d.[Date_Column],
        CASE 
            WHEN d.[Date_Column] >= start_next THEN start_next
            WHEN d.[Date_Column] >= start_cur THEN start_cur
            ELSE start_prev
        END
    ) / 7.0) AS [Fiscal Week Number]
FROM (
    SELECT [Date_Column],
           -- precompute fiscal year starts once
           CASE WHEN DayOfWeek(CreateDate(GetYear([Date_Column]) - 1, 1, 1)) <= 3
                THEN AddDays(CreateDate(GetYear([Date_Column]) - 1, 1, 1), -1 * DayOfWeek(CreateDate(GetYear([Date_Column]) - 1, 1, 1)))
                ELSE AddDays(CreateDate(GetYear([Date_Column]) - 1, 1, 1), 7 - DayOfWeek(CreateDate(GetYear([Date_Column]) - 1, 1, 1)))
           END AS start_prev,
           CASE WHEN DayOfWeek(CreateDate(GetYear([Date_Column]), 1, 1)) <= 3
                THEN AddDays(CreateDate(GetYear([Date_Column]), 1, 1), -1 * DayOfWeek(CreateDate(GetYear([Date_Column]), 1, 1)))
                ELSE AddDays(CreateDate(GetYear([Date_Column]), 1, 1), 7 - DayOfWeek(CreateDate(GetYear([Date_Column]), 1, 1)))
           END AS start_cur,
           CASE WHEN DayOfWeek(CreateDate(GetYear([Date_Column]) + 1, 1, 1)) <= 3
                THEN AddDays(CreateDate(GetYear([Date_Column]) + 1, 1, 1), -1 * DayOfWeek(CreateDate(GetYear([Date_Column]) + 1, 1, 1)))
                ELSE AddDays(CreateDate(GetYear([Date_Column]) + 1, 1, 1), 7 - DayOfWeek(CreateDate(GetYear([Date_Column]) + 1, 1, 1)))
           END AS start_next
    FROM [Date_Table]
) d
ORDER BY d.[Date_Column]