Calculated column in SQL based on multiple column matches between two tables and cumsum

You haven't specified which database you use, so I use Snowflake in my example. But the difference in syntax would be minor.

Here is the approach:

1. Calculate whole and reminder parts of table1.a column
2. Convert table2.week string to integer
3. Calculate cumulative sum
4. Join tables by the week (note that join is made by + 1)

WITH t1 AS (
 SELECT
   x,
   y,
   a,
   FLOOR(a, 7) AS weeks_whole,
   a % 7 AS week_reminder
 FROM table1
),

t2 AS (
  SELECT
   x,
   y,
   week,
   CAST(REPLACE(week, 'WK', '') AS INTEGER) AS week_num,
   c,
   SUM(c) OVER (PARTITION BY x, y ORDER BY week_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c_cumulative
  FROM table2
)

SELECT
  t1.x,
  t1.y,
  t1.a,
  t2.c_cumulative - t2.c + t2.c * (t1.week_reminder / 7)
FROM t1
JOIN t2
  ON t2.x = t1.x
  AND t2.y = y1.y
  AND t2.week_num = t1.weeks_whole + 1

Tried and tested in Oracle 21C,

SELECT OUTER_TABLE.X,OUTER_TABLE.Y,OUTER_TABLE.A,

  CASE WHEN NEXTWEEKDAYS =0 THEN
  FLOOR((SELECT SUM(Table2.C) FROM  Table2
  WHERE OUTER_TABLE.X=Table2.X
  AND OUTER_TABLE.Y=Table2.Y
  AND OUTER_TABLE.WEEK_NUM>=(SUBSTR(Table2.Week, 3))
  ))
  ELSE
  FLOOR((SELECT SUM(Table2.C) FROM  Table2
  WHERE OUTER_TABLE.X=Table2.X
  AND OUTER_TABLE.Y=Table2.Y
  AND OUTER_TABLE.WEEK_NUM>=(SUBSTR(Table2.Week, 3))
  ) 
  +
 ((
  SELECT Table2.C FROM Table2
    WHERE OUTER_TABLE.X=Table2.X
  AND OUTER_TABLE.Y=Table2.Y
  AND UPPER(OUTER_TABLE.NEXTWEEK)=UPPER(Table2.WEEK)
  )
  *(OUTER_TABLE.NEXTWEEKDAYS/7)
  ))
  END AS SUM
  
  FROM
(SELECT DISTINCT Table1.X,Table1.Y, Table1.A, Table1.A/7 AS WEEK_NUM, MOD(Table1.A,7) NEXTWEEKDAYS,'WK'||CEIL((Table1.A/7)) AS NEXTWEEK
FROM Table1, Table2
  WHERE Table1.X=Table2.X
  AND Table1.Y=Table2.Y
)OUTER_TABLE

You can check for the sample tables and output here fiddle