Use a calculated column value to look up a specific value from the same source table

I have a table in SQL Server called ShippingDocSummary which contains all historical records for shipping documents. For a given document number, there can be any number of historical records/transactions. What I am trying to do is create a view that summarizes that table and calculates various column values. My issue comes when trying to get a specific value from the source table based on a calculated column value from the same source table.

Here is a simplified example of what the source table looks like:

DocNum    DocHistID    StatusCode   StatusQty    StatusDate
12345     10000001     AS1          2            2/16/2020
12345     10000002     D6T          2            4/20/2020
12345     10000003     COR          2            4/20/2020
12345     10000004     AS1          2             5/5/2020

My code to create my summary so far is as follows:

SELECT
  DocNum,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN StatusQty ELSE 0 END) AS AS_QTY,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'D6T' THEN StatusQty ELSE 0 END) AS D6T_QTY,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'COR' THEN StatusQty ELSE 0 END) AS COR_QTY,
  MAX(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN DocHistID ELSE null END) AS LastAS_DHID
FROM ShippingDocSummary
GROUP BY DocNum

This gives me the following, which is correct:

DocNum    AS_QTY    D6T_QTY    COR_QTY    LastAS_DHID
12345     4         2          2          10000004

What I am trying to get next, as a column, is the StatusDate that correlates to the LastAS_DHID value that was calculated from the select (i.e. I need the next column to show ‘5/5/2020’ since that date ties to the DocHistID ‘10000004’).

In Power Bi, I’d use a lookupvalue function, but as I transfer my logic to the SQL server, I think I need to use a derived CASE WHEN type of query, but unsure of how to structure the syntax. Any help is greatly appreciated!

Answer

Maybe you have to create your on table and then do a left join. This being said, Try this:

SELECT A.DocNum,
A.AS_QTY,
A.D6T_QTY,
A.COR_QTY,
A.LastAS_DHID,
B.StatusDate

        FROM (
                SELECT DocNum,
                       SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN StatusQty ELSE 0 END) AS AS_QTY,
                       SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'D6T' THEN StatusQty ELSE 0 END) AS D6T_QTY,
                       SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'COR' THEN StatusQty ELSE 0 END) AS COR_QTY,
                       MAX(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN DocHistID ELSE null END) AS LastAS_DHID
                
                       FROM ShippingDocSummary
                       GROUP BY DocNum) A
        LEFT JOIN TABLE_WITH_DOCHIST_10000004 B
        ON A.LastAS_DHID = B.DocHistID