sql - Modifying one table based on the rows of another

Question

Example: Table A

IdNo  StartDate   EndDate
1     2019-01-01  2020-02-02
1     2019-01-15  2020-03-03
7     2018-04-03  2020-07-01
...

Then the other table has historical data as shown:
Table B

IdNo Date       Num
1    2020-02-29 100
1    2020-02-28 99
1    2020-02-27 98
1    2020-02-26 97
...
7    2020-07-02 299
7    2020-07-01 298
7    2020-06-30 297
...

What the output should look like:

IdNo Date       Num
1    2020-02-29 70
1    2020-02-28 69
1    2020-02-27 68
1    2020-02-26 67
...
7    2020-07-02 299
7    2020-07-01 268
7    2020-06-30 267
...

What I'm trying to do is subtract 30 from the value for Table B's column "Num" when it's dates fall between Table A's start and end dates and the IdNo for each table matches.

Table B will have IdNos and dates that don't appear in table A. Each row in Table A is independent of the others.

The solutions I've been trying out mostly look like this:

WHILE 

SELECT IdNo, Date, CASE WHEN Date BETWEEN A.StartDate AND A.EndDate THEN Num - 30 
  ELSE NULL END AS NewNum
  FROM TableB AS B
  LEFT JOIN TableA AS A ON A.IdNo = B.IdNo

END WHILE

The goal of the above attempt is to iterate through Table A and use a CASE WHEN to update Table B based on each row of Table A. Each iteration should update Table B and Table B should go through X updates where X is the number of rows in Table A.

Answer 1

You can do the whole thing without loops, using a correlated subquery. This can also be done as an APPLY:

SELECT
  IdNo,
  Date,
  NewNum - 30 *
     (SELECT COUNT(*)
     FROM TableA
     WHERE Date BETWEEN A.StartDate AND A.EndDate
     AND A.IdNo = B.IdNo)
  AS NewNum
  FROM TableB AS B

I have not dealt with the case where there are no matching rows in TableA as I'm unsure what result you want for that.