Linking tables to extract value based on closest previous date I'm going to use an example that was previously posted but modify it to match my problem:

TOCs bu Date:

Location StatDate TOC
WC1  9/1/1900    5600
WC1  10/1/2010   5605
WC2  8/2/1980    4300
WC2  9/5/2005    4280
WC2  10/6/2011   4285


Water Levels by date:

Location  MeasurementDate DepthToWater
WC1         7/26/2006    5.5
WC1         9/30/2008    5
WC1         10/5/2010    6
WC2         7/30/2010    8
WC2         11/3/2015    8.5


Desired result:

Location MeasurementDate  DepthToWater   TOC    Elevation(TOC-DepthToWater)
WC1         7/26/2006    5.5         5600      5594.5
WC1         9/30/2008    5           5600      5595
WC1         3/5/2015     6           5605      5599
WC2         7/30/2010    8           4280      4272
WC2         11/3/2015    8.5         4285      4276.5


I modified the proposed solution that was previously posted, but that solution didn't JOIN the 'location' fields, and I couldn't get it to work anyway.
I would GREATLY appreciate some help with this.
You can use a (CROSS) APPLY operator for this purpose. But please have a look at data you have supplied, there is some incoherence in these dates.




CREATE TABLE TOC (Location VARCHAR(10), StatDate DATETIME, TOC INT);
INSERT INTO TOC VALUES
('WC1',  '19000901', 5600),
('WC1',  '20101001', 5605),
('WC2',  '19800802', 4300),
('WC2',  '20050905', 4280),
('WC2',  '20111006', 4285);
GO






CREATE TABLE WLEVELS (Location VARCHAR(10), MeasurementDate DATETIME, DepthToWater DECIMAL(5,1));
INSERT INTO WLEVELS VALUES
('WC1', '20060726', 5.5),
('WC1', '20080930', 5),
('WC1', '20101005', 6),
('WC2', '20100730', 8),
('WC2', '20151103', 8.5);
GO






SELECT
    Location,
    MeasurementDate,
    DepthToWater,
    TOC,
    (TOC - DepthToWater) AS Elevation
FROM
    WLEVELS
CROSS APPLY (SELECT TOP 1 
                 TOC
             FROM
                 TOC
             WHERE
                 TOC.Location = WLEVELS.Location
                 AND StatDate &lt;= MeasurementDate 
             ORDER BY 
                 Location, StatDate DESC) TC;
GO



Location | MeasurementDate     | DepthToWater |  TOC | Elevation
:------- | :------------------ | :----------- | ---: | :--------
WC1      | 26/07/2006 00:00:00 | 5.5          | 5600 | 5594.5   
WC1      | 30/09/2008 00:00:00 | 5.0          | 5600 | 5595.0   
WC1      | 05/10/2010 00:00:00 | 6.0          | 5605 | 5599.0   
WC2      | 30/07/2010 00:00:00 | 8.0          | 4280 | 4272.0   
WC2      | 03/11/2015 00:00:00 | 8.5          | 4285 | 4276.5   



db&lt;>fiddle here