How do I create a JOIN between 2 SELECT Statements with GROUPs?

I have one table with daily stock data and I want to summarize it by week. The daily table is:

CREATE TABLE [dbo].[TempDaily] (
    [Symbol]    CHAR (10)  NOT NULL,
    [CloseDate] DATE       NOT NULL,
    [DailyHi]   FLOAT (53) NULL,
    [DailyLow]  FLOAT (53) NULL,
    [AdjClose]  FLOAT (53) NOT NULL,
    [WeekEnd]   DATE       NULL
);

I want to insert a summary for the week into the weekly table:

CREATE TABLE [dbo].[Weekly] (
    [Symbol]    CHAR (10)  NOT NULL,
    [WeekEnd]   DATE       NOT NULL,
    [WeeklyHi]  FLOAT (53) NOT NULL,
    [WeeklyLow] FLOAT (53) NOT NULL,
    [AdjClose]  FLOAT (53) NULL
);

The [AdjClose] column is set to nulls allowed because my current workaround inserts the other 4 columns first, then updates the weekly table with the weekly AdjClose value from a 3rd table, which is really slow.

Getting the weekly data into the table for the first 4 columns is straightforward:

strSQL = "INSERT INTO Weekly (Symbol, WeekEnd, WeeklyHi, WeeklyLow) " &
    "SELECT Symbol, WeekEnd, MAX(DailyHi), MIN(DailyLow) " &
     "FROM TempDaily " &
     "GROUP BY Symbol, WeekEnd "

Getting the weekly AdjClose is not as straightforward but I can insert the data into a temporary table and then update the Weekly table from it:

strSQL = "INSERT INTO Test (WeekEnd, AdjClose) " &
    "Select wdata.WeekEnd, MAX(wdata.AdjClose) " &
    "FROM " &
        "(Select CloseDate, WeekEnd, " &
        "FIRST_VALUE(AdjClose) OVER (PARTITION BY WeekEnd ORDER BY CloseDate                                            
         DESC ROWS UNBOUNDED PRECEDING) As AdjClose " &
         "FROM TempDaily) wdata " &
     "GROUP BY wdata.WeekEnd "

I would really prefer to insert the data for all 5 columns into the Weekly table with a single statement rather than resorting to my clumsy workaround but I haven't been able to figure out how to join the 2 statements.

The data from the Daily table is in the format (spaces added for clarity):

Symbol,    CloseDate,  DailyHi,DailyLow,AdjClose,   WeekEnd
AAPL      ,5/31/2019,   177.99,  175.00,  175.07,  6/1/2019
AAPL      ,5/30/2019,   179.23,  176.67,  178.30,  6/1/2019
AAPL      ,5/29/2019,   179.35,  176.00,  177.38,  6/1/2019
AAPL      ,5/28/2019,   180.59,  177.91,  178.23,  6/1/2019
AAPL      ,5/24/2019,   182.14,  178.62,  178.97,  5/25/2019
AAPL      ,5/23/2019,   180.54,  177.81,  179.66,  5/25/2019
AAPL      ,5/22/2019,   185.71,  182.55,  182.78,  5/25/2019
AAPL      ,5/21/2019,   188.00,  184.70,  186.60,  5/25/2019
AAPL      ,5/20/2019,   184.35,  180.28,  183.09,  5/25/2019

The weekly table should end up with:

Symbol,        WeekEnd, WeeklyHi, WeeklyLo,  AdjClose
AAPL      ,   6/1/2019,   180.59,   175.00,   175.07
AAPL      ,   5/25/2019,  188.00,   177.81,   178.97

I'm converting a MS Access database into VB.NET and thought it would be a fairly simple port. There are a lot more differences than I anticipated. Any help is appreciated.

The solution presented below is one I'd tried but it generates an SQLException: 'Incorrect syntax near '('. Incorrect syntax near 'wdata'. So I'm still trying to figure this one out.

strSQL = "INSERT INTO Weekly (Symbol, WeekEnd, WeeklyHi, WeeklyLow, AdjClose) " &
"Select A.Symbol, A.WeekEnd, A.WeeklyHi, A.WeeklyLow, ISNULL(B.AdjClose, 0) as AdjClose " &
"FROM " &
    "(SELECT Symbol, WeekEnd, MAX(DailyHi) as WeeklyHi, MIN(DailyLow) as WeeklyLow " &
     "FROM TempDaily " &
     "GROUP BY Symbol, WeekEnd ) A " &
     "LEFT JOIN " & 
    "(Select wdata.WeekEnd, MAX(wdata.AdjClose) as AdjClose" &
    "FROM " &
        "(Select CloseDate, WeekEnd, " &
        "FIRST_VALUE(AdjClose) OVER (PARTITION BY WeekEnd ORDER BY CloseDate                                            
         DESC ROWS UNBOUNDED PRECEDING) As AdjClose " &
         "FROM TempDaily) wdata " &
     "GROUP BY wdata.WeekEnd) B ON A.WeekEnd = B.WeekEnd "

Solution

I think that you can do what you want, joining them like this (updated):

    strSQL = "INSERT INTO Weekly (Symbol, WeekEnd, WeeklyHi, WeeklyLow, AdjClose) " &
"Select A.Symbol, A.WeekEnd, A.WeeklyHi, A.WeeklyLow, ISNULL(B.AdjClose, 0) as AdjClose " &
"FROM " &
    "(SELECT Symbol, WeekEnd, MAX(DailyHi) as WeeklyHi, MIN(DailyLow) as WeeklyLow " &
    "FROM TempDaily " &
    "GROUP BY Symbol, WeekEnd ) A " &
    "LEFT JOIN " &
    "(Select wdata.WeekEnd, MAX(wdata.AdjClose) as AdjClose " &
    "FROM " &
        "(Select CloseDate, WeekEnd, " &
        "FIRST_VALUE(AdjClose) OVER (PARTITION BY WeekEnd ORDER BY CloseDate " &
        "DESC ROWS UNBOUNDED PRECEDING) As AdjClose " &
        "FROM TempDaily) wdata " &
     "GROUP BY wdata.WeekEnd) B ON A.WeekEnd = B.WeekEnd "