How do I select MAX/MIN from multiple tables, along with other columns from MIN/MAX row I have a query where I JOIN 4 weekly "summary" tables, and SELECT a MIN and a MAX of units FROM each table. This would give me the highest/lowest total units sold for, and I'm pulling an average from another table. 

    SELECT
            L4.TotalUnits / 4   AS AvgMvmt,
            MIN(Ago.TotalUnits) AS LowMvmt,
            MAX(Ago.TotalUnits) AS HighMvmt
    FROM

            RTG_LOOKUP_LAST4 AS L4 INNER JOIN
            (
                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
            ) AS Ago
                    ON Ago.UCC12UPCCode = L4.UCC12UPCCode
    WHERE
            L4.UCC12UPCCode = '01254601144'
    GROUP BY
            L4.TotalUnits


Now I need to add two more columns, HighDate and LowDate. These columns represent the date for the week that sold the highest/lowest units.

The Ago table looks like this:

UCC12UPCCode  TotalUnits  Date
------------- ----------- ----------
01254601144   90          2018-04-14
01254601144   98          2018-05-05
01254601144   107         2018-04-21
01254601144   132         2018-04-28


How would pull the Date column from the correct table so my end results looks like below:

LowMvmt     HighMvmt    LowDate    HighDate
----------- ----------- ---------- ----------
90          132         2018-04-14 2018-04-28


E: Fiddle 
https://dbfiddle.uk/?rdbms=sqlserver_2012&amp;fiddle=749c59e1f11bc2146a605072f1ef7d98
As a another option

WITH Ago AS (
    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
    ),
    Ranked AS (
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits, Date) LowUnitRank, 
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits DESC, Date) HighUnitRank
    FROM Ago
    )
SELECT L4.UCC12UPCCode,
    MAX(CASE WHEN LowUnitRank = 1 THEN TotalUnits END) LowTotalUnits,
    MAX(CASE WHEN HighUnitRank = 1 THEN TotalUnits END) HighTotalUnits,
    MAX(CASE WHEN LowUnitRank = 1 THEN Date END) LowUnitDate,
    MAX(CASE WHEN HighUnitRank = 1 THEN Date END) HighUnitDate
FROM RTG_LOOKUP_LAST4 L4
    INNER JOIN Ranked r ON L4.UCC12UPCCode = r.UCC12UPCCode
GROUP BY L4.UCC12UPCCode;


I've done this using a CTE query as I find it easier to follow and explain.  

The first CTE is just your Ago data.

The second CTE ranks each row of the Ago data using ROW_NUMBER based on the ordering of the TotalUnits.  I have include the Date in the order so that if there are duplicate TotalUnits the first occurrence will be picked.

In the final query we are using CASE statements to pivot the data and aggregating the result for each UCC12UPCCode with MAX to create a single row for each UCC12UPCCode.

This query can be run over multiple UCC12UPCCode's