Dynamic Aggregation Based on Row Value

Question

I have a calendar table with a column [WeekFuzzy]. This column updates to show "CURRENT" for the current week and "PREVIOUS" for the previous week. There is also a [weekseq] column that is a running count of the week. I would like to make a column that counts backward from the current week to give us a static starting point where the current week is always zero.

I've tried joining in a filtered version of the table where [weekfuzzy]="CURRENT", but that just gives me the current weekseq and the rest of the rows are null.

SELECT
    cal.Week,
    cal.weekname,
    cal.weekfuzzy,
    cal.weekseq,
    CurCal.weekseq
FROM
    clientcalendar AS Cal
LEFT JOIN
    (SELECT
      cal.week,
      cal.weekseq
  FROM
      clientcalendar AS Cal
  WHERE
      cal.weekfuzzy="CURRENT") AS CurCal ON cal.week=curcal.week