By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ProductionInputs (
CowID int,
ProductionDate date,
DateCalved Date,
DaysInMilk int
);
create table CalvingDates (
CowID int,
DateCalved Date
);
insert into ProductionInputs
(CowID, ProductionDate) values
(18001, '2021-10-08'),
(17158, '2021-10-30'),
(17158, '2021-10-23'),
(17158, '2021-10-16'),
(17158, '2021-10-08'),
(17158, '2021-08-07');
6 rows affected
insert into CalvingDates
(CowID, DateCalved) values
(18001, '2021-04-23'),
(17158, '2021-10-01'),
(17158, '2020-08-07');
3 rows affected
UPDATE t
SET t.DateCalved = ca.DateCalved
, t.DaysInMilk = DATEDIFF(day, ca.DateCalved, t.ProductionDate)
FROM ProductionInputs t
CROSS APPLY (
SELECT MAX(d.DateCalved) AS DateCalved
FROM CalvingDates d
WHERE d.CowId = t.CowId
AND d.DateCalved <= t.ProductionDate
) ca
6 rows affected
select * from ProductionInputs;
| CowID | ProductionDate | DateCalved | DaysInMilk |
|---|---|---|---|
| 18001 | 2021-10-08 | 2021-04-23 | 168 |
| 17158 | 2021-10-30 | 2021-10-01 | 29 |
| 17158 | 2021-10-23 | 2021-10-01 | 22 |
| 17158 | 2021-10-16 | 2021-10-01 | 15 |
| 17158 | 2021-10-08 | 2021-10-01 | 7 |
| 17158 | 2021-08-07 | 2020-08-07 | 365 |